1

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
ByteEater
  • 885
  • 4
  • 13

1 Answers1

1

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”?

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thanks! So let me follow up with a question to ensure that now my understanding is full and correct. Only one of `x` and `y` can be `NULL` in an outer join (and none in an inner join), so it would be equivalent to specify the result as `x` unless it's a right join, in which case `y`. But, as a specification device, `COALESCE` is used which covers all cases. Right? – ByteEater Jun 14 '22 at 22:03
  • 1
    Comments are not for questions, but they are for requesting post clarifications. Can you rephrase to ask for clarification? Your comment isn't entirely clear & doesn't seem to add anything to what I wrote. "Only one of x and y can be NULL in an outer join" No, nulls can be input. "x unless it's a right join, in which case y" No, if both coalesce arguments are null then y is taken whether the join is left, right or full. Nevertheless the coalesce is used to describe the desired result of multiple cases concisely. I suggest you work through examples of cases to see how the coalesce is used. – philipxy Jun 14 '22 at 23:09
  • Indeed, there can be `NULL` in the column in a row that has no matching row in the other table, so in an outer join it will be matched with a row full of `NULL`s, giving two `NULL`s to `COALESCE`. Thanks for pointing out this case. I guess my omission of this possibility was what made my comment not entirely clear to you. But now I believe all my deficiencies in understanding and doubts have been addressed. So thank you again. – ByteEater Jun 15 '22 at 19:45
  • However, to what you wrote later: you can't really tell which argument is taken when both are `NULL` – they're indistinguishable. So my claim that the first one is taken in such case is true as well as yours that's it's the second. They used `COALESCE` as a specification device but the general rule is that implementations don't need to actually call `COALESCE`, just do anything that gives the same observable results. – ByteEater Jun 15 '22 at 19:48