Stone Coast Web Design Blog

Here are some examples of using IN vs. EXISTS

--Customers from Spain who made no orders 

SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
 AND NOT EXISTS
    (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID)
SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
    AND CustomerID NOT IN(SELECT CustomerID FROM Orders 
                          WHERE CustomerID IS NOT NULL)  

  
--Return customer for whom you cannot find any employee from the USA
--For whom you cannot find any order placed for the subject customer and by the select employee                          

SELECT * FROM Customers AS C
WHERE NOT EXISTS
    (SELECT * FROM Employees AS E
     WHERE Country = 'USA'
    AND NOT EXISTS
        (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID
        AND O.EmployeeID = E.EmployeeID))