Re:Let's Talk about Joins
by tzot on Monday January 22, 2024 @04:45AM
(#39087860)
This is a nice trick using full outer join!
My question to wood_spirit still stands, though.
[ Parent
| on HN ]
Re:Let's Talk about Joins
by wood_spirit on Monday January 22, 2024 @10:48AM
(#39090709)
A fun challenge :)
Assuming a null we can use as sentinel:
SELECT COALESCE(a.col, b.col) AS col
FROM a
LEFT JOIN b ON (TRUE)
WHERE (a.col IS NULL) IS DISTINCT FROM (b.col IS NULL)
(Getting that sentinel might take effort, depending on eg whether there are useful window functions. Here is a way to do it with only left joins and the assumption the column has no duplicate values: WITH crossed AS (
SELECT * FROM UNNEST([1, 2]) AS sentinal
)
SELECT IF(sentinal = 1, col, NULL) AS col
FROM a
LEFT JOIN crossed ON TRUE
WHERE sentinal = 1 OR col = (SELECT * FROM a LIMIT 1)