Using Aliases

You've now seen how to select specific columns from a table and how to control the order in which those columns are retrieved. In addition, you can supply aliases for columns or tables. In other words, you can refer to columns in your returned data set by your own names, not the ones that exist in the database.

This can be useful in making your SELECT statements and the data they return easier to read and understand and in getting data into a form that's more appropriate for your application. In particular, where multiple tables have columns with identical names, you might provide aliases to make it easier for you to differentiate between them.

To do this, you use the AS keyword to provide an alias for a name:

SELECT ColumnA AS A, ColumnB AS B, ColumnC AS C
FROM Table AS T;

Here you're providing aliases for every name used: A is used for ColumnA, B for ColumnB, and so on. These aliases will appear in the results returned by the query. This is another useful technique for combating situations where you have multiple columns with the same name because you can provide an easily identifiable alternative.

When you provide an alias for a table name, you can use that alias within the same SQL statement, for example:

SELECT T.ColumnA AS A, T.ColumnB AS B, T.ColumnC AS C
FROM Table AS T;

Where there are more complicated queries from multiple tables with lengthy names, this can make SQL queries a lot more readable and take up much less space!

The ANSI specification for SQL-99 allows you to omit this AS keyword:

SELECT T.ColumnA A, T.ColumnB B, T.ColumnC C
FROM Table T;

This format is supported by most database systems (although not by Access) and in fact is the only format supported by older versions of Oracle. However, the current versions of all the RDBMSs used to test the code in the book support AS, so you'll use this syntax throughout. It also makes for clearer, more readable SQL.

As before, you can see how this works in practice by executing the following SQL query against the InstantUniversity database:

SELECT Name AS StudentName, StudentID AS ID FROM Student;

This query should produce the following results (only the first four records are shown):

   StudentName      ID

   ---------------- ----
   John Jones       1
   Gary Burton      2
   Emily Scarlett   3
   Bruce Lee        4
   ...              ...

In the database, these two columns are called Name and StudentID. In the result set, you have given them the aliases StudentName and ID, respectively.