SQL Alias with Examples

aimtocode

SQL Alias Definition:

Alias is the process of renaming a table or a column temporarily by giving another name known as Alias

The use of table aliases is to rename a table in a specific SQL statement. The renaming is a temporary change and the actual table name does not change in the database,

Basically aliases are created to make column names more readable.

“AS” is a key word used to give Alias name.

Alias in SQL can be useful when:

  • There are more than one table involved in a query.
  • Functions are used in the query.
  • Column names are big or not very readable.
  • Two or more columns are combined together.

SQL Alias Syntax for Columns

  SELECT column_name AS alias_name
  FROM table_name;

SQL Alias Syntax for Tables

SELECT column_name(s)
FROM table_name AS alias_name;

Example to Alais Column Name:

you can alias a column by 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 use the SQL Column Alias :

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

There will be 2 records selected. and it will produce the result as shown

dept_id total
500 2
501 2

In above example, we are aliasing 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

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. and it will produce the result as shown

dept_id total employees
500 2
501 2

In Above example, we are alising 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 to Alias a Table Name

Aliasing table is used only if you plan to list the same table name more than once in the FORM clauses, Or You wish to shorten the table name to make the SQL statement easier to read.

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 We Are Also Having 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 alias each of the table names by joining both table. Use 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';

Therefore, There will be 5 records selected. and it will produce the result as shown:

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

In Above example, we are creating 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.


aimtocode