When data are spread across multiple tables, retrieval of that information
becomes an important issue. Figure
indicates how to find the customer name for a given order number.
test=> SELECT customer_id FROM salesorder WHERE order_id = 14673;
customer_id
-------------
648
(1 row)
test=> SELECT name FROM customer WHERE customer_id = 648;
name
--------------------------------
Fleer Gearworks, Inc.
(1 row)
It uses two queries. The first gets the customer_id for order number 14673. The returned customer identification number of 648 then is used in the WHERE clause of the next query. That query finds the customer name record where the customer_id equals 648. We call this two-query approach a manual join , because the user manually took the result from the first query and placed that number into the WHERE clause of the second query.
Fortunately, relational databases can
perform this type of join automatically. Figure
shows the same join as in Figure
but places it in a single query.
test=> SELECT customer.name -- query result
test-> FROM customer, salesorder -- query tables
test-> -- table join
test-> WHERE customer.customer_id = salesorder.customer_id AND
test-> salesorder.order_id = 14673; -- query restriction
name
--------------------------------
Fleer Gearworks, Inc.
(1 row)
This query shows all of the elements necessary to perform the join of two tables:
Notice that Figure
qualifies each column name by prefixing it with the table name, as
discussed in Section
. While such
prefixing is optional in many cases, it is required in this example
because the column customer_id exists in both tables mentioned
in the FROM clause, customer and salesorder.
Without such prefixing, the query would generate an error: ERROR:
Column 'customer_id' is ambiguous.
You can also perform the join in the opposite direction too. In the
previous query, the order number was supplied and the customer name
returned. In Figure ,
the customer name is supplied and the order number returned.
test=> SELECT salesorder.order_id
test-> FROM salesorder, customer
test-> WHERE customer.name = 'Fleer Gearworks, Inc.' AND
test-> salesorder.customer_id = customer.customer_id;
order_id
----------
14673
(1 row)
The order of items in the FROM and WHERE clauses has also been switched; the ordering of items is not important in these clauses.