Database Reference
In-Depth Information
▼
Analysis
The two tables needed in this query are actually the same table, and so the
products
table appears in the
FROM
clause twice. Although this is perfectly
legal, any references to table
products
would be ambiguous because MariaDB
could not know to which instance of the
products
table you are referring.
To resolve this problem, table aliases are used. The first occurrence of
products
has an alias of
p1
, and the second has an alias of
p2
. Now those
aliases can be used as table names. The
SELECT
statement, for example, uses
the
p1
prefix to explicitly state the full name of the desired columns. If it did
not, MariaDB would return an error because there are two columns named
prod_id
and
prod_name
. It cannot know which one you want (even though,
in truth, they are one and the same). The
WHERE
clause first joins the tables (by
matching
vend_id
in
p1
to
vend_id
in
p2
), and then it filters the data by
prod_id
in the second table to return only the desired data.
Tip
Self Joins Instead of Subqueries Self joins are often used to replace statements using
subqueries that retrieve data from the same table as the outer statement. Although the
end result is the same, sometimes these joins execute far more quickly than do subque-
ries. It is usually worth experimenting with both to determine which performs better.
Whenever tables are joined, at least one column appears in more than one table
(the columns being joined). Standard joins (the inner joins you learned about
in the previous chapter) return all data, even multiple occurrences of the same
column. A
natural join
simply eliminates those multiple occurrences so only one
of each column is returned.
How does it do this? The answer is it doesn't—you do it. A natural join is a
join in which you select only columns that are unique. This is typically done
using a wildcard (
SELECT *
) for one table and explicit subsets of the columns
for all other tables. The following is an example:
▼
Input
SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';
