CODEWITHSUNDEEP
sqlsql-serverjoin
MaGi
MaGi

Reputation: 87

Selecting those that does not match

I'm very new to this so I will try to explain this as good as I can, also try to explain as basic as you can, thank you!

I have a list with bills that a company gets when they buy something, I'm trying to select only those that have NOT bought anything (therefore their CustomerID is not on the Bill-list). How do I do that?

Here is my code so far (renamed some stuff to english so you hopefully understand better):

SELECT Name, Postnr+' '+City as Postadress
FROM Bill
RIGHT JOIN Customer
ON Customer.CustomerID = Bill.CustomerID

Upvotes: 0

Views: 298

Answers (3)

n-dru
n-dru

Reputation: 9430

SELECT Name, Postnr+' '+City as Postadress
FROM Bill
RIGHT JOIN Customer
ON Customer.CustomerID = Bill.CustomerID
WHERE Customer.CustomerID NOT IN (SELECT Bill.CustomerID FROM Bill)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460098

Meanwhile i prefer NOT EXISTS since it's more readable, maintainable and has no issues with null-values:

SELECT Name, 
       Postnr + ' ' + City AS Postadress 
FROM   Customer c 
WHERE  NOT EXISTS (SELECT 1 FROM   bill b 
                   WHERE  b.CustomerID = c.CustomerID)

Another (possibly less efficient) way is an OUTER JOIN:

SELECT Name, 
       Postnr + ' ' + City AS Postadress 
FROM   Customer c 
       LEFT OUTER JOIN Bill b 
                    ON c.CustomerID = b.CustomerID 
WHERE  b.CustomerID IS NULL

Here's a list of all approaches:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

Upvotes: 1

wumpz
wumpz

Reputation: 9131

You explained it quite well. You should have made your SQL right from your explanation: Customer with their CustomerID not in Bill list. Now this would be like:

select * from Customer where CustomerID not in (select CustomerID from Bill)

Dependent on your database system the syntax could vary slightly e.g. not CustomerID in ...

Upvotes: 0

Related Questions