Database Reference
In-Depth Information
▼
Output
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
▼
Analysis
This first solution uses subqueries. The inner
SELECT
statement does a simple
retrieval to return the
vend_id
of the vendor that makes item
DTNTR
. That ID
is the one used in the
WHERE
clause of the outer query so all items produced
by that vendor are retrieved. (You learned all about subqueries in Chapter 14,
“Working with Subqueries.” Refer to that chapter for more information.)
Now look at the same query using a join:
▼
Input
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
▼
Output
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
