SQL advanced -> Avoid to use subquery returning multiple rows dataset

The mission is to create phone list of customers that buy products like John Depp.

1. At first we find list of items that customer John Depp buys. To do this we make join between 3 tables (Customers, Invoices, Invoices-Lines) :
SELECT a.ItemCode
FROM (InvLines AS a INNER JOIN Invoice AS b ON a.DocKey = b.DocKey) INNER JOIN Customers AS c ON b.CustomerID = c.CustomerID
WHERE c.CustomerName = 'John Depp'
GROUP BY a.ItemCode

2. On the next step we retrieve phones list from from Customers table while checking that products purchased by these people are the same like in the purchase list of John Depp:
SELECT a.CustomerID, c.CustomerName, c.phone1
FROM (Invoice AS a INNER JOIN InvLines AS b ON a.DocKey = b.DocKey)
INNER JOIN Customers AS c ON a.CustomerID = c.CustomerID
WHERE c.CustomerName <> 'John Depp' AND b.ItemCode IN
(
SELECT a.ItemCode
FROM (InvLines AS a INNER JOIN Invoice AS b ON a.DocKey = b.DocKey)
INNER JOIN Customers AS c ON b.CustomerID = c.CustomerID
WHERE c.CustomerName = 'John Depp'
GROUP BY a.ItemCode
)
GROUP BY a.CustomerID, c.CustomerName, c.phone1

Here Subquery was used in the WHERE clause together with the IN operator. Smarties says that this kind of queries usually works slowly, cause many DBMS systems executes Subquery for every record in the outer query.

3. One of alternative ways is to incorporate Subquery as a dataset participating in the JOIN:
SELECT a.CustomerID, c.CustomerName, c.phone1
FROM ((Invoice AS a INNER JOIN InvLines AS b ON a.DocKey=b.DocKey)
INNER JOIN Customers AS c ON a.CustomerID = c.CustomerID)
INNER JOIN
(
SELECT a.ItemCode
FROM (InvLines AS a INNER JOIN Invoice AS b ON a.DocKey=b.DocKey)
INNER JOIN Customers AS c ON b.CustomerID = c.CustomerID
WHERE c.CustomerName = 'John Depp'
GROUP BY a.ItemCode
) AS d
ON b.ItemCode = d.ItemCode
WHERE c.CustomerName <> 'John Depp'
GROUP BY a.CustomerID, c.CustomerName, c.phone1

4. One more way is to save Subquery into TEMP table. Then the solution consists of two queries executed one after another:
/* first query */
SELECT a.ItemCode INTO tmpICodes
FROM (InvLines AS a INNER JOIN Invoice AS b ON a.DocKey=b.DocKey)
INNER JOIN Customers AS c ON b.CustomerID = c.CustomerID
WHERE c.CustomerName = 'John Depp'
GROUP BY a.ItemCode;

/* second query */
SELECT a.CustomerID, c.CustomerName, c.phone1
FROM ((Invoice AS a INNER JOIN InvLines AS b ON a.DocKey=b.DocKey)
INNER JOIN Customers AS c ON a.CustomerID = c.CustomerID)
INNER JOIN tmpICodes AS d
ON b.ItemCode = d.ItemCode
WHERE c.CustomerName <> 'John Depp'
GROUP BY a.CustomerID, c.CustomerName, c.phone1;

/* remove temp table */
DROP TABLE tmpICodes;


sqlexamples.info