Problem
I’ve got two tables, one with a main key and the other with a foreign key.
Only if the secondary table does not have an entry containing the key do I want to extract data from the primary table. It’s the polar opposite of a simple inner join, which only returns rows that are linked by that key.
Asked by Chaddeus
Solution #1
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
join image in its entirety
The following is an excerpt from the article: http://www.codeproject.com/KB/database/Visual SQL Joins.aspx
Answered by Pranay Rana
Solution #2
SELECT
*
FROM
primarytable P
WHERE
NOT EXISTS (SELECT * FROM secondarytable S
WHERE
P.PKCol = S.FKCol)
In most cases, (NOT) EXISTS is preferable to (NOT) IN or (LEFT) JOIN.
Answered by gbn
Solution #3
use a left join that says “not exists”:
SELECT p.*
FROM primary_table p LEFT JOIN second s ON p.ID = s.ID
WHERE s.ID IS NULL
Answered by The Scrum Meister
Solution #4
Another solution is:
SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2)
Answered by Ali Akbar
Solution #5
SELECT P.*
FROM primary_table P
LEFT JOIN secondary_table S on P.id = S.p_id
WHERE S.p_id IS NULL
Answered by Tommi
Post is based on https://stackoverflow.com/questions/4560471/how-to-exclude-rows-that-dont-join-with-another-table