CODEWITHSUNDEEP
mysqlsqldatabasejoin
Dot NET
Dot NET

Reputation: 4907

SQL outer join which omits common records

I'm trying to formulate an SQL FULL OUTER JOIN, which includes all values in table A and table B, but not those values common between them.

I have searched the internet, and stumbled upon the following SQL code:

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

Which can be illustrated like so:

enter image description here

I'm not sure I understand the IS null parts. Could the SQL be carried out by simply stating something like the following as a WHERE condition? :

WHERE TableA.id <> TableB.id

Upvotes: 2

Views: 310

Answers (2)

JodyT
JodyT

Reputation: 4412

SELECT Name, ID FROM TableA UNION SELECT Name, ID FROM TableB 
EXCEPT
SELECT Name, ID FROM TableB INTERSECT SELECT Name, ID FROM TableA

The first select gets all rows from table A and table B and combines this into 1 result set. The second select selects all rows that are common between the two. What the except does is select all rows from the first select - all rows from the second select.

What you end up with is all rows - the rows that are common between the two tables.

Upvotes: 0

Petter Brodin
Petter Brodin

Reputation: 2189

What is it you don't understand about the IS NULL clauses?

In an OUTER JOIN (LEFT, RIGHT, FULL) there's a chance that columns from the outer table could end up as NULL.

The clauses

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

are simply saying that one of the IDs has to be NULL, I.E. if you have a row from TableA there can't exist a matching row from TableB and vice versa.

Upvotes: 2

Related Questions