With data spread across multiple tables, an important issue is how to retrieve
the data. Figure shows how to
find the customer name for a given order number.
Finding customer name using
It uses two queries. The first gets the customer_id for order number
14673. The user then uses the returned customer id of 648 in the
WHERE clause of the next query. That query finds the customer name
record where the customer_id equals 648. We can 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 join automatically. Figure
shows the same join as figure
but in a single query.
Finding customer name using one
This query shows all the elements necessary to perform the join of two tables:
The two tables involved in the join are specified in the FROM clause.
The two columns needed to perform the join are specified as equal in the WHERE
The salesorder table's order number is tested in the WHERE clause.
The customer table's customer name is returned from the SELECT.
Internally, the database performs the join by:
salesorder.order_id = 14673: Find that row in the salesorder table
salesorder.customer_id = customer.customer_id: From the row just found, get
the customer_id. Find the equal customer_id in the customer
customer.name: Return name from the customer table.
You can see the database is performing the same steps as our manual join,
but much faster.
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, in this example it is required
because the column customer_id exists in both tables mentioned in the
FROM clause, customer and salesorder. If this was not
done, the query would generate an error: ERROR: Column 'customer_id'
You can perform the join in the opposite direction too. In the previous query,
the order number is supplied, and the customer name is returned. In figure ,
the customer name is supplied, and the order number returned.
Finding order number for customer
I have switched the order of items in the FROM clause and in the WHERE
clause. The ordering of items is not important in those clauses.