CODEWITHSUNDEEP
sqljoinsetset-theory
Gabriel Oliveira
Gabriel Oliveira

Reputation: 102

How does this where clause work?

I found this link explaining joins visually and something caught my attention.

The very last example says the following:

To produce the set of records unique to Table A and Table B, 
we perform the same full outer join, then exclude the records 
we don't want from both sides via a where clause.

full join - venn diagram

The two tables for this example contain the following:

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

Query and result:

SELECT * 
FROM TableA
FULL OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableA.id IS null
   OR TableB.id IS null

Output:

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

What i don't understand is, how can the where clause find null IDs on those tables ?

** WHERE TableA.id IS null
OR TableB.id IS null **

Could it be that, after the full join takes place, a bunch of nulls tuples are inserted in the final output on both tables(as expected for the full join), and then the where clause would take its null IDs from?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

An outer join includes all records in one or both tables, even if there are no matches.

In particular, a full outer join returns all records in each table even when the on clause evaluates to non-true. The columns in the other table are not null. So, your query returns the records that are in one table, but not both.

Note that this may not be the best approach for getting this information, because duplicate join keys can multiply the number of records.

Upvotes: 1

Related Questions