The SQL standard (SQL:2011 draft, p. 363) says that when two tables are joined with USING, the result has only one column named c for each c (called a corresponding join column) specified in <join column list> and its value is defined as COALESCE(x, y), where x and y are the values in the column c for the first and second table, respectively. But how can x be NULL, given that it compares as equal to its corresponding value in the second table? And if it's never NULL when a result row is created, the resulting value is just x, no need for COALESCE, it seems. Probably my understanding is incomplete, but what do I miss?
Why does the SQL standard define JOIN USING via COALESCE of corresponding join column values given they can't be NULL?
Viewed 55
1 Answers
What you say is true for an INNER JOIN, but not for an OUTER JOIN, where common columns can contain NULLs.
COALESCE is used for the case of JOIN USING in both the Syntax Rules section and the General Rules section (which actually describes the result value). Each rules section has a single COALESCE/USING subsection that applies regardless of whether the JOIN is INNER or OUTER.
The JOIN type has four cases, INNER/LEFT/RIGHT/OUTER; the COALESCE argument has two cases, x/y; the argument source has two cases, input vs OUTER-generated NULL; the input has two cases, NULL/non-NULL. If you look at all the cases then you will find that the COALESCE happens to return the right thing in every case.
What is the difference between “INNER JOIN” and “OUTER JOIN”?
Related