SQL Alias with Examples

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:

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.

---