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.
| 
No comments:
Post a Comment