4 ms·

> All the other non-left joins are just syntactic sugar and can be expressed using only left join…? A “vertical join” (SQL UNION) is one of the “non-left joins

by tzot 3y ago

> All the other non-left joins are just syntactic sugar and can be expressed using only left join…? A “vertical join” (SQL UNION) is one of the “non-left joins”. How can you transform a “vertical join” to a “left join”?

cbreezyyall 3y ago
This feels like an interesting interview question. I think you could simulate this with a full outer join on the entire select list and coalesce? For a UNION ALL you could put some literal column in the selects from both tables that you set to different values and include that in the join so you'd get a result set that will have all nulls in the right table columns for the rows in the left table and vice versa. Something like WITH top_t AS ( SELECT a ,b ,c ,'top' as nonexistent_col FROM table_1 ), bottom_t AS ( SELECT a ,b ,c ,'bottom' as nonexistent_col FROM table_2 ) SELECT COALESCE(top_t.a, bottom_t.a) AS a ,COALESCE(top_t.b, bottom_t.b) AS b ,COALESCE(top_t.c, bottom_t.c) AS c FROM top_t FULL OUTER JOIN bottom_t ON top_t.a = bottom_t.a AND top_t.b = bottom_t.b AND top_t.c = bottom_t.c AND top_t.nonexistent_col = bottom_t.nonexistent_col -- remove this for a normal UNION
- wood_spirit 3y ago
  Bravo!!
- tzot 3y ago
  This is a nice trick using full outer join! My question to wood_spirit still stands, though.
  - wood_spirit 3y 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)

by tzot 3y ago

cbreezyyall 3y ago

This feels like an interesting interview question. I think you could simulate this with a full outer join on the entire select list and coalesce? For a UNION ALL you could put some literal column in the selects from both tables that you set to different values and include that in the join so you'd get a result set that will have all nulls in the right table columns for the rows in the left table and vice versa. Something like WITH top_t AS ( SELECT a ,b ,c ,'top' as nonexistent_col FROM table_1 ), bottom_t AS ( SELECT a ,b ,c ,'bottom' as nonexistent_col FROM table_2 ) SELECT COALESCE(top_t.a, bottom_t.a) AS a ,COALESCE(top_t.b, bottom_t.b) AS b ,COALESCE(top_t.c, bottom_t.c) AS c FROM top_t FULL OUTER JOIN bottom_t ON top_t.a = bottom_t.a AND top_t.b = bottom_t.b AND top_t.c = bottom_t.c AND top_t.nonexistent_col = bottom_t.nonexistent_col -- remove this for a normal UNION

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