Database Reference
In-Depth Information
▼
Input
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
▼
Analysis
The
SELECT
in the statement is the same as the preceding
SELECT
statement,
but the
FROM
clause is different. Here the relationship between the two tables is
part of the
FROM
clause specified as
INNER JOIN
. When using this syntax the
join condition is specified using the special
ON
clause instead of a
WHERE
clause.
The actual condition passed to
ON
is the same as would be passed to
WHERE
.
Note
Which Syntax To Use? Per the ANSI SQL specification, use of the
INNER JOIN
syntax is preferable. Furthermore, while the using the
WHERE
clause to define joins is
indeed simpler, using explicit join syntax ensures that you will never forget the join con-
dition and can impact performance, too (in some cases).
SQL imposes no limit to the number of tables that may be joined in a
SELECT
statement. The basic rules for creating the join remain the same. First list all the
tables, and then define the relationship between each. Here is an example:
▼
Input
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
▼
Output
+----------------+-------------+------------+----------+
| prod_name | vend_name | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil | Anvils R Us | 5.99 | 10 |
| 1 ton anvil | Anvils R Us | 9.99 | 3 |
| TNT (5 sticks) | ACME | 10.00 | 5 |
| Bird seed | ACME | 10.00 | 1 |
+----------------+-------------+------------+----------+
