3 ms·
This is a nice trick using full outer join! My question to wood_spirit still stands, though.
by tzot 3y ago
This is a nice trick using full outer join!
My question to wood_spirit still stands, though.
- wood_spirit 3y agoA 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)