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?

CodePudding user response：

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.

JOIN type has four cases, INNER/LEFT/RIGHT/OUTER; COALESCE argument has two cases, x/y; argument source has two cases, input vs OUTER-generated NULL; 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”?