SQL - INNER JOIN with AND vs using sub-query

Question

I'm practicing questions for the book "SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach ". Question 31 is -

Customers with no orders for EmployeeID 4

One employee (Margaret Peacock, EmployeeID 4) has placed the most orders. However, there are some customers who've never placed an order with her. Show only those customers who have never placed an order with her.

The solution I did creates a temporary table "cte" and joints it with an existing table. Not pretty or readable actually -

with cte as
    (Select Customers.CustomerID 
    from customers
    where CustomerID not in 
        (select Orders.CustomerID from orders where orders.EmployeeID = '4'))

 select *  
 from cte left join 
    (select CustomerID from Orders where Orders.EmployeeID = '4') O
     on cte.CustomerID = O.CustomerID

I found the following solution online -

SELECT c.CustomerID, o.CustomerID
FROM Customers AS c
LEFT JOIN Orders AS o ON o.CustomerID = c.CustomerID AND o.EmployeeID = 4
WHERE o.CustomerID IS NULL;

Which is nicer.

My question - when can I use OR, AND clauses in a JOIN? What are the advantages? Is it the fact that a JOIN is executed before the where clause?

Thanks,

Asaf

"Is it the fact that a JOIN is executed before the where clause?" (Obviously,) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, May 16 '19 at 02:30
[Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Aug 19 '19 at 04:01

score 1 · Answer 1 · answered May 15 '19 at 11:01

1

A JOIN condition can contain any boolean comparison, even subqueries using EXISTS and correlated subqueries. There is no limitation on what can be expressed.

Just a note, however. = and AND are good for performance. Inequalities tend to be performance killers.

As for your particular problem, I think the following is a more direct interpretation of the question:

SELECT c.CustomerID
FROM Customers c
WHERE NOT EXISTS (SELECT 1
                  FROM Orders o 
                  WHERE o.CustomerID = c.CustomerID AND
                        o.EmployeeID = 4
                 );

That is, get all customers for whom no order exists with employee 4.

answered May 15 '19 at 11:01

Gordon Linoff

1,242,037
58
646
786

Thank you! Your solution is correct however the book wanted me to provide an additional column of the Customer ID from the Orders table. – Assaf May 15 '19 at 11:03
@Assaf . . . You can just include the customer id from the customer table. The matching condition requires that they be the same. – Gordon Linoff May 15 '19 at 12:30

Andrew Skirrow · Answer 2 · 2019-05-15T11:18:50.757

1

Generally I'd recommend that you always choose the most readable version of the query unless you can actually measure a performance difference with realistic data. The cost based optimiser should pick a good way of executing the query to return the results you want in this case.

For me the JOIN is a lot more readable than the CTE.

edited May 15 '19 at 11:18

answered May 15 '19 at 11:07

Andrew Skirrow

3,402
18
41

Thank you for your answer. First I did try this approach but no records where returned. The book says - "Note that with outer joins, the filters on the where clause are applied after the join." – Assaf May 15 '19 at 11:15

score 0 · Answer 3 · answered Aug 19 '19 at 00:46

0

Here's is another Solution

SELECT * FROM(
(SELECT Customers.CustomerID AS Customers_ID
      FROM Customers) AS P
          LEFT JOIN  
           (Select Orders.CustomerID from Orders
            where Orders.EmployeeID=4) as R
             on R.CustomerID = P.Customers_ID
              )
               WHERE R.CustomerID  IS NULL
               ORDER BY R.CustomerID DESC

answered Aug 19 '19 at 00:46

Ricardo Roa

177
3
12

This doesn't answer the question that was asked. – philipxy Aug 19 '19 at 04:00

SQL - INNER JOIN with AND vs using sub-query

3 Answers3