Hacker News new | ask | show | jobs
by tzot 877 days ago
This is a nice trick using full outer join! My question to wood_spirit still stands, though.
1 comments
wood_spirit 877 days ago
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)
link