Database Reference
In-Depth Information
▼
Output
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
▼
Analysis
Like the inner join seen in the previous chapter, this
SELECT
statement uses
the keywords
OUTER JOIN
to specify the join type (instead of specifying it in
the
WHERE
clause). But unlike inner joins, which relate rows in both tables,
outer joins also include rows with no related rows. When using
OUTER JOIN
syntax you must use the
RIGHT
or
LEFT
keywords to specify the table from
which to include all rows (
RIGHT
for the one on the right of
OUTER JOIN
,
and
LEFT
for the one on the left). The previous example uses
LEFT OUTER
JOIN
to select all the rows from the table on the left in the
FROM
clause (the
customers
table). To select all the rows from the table on the right, you use a
RIGHT OUTER JOIN
as seen in this example:
▼
Input
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id;
Note
No
*=
MariaDB does not support the use of the simplified
*=
and
=*
syntax popular-
ized by other DBMSs.
Tip
Outer Join Types There are two basic forms of outer joins—the left outer join and the
right outer join. The only difference between them is the order of the tables they are
relating. In other words, a left outer join can be turned into a right outer join simply by
reversing the order of the tables in the
FROM
or
WHERE
clause. As such, the two types
of outer join can be used interchangeably, and the decision about which one is used is
based purely on convenience.
