Table aliases in SQL queries
To save typing and add additional functionality, table aliases are sometimes used in queries. Consider an example that finds all inventory details of wine #183:
SELECT * FROM inventory i, wine w
WHERE i.wine_id = 183 AND
i.wine_id = w.wine_id;
In this query, the FROM clause specifies aliases for the table names. The alias inventory i means than the inventory table can be referred to as i elsewhere in the query. For example, i.wine_id is the same as inventory.wine_id. This saves typing in this query.
Aliases are powerful for complex queries that need to use the same table twice but in different ways. For example, to find any two customers with the same surname, you can write the query:
SELECT c1.cust_id, c2.cust_id FROM
customer c1, customer c2 WHERE
c1.surname = c2.surname AND
c1.cust_id != c2.cust_id;
The final clause, c1.cust_id!=c2.cust_id, is essential; without it, all customers are reported as answers. This occurs because all customers are rows in tables c1 and c2 and, for example, a customer with cust_id=1 in table c1 has-of course-the same surname as the customer with cust_id=1 in table c2.