John Doe John Doe - 1 month ago 11
SQL Question

SQL Join to return results where no match occurs

I have a table A and a table B. I want to join A to B but only need results where the join is not possible. I have tried

SELECT B.*
FROM A
RIGHT JOIN B ON A.KEY = B.KEY
WHERE A.KEY IS NULL


This should work but it doesn't. Is my SQL above correct?

Answer Source

You didn't say what you mean by "didn't work", but if either of these queries:

SELECT B.* 
 FROM A 
RIGHT JOIN B ON A.KEY = B.KEY 
 WHERE A.KEY IS NULL

SELECT A.* 
 FROM A 
LEFT JOIN B ON A.KEY = B.KEY 
 WHERE B.KEY IS NULL

return no results, it means that every key in the solid table is matched with at least one key in the outer joined table.

If you're trying to find rows in either A or B that have no matching row in the other, you'll need a query like:

SELECT A.*, B.*, CASE WHEN A.KEY IS NULL THEN 'in B but not A' WHEN B.KEY IS NULL THEN 'in B but not A' END as explanation
 FROM A 
FULL OUTER JOIN B ON A.KEY = B.KEY 
 WHERE A.KEY IS NULL OR B.KEY IS NULL

You might hear people saying "but, a key can never be null because that's what a key is for/primary keys cannot be null!" - yes, but we're doing a join; theyre primary keys in the table, but theyre not necessarily primary keys after a join has occurred. The database will be perfectly happy to generate a row full of NULL values for B (including the b.key column) paired with a row full of actual values from A, if there is no row in B where the key is equal to the value of a.key - this means the key of the table is no longer a key of the join, and it's allowed to be null

Could you test on null against another column than key? Yes, but unless that other column in the table is NOT NULLABLE the key column is the best one to use if you're looking for mismatched rows. This is because it's the only column that we can guarantee will contain a null in the situation where the two tables are joined, and there's no data in this table to match with the other table

To better explain, here is some data:

A
key|val
-------
000|hello
001|world

B
key|val
-------
000|hello
002|there
  • If these two tables are INNER JOINED, only row 000 will result
  • If these two tables are A LEFT JOIN B ON A.KEY=B/KEY WHERE B.KEY IS NULL, row 001 will result
  • If these two tables are A RIGHT JOIN B ON A.KEY=B/KEY WHERE A.KEY IS NULL, row 002 will result
  • If these two tagbles are full outer joined where a or b key is null, rows 001 and 002 will result

If the key column in either A or B contains NULL, that row will never appear in join results