CODEWITHSUNDEEP
postgresqljoinnullnatural-join
James Chen
James Chen

Reputation: 33

NATURAL FULL OUTER JOIN or USING, if common attribute is NULL

So if table A is:

no | username 
1  | admin
2  | chicken

And table B is:

id | no
a  | 1
b  | 3
c  | 4

Then, I do a NATURAL FULL OUTER JOIN as so:

SELECT no
FROM A NATURAL FULL OUTER JOIN
     B;

Then, what is the result? And is the result the same for all PostgreSQL implementations?

Because does the 'no' come from table A, or table B, it is ambiguous. But, NATURAL joins combine the 'no'. But what if one of the 'no' is ambiguous, i.e. A.no IS NOT NULL, but B.no IS NULL, which of the 'no' does it pick? And what if A.no and B.no are both NULL?

TL;DR: So the question is, WHAT is the value of the no in SELECT no: Is it the A.no or B.no, or is it the COLAESCE of them?

SELECT no
FROM A NATURAL FULL OUTER JOIN
     B;

Upvotes: 1

Views: 2434

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

First, don't use natural for joins. It is a bug waiting to happen. As you note in your question, natural chooses the join keys based on the names of columns. It doesn't take types into account. It doesn't even take explicitly declared foreign key relationships in to account.

The particularly insidious problem, though, is that someone reading the query does not see the join keys. That makes is much harder to debug queries or to modify/enhance them.

So, my advice is to use using instead.

SELECT no
FROM A FULL OUTER JOIN
     B
     USING (no);

What does a full join return? It returns all rows from both tables, regardless of whether the join matches or not. Because a NULL comparison always fails, NULL will not match in the join conditions.

For example, the following query returns 4 rows not 2 containing a NULL value:

with x as (
      select NULL::int as id union all select NULL as id
     )
select id
from x full join
     x y
     using (id);

You would get the same result with a natural join, but I simply don't use that construct.

I'm not 100% sure, but I'm pretty sure that all versions of Postgres that support full join would work the same way. This behavior is derived specifically from the ANSI definitions of joins and join conditions.

Upvotes: 2

Related Questions