Joins and Subqueries
Joins can sometimes be used to good advantage in reformulating SELECT statements that would otherwise contain subqueries. Consider the problem of obtaining a list of suppliers of parts for which your inventory has dropped below ten units. You might begin by writing a query such as the following:
SELECT supplier_id, name
FROM supplier s
WHERE EXISTS (SELECT *
FROM part p
WHERE p.inventory_qty < 10
AND p.supplier_id = s.supplier_id);The subquery in this SELECT statement is a correlated subquery, which means that it will be executed once for each row in the supplier table. Assuming that you have no indexes on the INVENTORY_QTY and SUPPLIER_ID columns of the PART table, this query could result in multiple, full-table scans of the PART table. It’s possible to restate the query using a join, for example:
SELECT s.supplier_id, s.name FROM supplier s, part p WHERE p.supplier_id = s.supplier_id AND p.inventory_qty < 10;
Whether the join version or the subquery version of a query is more efficient depends on the specific situation. It may be worth your while to test both approaches to see which has a lower cost.