Database Reference
In-Depth Information
•
WHERE:
Specifies the criterion or filter to be applied
In a relational database, it is often necessary to access related data from multiple
tables at once. To accomplish this task, the SQL query uses
JOIN
statements to
specify the relationships between the multiple tables.
11.1.1 Joins
Joins enable a database user to appropriately select columns from two or more
tables. Based on the relationship diagram in
Figure 11.1
,
the following SQL query
provides an example of the most common type of join: an inner join.
SELECT c.customer_id,
o.order_id,
o.product_id,
o.item_quantity AS qty
FROM orders o
INNER JOIN customer c
ON o.customer_id = c.customer_id
WHERE c.first_name = 'Mason'
AND c.last_name = 'Hu'
customer_id order_id product_id qty
666730 51965-1172-6384-6923 33611 5
666730 79487-2349-4233-6891 34098 1
666730 39489-4031-0789-6076 33928 1
666730 29892-1218-2722-3191 33625 1
666730 07751-7728-7969-3140 34140 4
666730 85394-8022-6681-4716 33571 1
This query returns details of the orders placed by customer Mason Hu. The SQL
query joins the two tables in the
FROM
clause based on the equality of the
customer_id
values. In this query, the specific
customer_id
value for Mason
Hu does not need to be known by the programmer; only the customer's full name
needs to be known.
Some additional functionality beyond the use of the
INNER JOIN
is introduced
in this SQL query. Aliases
o
and
c
are assigned to tables
orders
and
customer
,
respectively. Aliases are used in place of the full table names to improve the
readability of the query. By design, the column names specified in the
SELECT
