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.