T-SQL: Using the SELECT Statement for Naming Columns
Excerpt by Don Kiely | April 18, 2013
One of the most versatile statements in all of T-SQL is the SELECT statement. It's guaranteed to become your go-to tool for retrieving data stored in a SQL Server database, with a dizzying array of clauses and options that let you select data from any databases and tables, shape it however you want, and apply conditions and filters so that you get exactly the data you need. This article discusses how to use the SELECT statement for naming columns.
Notice in the Figure below that the column has no name. Since the column is the result of an expression, rather than a column in the database, you must explicitly name the column if you want to be able to reference the calculated column in a client application. Here is how you can do that by using the AS keyword:
SELECT LastName + ', ' + FirstName AS FullName
The query itself is essentially the same as the previous example; all that's changed is that the column now has a name, FullName. This name is also called an alias. Aliases are used routinely in complex SQL and are necessary for certain types of joins, such as the self joins that you'll learn about later in the chapter. To include a space within an alias, surround the alias with square brackets; the results appear in Figure 4.
SELECT LastName + ', ' + FirstName AS [Full Name]
The AS clause is optional-you can name a column just by using the name separated from the column list by a space:
SELECT LastName + ', ' + FirstName FullName
This query functions in the same way as the query with the AS clause, but makes your SQL statement harder to read. Is FullName a column in the database and the programmer forgot the comma, or an alias? It is better to be explicit with the AS clause. The AS clause is the most explicit way to alias a column and is supported by the ANSI standard.
Another supported option is to use an equal sign for defining a column alias, which produces the same results as the previous statements. This syntax is required in some cases when using more advanced features of the SELECT statement.
SELECT FullName = LastName + ', ' + FirstName
Deprecated Syntax - One method of defining column aliases using the equal sign (=) is now deprecated, which means it will not be supported in future versions of SQL Server. (Even though it has been deprecated for a long time, it still works in SQL Server 2012.) This method uses a string expression to define the alias:
-- Deprecated Syntax
SELECT 'FullName' = LastName + ', ' + FirstName
You can, however, still use a string expression to define the column alias if you use the AS syntax (even if you leave out the word AS).