CODEWITHSUNDEEP
sql-server
BumbleBee
BumbleBee

Reputation: 10799

SQL joins vs subqueries vs exists for matching and filtering rows

I have a table which has the following fields : Id, Name, Dept

Sample data :

ID    Name    Dept 
1     John      A   
2     Mary      A   
3     Tom       A   
4     David     A   
5     Mary      B   
6     David     B

I am trying to get the names which are in Dept'A' but NOT in Dept'B'

I was able to come up with a query which gives the desired results :

select Id, Name, Dept
from test
Where  Dept= 'a' and name not in (
  select name from test where Dept= 'b')

But how can I do the same with joins?

Upvotes: 0

Views: 44

Answers (2)

BuvinJ
BuvinJ

Reputation: 11076

How about:

SELECT A.Id, A.Name, A.Dept
FROM test A, test B
WHERE A.Dept= 'a' 
AND B.Dept = 'b'
AND A.Name <> B.Name

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use LEFT JOIN:

SELECT t1.*
FROM test t1
LEFT JOIN test t2
    ON t1.Name = t2.Name
    AND t2.Dept = 'B'
WHERE
    t1.Dept = 'A'
    AND t2.ID IS NULL

But I prefer the NOT EXISTS approach:

SELECT *
FROM test t
WHERE
    t.Dept = 'A'
    AND NOT EXISTS(SELECT 1 FROM test WHERE Name = t.Name AND Dept = 'B')

Aaron Bertrand wrote an article comparing the different techniques to check for existence of a row, with NOT EXISTS typically being the best choice.

Upvotes: 4

Related Questions