Database Reference
In-Depth Information
records where there is no matching
order_id
.
NULL
is a special SQL keyword that
denotes an unknown value. Without the
WHERE
clause, the output also would have
included all the records that had a matching
customer_id
in the
orders
table,
as seen in the following SQL query.
SELECT c.customer_id,
c.first_name,
c.last_name,
o.order_id
FROM orders o
RIGHT OUTER JOIN customer c
ON o.customer_id = c.customer_id
ORDER BY c.last_name,
c.first_name
LIMIT 5
customer_id first_name last_name order_id
143915 Abigail Aaron
222599 Addison Aaron 50314-7576-3355-6960
222599 Addison Aaron 21007-7541-1255-3531
222599 Addison Aaron 19396-4363-4499-8582
222599 Addison Aaron 69225-1638-2944-0264
In the query results, the first customer, Abigail Aaron, had not placed an order, but
the next customer, Addison Aaron, has placed at least four orders.
There are several other types of join statements. The
LEFT OUTER JOIN
performs
the same functionality as the
RIGHT OUTER JOIN
except that all records from the
table on the left-hand side (LHS) of the join are considered. A
FULL OUTER JOIN
includes all records from both tables regardless of whether there is a matching
record in the other table. A
CROSS JOIN
combines two tables by matching every
row of the first table with every row of the second table. If the two tables have 100
and 1,000 rows, respectively, then the resulting
CROSS JOIN
of these tables will
have 100,000 rows.
The actual records returned from any join operation depend on the criteria stated
in the
WHERE
clause. Thus, careful consideration needs to be taken in using a
WHERE
clause, especially with outer joins. Otherwise, the intended use of the outer
join may be undone.
