Table Aliases and Self-joins

 In Section , you saw how to refer to specific tables in the FROM clause using a table alias. Figure  shows a rewrite of the query in Figure  using aliases.  

        SELECT order_id
        FROM   customer c, salesorder s
        WHERE  c.code = 'FLE001' AND
               c.customer_id = s.customer_id;  

A c is used as an alias for the customer table, and an s is used as an alias for the salesorder table. Table aliases are handy in these cases.

With table aliases, you can even join a table to itself in a self-join. In this case, the same table is given two different alias names. Each alias then represents a different instance of the table. This concept might seem to have questionable utility, but it can prove useful. Figure  shows practical examples.  

        SELECT c2.name 
        FROM   customer c, customer c2
        WHERE  c.customer_id = 648 AND
               c.zipcode = c2.zipcode; 
         
        SELECT c2.name, s.order_id  
        FROM customer c, customer c2, salesorder s
        WHERE c.customer_id = 648 AND
              c.zipcode = c2.zipcode AND
              c2.customer_id = s.customer_id AND
              c2.customer_id <> 648; 
         
        SELECT c2.name, s.order_id, p.name
        FROM   customer c, customer c2, salesorder s, part p
        WHERE  c.customer_id = 648 AND
               c.zipcode = c2.zipcode AND
               c2.customer_id = s.customer_id AND
               s.part_id = p.part_id AND
               c2.customer_id <> 648;  

For simplicity, results are not shown for these queries.

The first query in Figure  uses c as an alias for the customer table and c2 as another alias for customer. It finds all customers in the same ZIP code as customer number 648. The second query finds all customers in the same ZIP code as customer number 648. It then finds the order numbers placed by those customers. We have restricted the c2 table's customer identification number to be not equal to 648 because we do not want customer 648 to appear in the result. The third query goes further, retrieving the part numbers associated with those orders.