Полезная информация

next up previous contents index
Next: Three and Four Table Up: Joining Tables Previous: Creating Joined Tables

Performing Joins

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.

  
Figure: Finding customer name using two queries
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...works,~Inc.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\par (1~row)\end{list}\par
\end{figure}

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.

  
Figure: Finding customer name using one query
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...works,~Inc.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
\par (1~row)\end{list}\par
\end{figure}

This query shows all the elements necessary to perform the join of two tables:

Internally, the database performs the join by:

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' is ambiguous.

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.

  
Figure: Finding order number for customer name
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...-{}-{}-{}-{}-{}-{}-{}-{}-
\par ~~~~14673
\par (1~row)\end{list}\par
\end{figure}

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.


next up previous contents index
Next: Three and Four Table Up: Joining Tables Previous: Creating Joined Tables
Bruce Momjian
1999-11-21