SQL Aliases

In SQL, aliases are temporary names assigned to columns or tables for the duration of a query. They make the query more readable, especially when dealing with complex queries or large datasets. Aliases help simplify long column names, improve query clarity, and are particularly useful in queries involving multiple tables or aggregated data.

In this guide, we’ll learn SQL column aliases and SQL table aliases with a consistent example table and provide practical use cases to help you understand how and when to use them.

What Are SQL Aliases ?

Aliases are the temporary names given to tables or columns for the purpose of a particular SQL query. It is used when the name of a column or table is used other than its original name, but the modified name is only temporary.

Aliases are created to make table or column names more readable.
The renaming is just a temporary change and the table name does not change in the original database.
Aliases are useful when table or column names are big or not very readable.
These are preferred when there is more than one table involved in a query.

There are two types of aliases in SQL:

Column Aliases: Temporary names for columns in the result set.
Table Aliases: Temporary names for tables used within a query.

We’ll use the following Customer table throughout the article to demonstrate all SQL alias concepts. This table contains customer information such as ID, name, country, age, and phone number.

Query

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age INT,
    Phone VARCHAR(15)
);

-- Inserting sample data into the Customer table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES 
(1, 'Shubham', 'Thakur', 'India', 23, '9876543210'),
(2, 'Aman', 'Chopra', 'Australia', 21, '9876543211'),
(3, 'Naveen', 'Tulasi', 'Sri Lanka', 24, '9876543212'),
(4, 'Aditya', 'Arpan', 'Austria', 21, '9876543213'),
(5, 'Nishant', 'Jain', 'Spain', 22, '9876543214');

Output

CustomerID	CustomerName	LastName	Country	Age	Phone
1	Shubham	Thakur	India	23	9876543210
2	Aman	Chopra	Australia	21	9876543211
3	Naveen	Tulasi	Sri Lanka	24	9876543212
4	Aditya	Arpan	Austria	21	9876543213
5	Nishant	Jain	Spain	22	9876543214

SQL Column Aliases

A column alias is used to rename a column in the output of a query. Column aliases are useful for making the result set more readable or when performing calculations or aggregations.

Syntax

SELECT column_name AS alias_name
FROM table_name;

The following table explains the arguments in detail:

column_name: The column name can be defined as the column on which we are going to create an alias name.
alias_name: It can be defined as a 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.

Example 1: Column Alias for Renaming a Column

To fetch the CustomerID and rename it as id in the result set

Query

SELECT CustomerID AS id
FROM Customer;

Output

id
1
2
3
4
5

SQL 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).

Example 2: Table Alias for Joining Tables

We want to join the Customer table with itself to find customers who have the same country and are aged 21. We’ll use table aliases for each instance of the Customer table.

Query

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

Output

CustomerName	Country
Shubham	India
Aman	Australia
Naveen	Sri Lanka
Aditya	Austria
Nishant	Spain

Here, c1 and c2 are aliases for two instances of the Customer table.

Combining Column and Table Aliases

We want to fetch customers who are aged 21 or older and rename the columns for better clarity. We’ll 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

Name	Location
Shubham	India
Aman	Australia
Naveen	Sri Lanka
Aditya	Austria
Nishant	Spain

Advantages of SQL Aliases

Readability: Aliases make long or complex table/column names more readable and concise.
Simplification: Aliases reduce the verbosity of SQL queries, especially in joins or complex calculations.
Clearer Results: Aliases help clarify what data is being returned, especially when performing aggregations or combining multiple tables.
Avoid Name Conflicts: Aliases prevent naming conflicts, especially when columns in multiple tables share the same name.

Conclusion

SQL aliases are an essential tool for simplifying complex queries, especially when dealing with multiple tables, aggregate functions, and subqueries. Whether you’re renaming columns to make the output more understandable or using table aliases to handle multiple instances of the same table, aliases play a critical role in improving the clarity and maintainability of your SQL queries.