SLQ ALIASES

 

SQL: ALIASES

This SQL tutorial explains how to use SQL ALIASES (temporary names for columns or tables) with syntax and examples.

Description

SQL ALIASES can be used to create a temporary name for columns or tables.

• COLUMN ALIASES are used to make column headings in your result set easier to read.
• TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause).

Syntax

The syntax to ALIAS A COLUMN in SQL is:

column_name [AS] alias_name

OR

The syntax to ALIAS A TABLE in SQL is:

table_name [AS] alias_name

Parameters or Arguments

column_name

The original name of the column that you wish to alias.

table_name

The original name of the table that you wish to alias.

alias_name

The temporary name to assign.

Note

• If the alias_name contains spaces, you must enclose the alias_name in quotes.
• It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
• The alias_name is only valid within the scope of the SQL statement.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - How to Alias a Column Name

Generally, aliases are used to make the column headings in your result set easier to read. Most commonly, you will alias a column when using an aggregate function such as MIN, MAX, AVG, SUM or COUNT in your query.

Let's look at an example of how to use to alias a column name in SQL.

In this example, we have a table called employees with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Let's demonstrate how to alias a column. Enter the following SQL statement:

SELECT dept_id, COUNT(*) AS total
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id	total
500	2
501	2

In this example, we've aliased the COUNT(*) field as total. As a result, total will display as the heading for the second column when the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes.

Now, let's rewrite our query to include a space in the column alias:

SELECT dept_id, COUNT(*) AS "total employees"
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id	total employees
500	2
501	2

In this example, we've aliased the COUNT(*) field as "total employees" so this will become the heading for the second column in our result set. Since there are spaces in this column alias, "total employees" must be enclosed in quotes in the SQL statement.

Example - How to Alias a Table Name

When you alias a table, it is either because you plan to list the same table name more than once in the FROM clause (ie: self join), or you want to shorten the table name to make the SQL statement shorter and easier to read.

Let's look at an example of how to alias a table name in SQL.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

And a table called categories with the following data:

category_id	category_name
25	Deli
50	Produce
75	Bakery
100	General Merchandise
125	Technology

Now let's join these 2 tables and alias each of the table names. Enter the following SQL statement:

SELECT p.product_name, c.category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.category_id
WHERE p.product_name <> 'Pear';

There will be 5 records selected. These are the results that you should see:

product_name	category_name
Banana	Produce
Orange	Produce
Apple	Produce
Bread	Bakery
Sliced Ham	Deli

In this example, we've created an alias for the products table and an alias for the categories table. Now within this SQL statement, we can refer to the products table as p and the categories table as c.

When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.