Performing Joins

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:

• 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 clause.
• 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 carrying out the following operations:

• 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 table.
• customer.name: Return name from the customer table.

That is, the database performs the same steps as the 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, 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.