Column Aliases

A column alias gives SQL users a way to name the columns in their result sets by providing the alias for a column in the SELECT list.

There are three supported ways to define column aliases. The first way, which immediately follows, is the method specified by the ANSI-SQL standard. I adhere to ANSI standards whenever I can, except when using them incurs a performance penalty. In the case of aliases, there is no performance benefit to using any one method. So, to use the ANSI aliasing method, provide the name of the column and then provide the alias name in the select list. Use quotes if the alias includes spaces or punctuation:

     select au_lname “Last Name”, city Hometown
     from authors

     Last Name                                Hometown
     ----------------------                   ------------
     White                                    Menlo Park
     Green                                    Oakland
     Carson                                   Berkeley
     O’Leary                                  San Jose
     Straight                                 Oakland
     Smith                                    Lawrence
     Bennet                                   Berkeley
     [...]

     (23 row(s) affected)

The second way to create an alias is to list the alias first, followed by an equal sign. The output for this example is the same as the output for the last example:

     select “Last Name” = au_lname, Hometown = city
     from authors

The third method, only supported by MS SQL Server 6.5, is to use the AS keyword in the SELECT list. It’s just like the ANSI-supported way, with more typing thrown in:

     select au_lname as “Last Name”, city as Hometown
     from authors

Although it makes little difference which method you use for column aliasing, it’s often better to learn the ANSI-supported way. Then if you ever need to access a different database vendor’s product (moving from MS SQL Server to Sybase or Oracle, for example), you have a better chance of knowing a system that works in all environments.

---

Tip:  Troubleshooting Your Query
If you happen to leave out a comma in your SELECT list, instead of generating a syntax error, the server may interpret one of the column names as an alias. Consider this accidental query:

select city state
from authors

state
----------------------
Menlo Park
Oakland
Berkeley
San  Jose
[...]
(23 row(s) affected)

Because the comma was left out between city and state, the server assumed that I wanted one column named “state” that contained the cities in which the authors live. The way to sniff out this particular mistake is to ask, “How many columns did I expect? How many did I get back?” If those two questions don’t yield the same number, look in the SELECT list for a missing comma.

---

Getting All the Columns in a Table

Listing specific columns is all well and good, but if you want to see everything, there has to be a better way to ask for all the information. Fortunately, there is a better way:

     select *
     from jobs

     job_id                                    min_lvl  max_lvl
     ------    ----------------------------    ------   -------
     1         New Hire - Job not specified    10       10
     2         Chief Executive Officer         200      250
     3         Business Operations Manager     175      225
     4         Chief Financial Officer         175      250
     5         Publisher                       150      250
     […]
     (14 row(s) affected)

This statement selects all the columns and all the rows from the jobs table. What actually happens at the server is that the asterisk gets expanded to include all the columns in a table, in the order in which the columns were defined when the table was created. In this example, the server gets this query:

select * from jobs

Then it changes it into this query, which it executes:

select job_id, job_desc, min_lvl, max_lvl from  jobs

This is a fast way to get all the data in a table.

---

Note:  If you are creating a stored procedure, trigger, view, or SQL statement as part of a client program—that is, if you are creating reusable SQL code that is expected to perform the same way in all circumstances—you should not use SELECT * to access all the columns in a table.

I’m always a big fan of anything that lets me do less typing. However, if the table is ever dropped and re-created with its columns in a different order, or, worse, if a new column is added to a table that your code accesses with a SELECT *, you suddenly will be getting back data that you did not expect and cannot handle. Discipline yourself to use SELECT * only when performing interactive queries.

---