> 【緩募】SQL で b が空集合の時は a をそのまま、そうでない時は a を b の要素でフィルタしたものを結果とする、みたいなクエリの書き方。 https://twitter.com/okapies/status/1570367933968420868?s=21&t=YKmgbQmmUQxfi4pdtWNFcw

fuga.sql

WITH a AS (
  SELECT 1 AS n UNION SELECT 2 AS n
), b AS (
  SELECT 2 AS n UNION SELECT 3 AS n
), t AS (
  SELECT a.n AS n, b.n AS b_n, COUNT(b.n) OVER() AS b_count
    FROM a LEFT OUTER JOIN b USING(n)
)
SELECT n
  FROM t
 WHERE b_n IS NOT NULL OR b_count = 0
;
--> 2

WITH a AS (
  SELECT 1 AS n UNION SELECT 2 AS n
), b AS (
  SELECT 2 AS n WHERE false
), t AS (
  SELECT a.n AS n, b.n AS b_n, COUNT(b.n) OVER() AS b_count
    FROM a LEFT OUTER JOIN b USING(n)
)
SELECT n
  FROM t
 WHERE b_n IS NOT NULL OR b_count = 0
;
--> 1
--> 2

hoge.sql

WITH a AS (
  SELECT 1 UNION SELECT 2
), b AS (
  SELECT 2 UNION SELECT 3
), i AS (
  SELECT * FROM a INTERSECT SELECT * FROM b
)
SELECT * FROM a WHERE (SELECT COUNT(*) = 0 FROM b)
 UNION ALL
SELECT * FROM i WHERE (SELECT COUNT(*) > 0 FROM b)
;
--> 2

WITH a AS (
  SELECT 1 UNION SELECT 2
), b AS (
  SELECT 2 WHERE false
), i AS (
  SELECT * FROM a INTERSECT SELECT * FROM b
)
SELECT * FROM a WHERE (SELECT COUNT(*) = 0 FROM b)
 UNION ALL
SELECT * FROM i WHERE (SELECT COUNT(*) > 0 FROM b)
;
--> 1
--> 2

piyo.sql

WITH a AS (
  SELECT 1 UNION SELECT 2
), b AS (
  SELECT 2 UNION SELECT 3
), i AS (
  SELECT * FROM a INTERSECT SELECT * FROM b
)
SELECT * FROM a WHERE NOT EXISTS(SELECT * FROM b)
 UNION ALL
SELECT * FROM i WHERE EXISTS(SELECT * FROM b)
;
--> 2

WITH a AS (
  SELECT 1 UNION SELECT 2
), b AS (
  SELECT 2 WHERE false
), i AS (
  SELECT * FROM a INTERSECT SELECT * FROM b
)
SELECT * FROM a WHERE NOT EXISTS(SELECT * FROM b)
 UNION ALL
SELECT * FROM i WHERE EXISTS(SELECT * FROM b)
;
--> 1
--> 2