Real life examples - when to use a subquery with IN vs. a subquery with derived tables(Querying Relational Databases)

Hi Jan,

tl;dr Use a regular old JOIN when possible. Use a derived table otherwise.

In almost every case, a WHERE...IN(subquery) or a derived table should be rewritten to use a JOIN instead. (Other subqueries like WHERE...NOT EXISTS(subquery) are really useful but that's off-topic.) When a JOIN is insufficient, the derived table approach is almost always better than WHERE...IN(subquery) because it's more powerful/flexible (you can only return 1 column from WHERE...IN(subquery)) and I believe may perform better with large result set.

Using WHERE...IN(subquery) Example: Our client sells products. They want productorder info for their biggest sales The productorder table includes product id, quantity purchased, what the customer was charged, etc. The sale table has info on sales like date, totalrevenue, etc.

SELECT *
FROM productorder
WHERE date IN (SELECT date FROM sale ORDER BY totalrevenue DESC LIMIT 3);

After racking my brain, the above query is the only example I could come up with where I would use WHERE...IN(subquery) because it's just a little cleaner. Even so, it could also be written as a derived table, with the same performance and results:

SELECT po.* 
FROM productorder po
INNER JOIN (SELECT date FROM sale ORDER BY totalrevenue DESC LIMIT 3) AS s ON s.date = po.date;

Using derived table Example: Our client is considering selling some products in bulk so they need info on how their customers purchase large quantities. For each product, they need the product's id and the total amount customers have spent on large orders.

SELECT productid, SUM(charged) AS 'Total spent on large orders'
FROM
(SELECT po.productid, po.charged FROM productorder AS po WHERE po.quantity > 20) AS largeorder
GROUP BY productid

There's just no way to write the above query using a JOIN or WHERE...IN because of the filtering and aggregation, and the multiple columns returned from the derived table.