How to exclude rows that don't join with another table?

Solution 1:

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Full image of join

From aticle : http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

Solution 2:

SELECT
   *
FROM
   primarytable P
WHERE
   NOT EXISTS (SELECT * FROM secondarytable S
     WHERE
         P.PKCol = S.FKCol)

Generally, (NOT) EXISTS is a better choice then (NOT) IN or (LEFT) JOIN

Solution 3:

use a "not exists" left join:

SELECT p.*
FROM primary_table p LEFT JOIN second s ON p.ID = s.ID
WHERE s.ID IS NULL

Solution 4:

Another solution is:

SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2)

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