CODEWITHSUNDEEP
sqljoin
Chaddeus
Chaddeus

Reputation: 13356

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

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.

Upvotes: 131

Views: 193232

Answers (7)

Ali Akbar
Ali Akbar

Reputation: 368

Another solution is:

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

Upvotes: 4

JennyB
JennyB

Reputation: 1

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.

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

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

Upvotes: 5

Anil Soman
Anil Soman

Reputation: 2467

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

Upvotes: 3

Pranay Rana
Pranay Rana

Reputation: 176896

alt text

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 alt text

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

Upvotes: 349

Tommi
Tommi

Reputation: 8608

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

Upvotes: 3

gbn
gbn

Reputation: 432261

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

Upvotes: 15

Related Questions