Database Reference
In-Depth Information
▼
Output
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
▼
Analysis
Take a look at the preceding code. The
SELECT
statement starts in the same
way as all the statements you've looked at thus far, by specifying the columns
to be retrieved. The big difference here is that two of the specified columns
(
prod_name
and
prod_price
) are in one table, whereas the other (
vend_
name
) is in another table.
Now look at the
FROM
clause. Unlike all the prior
SELECT
statements, this one
has two tables listed in the
FROM
clause,
vendors
and
products
. These are
the names of the two tables that are being joined in this
SELECT
statement.
The tables are correctly joined with a
WHERE
clause that instructs MariaDB to
match
vend_id
in the
vendors
table with
vend_id
in the
products
table.
Notice that the columns are specified as
vendors.vend_id
and
products.
vend_id
. This fully qualified column name is required here because if you
just specified
vend_id
, MariaDB cannot tell which
vend_id
columns you are
referring to (as there are two of them, one in each table).
Caution
Fully Qualifying Column Names You must use the fully qualified column name (table
and column separated by a period) whenever there is possible ambiguity about to which
column you are referring. MariaDB returns an error message if you refer to an ambigu-
ous column name without fully qualifying it with a table name.