Supplying Table Aliases

Earlier, in the section Performing SELECT Statements that Use Two Tables, you saw that one way to qualify columns in queries that involve columns of the same name in different tables is to include the entire table name . The example in the earlier section used the following query:

 SELECT products.name, product_types.name FROM products, product_types WHERE products.product_type_id = product_types.product_type_id; 

Notice that the products and product_types table names are used in the SELECT and WHERE clauses. Repeating the table names is redundant typing. A better way is to define table aliases to reference the tables in the FROM clause, and then use that alias when referencing the tables elsewhere in the query. For example, let s use the alias p for the products table and pt for the product_types table. The query then becomes

 SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id; 

Notice that the alias for each table is specified in the FROM clause after each table name, and that the alias is used with each column reference.

Tip  

Table aliases also make your queries more readable, especially when you start writing longer queries referencing many tables.