Using Aliases in the FROM Clause Table/View List

You can assign each table (or view—see Chapter 9) an alias (an abbreviation for the full table name) in the FROM clause table list, in order to make join queries

• Easier to type

• More readable

Assigning an alias to each table name is particularly helpful when you join on identically named columns, which have to be qualified with the table name each time they're used. Aliases can be letters or numbers in any combination; most people make them short and easy to remember.

Here's how you could use aliases in a query to find authors who live in the same city as some publisher:

select au_lname, au_fname          select au_lname
, au_fname
from authors a, publishers p       from authors a
										 join publishers p
where a.city = p.city              on a.city = p.city
au_lname                                 au_fname
========================================
 ====================
Bennet                                   Abraham
Carson                                   Cheryl
[2 rows]

If you want to display the city name, you need to qualify it with one of the table name aliases (a or p) in the SELECT list.

Some people make it a rule to qualify all columns. That way, changes in table structures do not introduce ambiguities.

select a.au_fname as Author, a.zip as AuthorZip, p.pub_name as Publisher
from authors a, publishers p
where a.city = p.city
Author               AuthorZip Publisher
==================== ========= =====================
Abraham              94705     Algodata Infosystems
Cheryl               94705     Algodata Infosystems
[2 rows]

If someone adds a column coincidentally named zip to publishers (which did not originally have a zip column), the query will still run. The qualifier makes it clear that it's authors.zip you want to see.

alter table publishers
						add zip char(5) null

select a.au_fname as Author, a.zip as AuthorZip, p.pub_name as Publisher
from authors a, publishers p
where a.city = p.city
Author               AuthorZip Publisher
==================== ========= =====================
Abraham              94705     Algodata Infosystems
Cheryl               94705     Algodata Infosystems
[2 rows]

Without the qualification on the (originally) unambiguous column, you'd get an error.

select au_fname as Author, zip as AuthorZip, pub_name as Publisher
from authors a, publishers p
where a.city = p.city
						Error: Column 'zip' found in more than one
table -- need a correlation name.