3.4 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 10 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 JOIN part p

ON p.supplier_id = s.supplier_id

WHERE 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 query runs faster.