Re:Let's Talk about Joins
by wood_spirit on Saturday January 20, 2024 @03:17PM
(#39071839)
All the other non-left joins are just syntactic sugar and can be expressed using only left join…?
[ Parent
| on HN ]
> 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”?
Re:Let's Talk about Joins
by cbreezyyall on Saturday January 20, 2024 @04:49PM
(#39072768)
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
Re:Let's Talk about Joins
by wood_spirit on Saturday January 20, 2024 @05:42PM
(#39073224)
Bravo!!
This is a nice trick using full outer join!
My question to wood_spirit still stands, though.
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)