| 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. | 