Database Reference
In-Depth Information
clause are also provided in the output. However, the outputted column name can
be modified with the
AS
keyword. In the SQL query, the values of
item_quantity
are displayed, but this outputted column is now called
qty
.
The
INNER JOIN
returns those rows from the two tables where the
ON
criterion
is met. From the earlier query on the
customer
table, there is only one row in
the table for customer Mason Hu. Because the corresponding
customer_id
for
Mason Hu appears six times in the
orders
table, the
INNER JOIN
query returns
six records. If the
WHERE
clause was not included, the query would have returned
millions of rows for all the orders that had a matching customer.
Suppose an analyst wants to know which customers have created an online account
but have not yet placed an order. The next query uses a
RIGHT OUTER JOIN
to
identify the first five customers, alphabetically, who have not placed an order. The
sorting of the records is accomplished with the
ORDER BY
clause.
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
WHERE o.order_id IS NULL
ORDER BY c.last_name,
c.first_name
LIMIT 5
customer_id first_name last_name order_id
143915 Abigail Aaron
965886 Audrey Aaron
982042 Carter Aaron
125302 Daniel Aaron
103964 Emily Aaron
In the SQL query, a
RIGHT OUTER JOIN
is used to specify that all rows from
the table
customer
,
on the right-hand side (RHS) of the join, should be returned,
regardless of whether there is a matching
customer_id
in the
orders
table. In
this query, the
WHERE
clause restricts the results to only those joined customer
