Aliases in SQL

In SQL, aliases provide temporary names for columns or tables to make queries cleaner and easier to understand. They are especially helpful in complex queries or when dealing with lengthy names.

Example: First, we create a demo SQL database and table, on which we will use the Aliases command.

Query:

SELECT EmpID AS id
FROM Employees;

Output:

• The query selects the EmpID column from the Employees table.
• It assigns a temporary alias id to the EmpID column in the result set.
• The output will display the employee IDs under the column name id.

Types of aliases

There are two types of aliases:

1. Column Aliases

A column alias is used to rename a column just for the output of a query. They are useful when:

• Displaying aggregate data
• Making results more readable
• Performing calculations

Syntax:

SELECT column_name AS alias_name
FROM table_name;

• column_name: column on which we are going to create an alias name.
• alias_name: temporary name that we are going to assign for the column or table. 
• AS: It is optional. If you have not specified it, there is no effect on the query execution. 

Let's understand Aliases in SQL with the help of example. First, we will create a demo SQL database and table, on which we will use the Aliases command.

Query:

SELECT CustomerID AS id
FROM Customer;

Output:

• The query retrieves the CustomerID column from the Customer table.
• It assigns a temporary alias id to the CustomerID column.
• The output displays customer IDs under the column name id.

2. Table Aliases

A table alias is used when you want to give a table a temporary name for the duration of a query. Table aliases are especially helpful in JOIN operations to simplify queries, particularly when the same table is referenced multiple times (like in self-joins).

Query:

SELECT c1.CustomerName, c1.Country
FROM Customer AS c1, Customer AS c2
WHERE c1.Age = c2.Age AND c1.Country = c2.Country;

Output:

• The query uses table aliases (c1 and c2) for the same Customer table.
• It compares records where both the Age and Country values are the same.
• The condition c1.Age = c2.Age AND c1.Country = c2.Country finds customers who share the same age and country.
• The result displays each matching customer’s name and country.

Combining Column and Table Aliases

We want to fetch customers who are aged 21 or older and rename the columns for better clarity. We will use both table and column aliases.

Query:

SELECT c.CustomerName AS Name, c.Country AS Location
FROM Customer AS c
WHERE c.Age >= 21;

Output: