CODEWITHSUNDEEP
sql
Koray Tugay
Koray Tugay

Reputation: 23800

How to avoid chaining WHERE .. IN statements in SQL?

Here is a sample query:

SELECT customerName from customers 
WHERE customerNUMBER IN 
( SELECT customerNumber FROM orders 
  WHERE orderNumber 
    IN ( SELECT orderNumber FROM orderdetails 
    INNER JOIN products on orderdetails.productCode = products.productCode 
    where products.buyPrice > 100 ));

I believe the tables are self explanatory.

Is there a better way to do this?

SQL noob here.

Upvotes: 1

Views: 196

Answers (3)

wildplasser
wildplasser

Reputation: 44250

EXISTS allows you to combine all the subquery parts. Duplicates are not an issue:

  • only the customers table is visible from the outer query
  • EXISTS yields a boolean result for each customers row:

--

SELECT customerName from customers cc
WHERE EXISTS (
        SELECT * 
        FROM orders oo
        JOIN orderdetails od ON od.orderNumber = oo.orderNumber
        JOIN products pr ON od.productCode = pr.productCode
        WHERE oo.customerNUMBER = cc.customerNumber
        AND pr.buyPrice > 100 
        );

Upvotes: 2

Taryn
Taryn

Reputation: 247810

My suggestion would be to change this to JOIN syntax instead of all of the WHERE/IN clause filtering:

select c.customerName
from customer c
inner join orders o
  on c.customerNumber = o.customerNumber
inner join orderdetails od
  on o.orderNumber = od.orderNumber
inner join products p
  on od.productCode = p.productCode
where p.buyPrice > 100;

If needed you might have to add a DISTINCT to the query in the event there are duplicates.

Upvotes: 13

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

Use normal inner joins throughout, and toss in a group by or a distinct clause if you need to eliminate dups:

select customers.*
from customers
join orders on ...
join orderdetails on ...
join products on ...
group by customers.customerNumber

Upvotes: 8

Related Questions