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
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
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
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
Another solution is:
SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2)
Answered by Ali Akbar
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