124

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.

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
Chaddeus
  • 13,134
  • 29
  • 104
  • 162

7 Answers7

338

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

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • 10
    finally! why don't they have these in text books and why did my lecturers at uni not have these?! they used the worst possible explanations in the world, not remotely close to these! – pythonian29033 Aug 13 '14 at 13:04
  • 6
    This is gold. I hate to write a comment that has no content but effusive praise, but come on! This is an awesome answer. Thank you, @Pranay Rana. – 0xbe5077ed Jun 06 '16 at 17:53
  • 1
    Please explain for me, why `B.Key IS NULL` but we still comparable `A.Key = B.Key`? – Vy Do Jul 24 '16 at 11:01
  • 1
    @DoNhuVy simple, the comparison is in the "ON" clause, in a LEFT or RIGHT join if there is no matching row then a row with all NULLs is joined, after which you test for IS NULL to know that there is no matching row. (This only works, by the way, if the field you are testing is NOT NULL, i.e. cannot have NULL for some other reason) – Gregory Magarshak Apr 16 '17 at 06:03
  • I am using the below query: SELECT A.* FROM #PurgeFilesListNew A FULL OUTER JOIN #DoNotPurgeFilesListNew B ON A.JobFileId = B.JobFileId AND A.AccountID = B.AccountID WHERE A.JobFileId IS NULL OR B.JobFileId IS NULL AND A.AccountID IS NULL OR B.AccountID IS NULL Basically, I am having two comparing two values in "ON" clause. It is working fine, but the query is returning null row for those rows which are not matched. How to solve this? Please help – HarshSharma Jan 16 '20 at 07:14
  • a picture is better than 1000 words – Nelson Sequiera May 03 '21 at 09:20
  • This is not the answer. – Alex Byrth Aug 10 '22 at 19:29
15
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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • well he didn't post what DBRMS is used, however in MySql `LEFT JOIN` outperforms `NOT EXIST` – The Scrum Meister Dec 30 '10 at 06:30
  • @The Scrum Meister: did I say faster? Search for IN vs EXISTS vs JOIN to discover the semantic and logical differences... – gbn Dec 30 '10 at 06:39
  • @gbn Sorry, i thought by "better choice" you meant faster. Can you then please explain in what way is it a better choice? http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ – The Scrum Meister Dec 30 '10 at 06:45
  • 1
    @The Scrum Meister: generally, any kind of JOIN may need a DISTINCT. NOT IN with a null in the list gives false. IN/EXISTS behave the same. However, the only "safe" construct is (NOT) EXISTS unless you like inconsistency – gbn Dec 30 '10 at 07:38
  • @The Scrum Meister: http://explainextended.com/2009/06/16/in-vs-join-vs-exists/ or http://stackoverflow.com/questions/3305891/join-or-correlated-subquery-with-exists-clause-which-one-is-better – gbn Dec 30 '10 at 07:45
  • @gbn Since he is selecting by the Primary Key, no DISTINCT is needed. and the links you posted were about `INNER JOIN`. what does that have to do with our case? – The Scrum Meister Dec 30 '10 at 16:38
  • the fastest solution is always the best, don't let project managers and software architects tell u something different :^) – clockw0rk Aug 12 '21 at 10:33
5

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
David Sherret
  • 101,669
  • 28
  • 188
  • 178
The Scrum Meister
  • 29,681
  • 8
  • 66
  • 64
4

Another solution is:

SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2)
Unheilig
  • 16,196
  • 193
  • 68
  • 98
Ali Akbar
  • 370
  • 3
  • 9
3
SELECT P.*
FROM primary_table P
LEFT JOIN secondary_table S on P.id = S.p_id
WHERE S.p_id IS NULL
Tommi
  • 8,550
  • 5
  • 32
  • 51
  • 2
    Got a question.. if we are using the condition `P.key = S.key` and then say `where S.key IS NULL` , then doesn't that make P.key null as well ? – Somjit Mar 28 '15 at 04:25
3

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
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
Anil Soman
  • 2,443
  • 7
  • 40
  • 64
0

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.

JennyB
  • 1
  • 1