Custom Search
 


Using Column Alias in SELECT Statement


By default, after executing a SELECT statement, the column heading(s) displayed in the result set is the name of the column in the table. You can override it with a column alias. That is, you can change a column heading by using a column alias.

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using column alias:

  1. Column alias is added in the SELECT statement immediately after the column name.

  2. Optionally, you can add the keyword AS in between the column name and the column alias to clearly indicate the use of alias.

  3. Use column alias if the original column name does not meet your requirements. For example, if the column name does not make too much business sense, you can use a meaningful alias instead.

  4. Use column alias with single quotes (' ') or double quotes (" ") if you need to put spaces or add special characters (such as $, #, @) in the column heading.

  5. Use column alias when there is a column name conflict. This can happen when joining two or more tables and these tables may use the same column names. JOINs are covered in Querying Multiple Tables section.

Practice #1: execute SELECT statement with or without using keyword AS before column alias name

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Use the keyword AS.
SELECT CategoryID AS ID, CategoryName AS Name
FROM categories;

-- Keyword AS is not used - Not recommended.
SELECT CategoryID ID, CategoryName Name
FROM categories;

The queries above display CategoryID and CategoryName from categories table. In the first query, we have used optional keyword AS before the column alias name. This is the recommended way to do it. The second query doesn't use optional keyword AS - not recommend. Both queries have produced the same result whether the AS keyword is used or not.

Query result set - 8 rows returned:
Column alias for categories table

Practice #2: execute SELECT statement with single or double quotes around column alias name

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Use double quotes around column alias
SELECT CategoryID AS "Category ID", CategoryName AS "Category Name"
FROM categories;

-- Use single quotes around column alias - Not recommended.
SELECT CategoryID AS 'Category ID', CategoryName AS 'Category Name'
FROM categories;

The first query uses double quotation marks to enclose column alias name. This is the recommended way to do it. The second query uses single quotation marks to enclose column alias name - not recommend as single quotes are normally used to enclose character strings. Literal strings are discussed in Using Literal Character Strings section. Note that both queries have produced the same result whether single or double quotes are used.

Query result set - 8 rows returned:
Column alias for categories table with quotation marks





Other tutorials in this category

1. The Basic SELECT Statement

2. Using Literal Character Strings

3. Perform Arithmetic Operations

4. Use WHERE Clause to Conditionally Select Rows

5. Sorting Data

6. Using LIMIT Clause to Constrain the Number of Rows Retrieved

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page


Copyright © 2014 GeeksEngine.com. All Rights Reserved.

This website is hosted by LunarPages.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy