Use Fully Qualified Column References When Performing Joins

Always include table aliases in your queries and explicitly indicate the appropriate alias for each column referenced in your query (this is known as fully qualifying your column references). That way, the database doesn t have to search for each column in the tables used in your query.

The following example uses the aliases p and pt for the products and product_type s tables respectively, but the query doesn t fully qualify the description and price columns (bad):

 -- BAD (description and price columns not fully qualified)  SELECT p.name, pt.name, description, price   FROM products p, product_types pt   WHERE p.product_type_id = pt.product_type_id   AND p.product_id = 1;  NAME NAME ------------------------------ ---------- DESCRIPTION PRICE -------------------------------------------------- ---------- Modern Science Book A description of modern science 19.95

This example works, of course, but the database has to search both the products and product_types tables for the description and price columns; that s because there s no alias that tells the database which table those columns are in. The time spent by the database having to do the search is time wasted .

The next example includes the table alias p to fully qualify the description and price columns:

 -- GOOD (fully qualified columns)  SELECT p.name, pt.name, p.description, p.price   FROM products p, product_types pt   WHERE p.product_type_id = pt.product_type_id   AND p.product_id = 1;  NAME NAME ------------------------------ ---------- DESCRIPTION PRICE -------------------------------------------------- ---------- Modern Science Book A description of modern science 19.95

Because all references to columns include a table alias, the database doesn t have to waste time searching the tables for the columns and execution time is reduced.