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

Question

I have two tables, one has primary key other has it as a foreign key.

I want to pull data from the primary table, only if the secondary table does not have an entry containing it's key. Sort of an opposite of a simple inner join, which returns only rows that join together by that key.

Answer 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

Answer 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

Answer 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

Answer 4

Another solution is:

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

Answer 5

SELECT P.*
FROM primary_table P
LEFT JOIN secondary_table S on P.id = S.p_id
WHERE S.p_id IS NULL

Answer 6

If you want to select the columns from First Table "which are also present in Second table, then in this case you can also use EXCEPT. In this case, column names can be different as well but data type should be same.

Example:

select ID, FName
from FirstTable
EXCEPT
select ID, SName
from SecondTable

Answer 7

This was helpful to use in COGNOS because creating a SQL "Not in" statement in Cognos was allowed, but it took too long to run. I had manually coded table A to join to table B in in Cognos as A.key "not in" B.key, but the query was taking too long/not returning results after 5 minutes.

For anyone else that is looking for a "NOT IN" solution in Cognos, here is what I did. Create a Query that joins table A and B with a LEFT JOIN in Cognos by selecting link type: table A.Key has "0 to N" values in table B, then added a Filter (these correspond to Where Clauses) for: table B.Key is NULL.

Ran fast and like a charm.