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.
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
SELECT CustomerID AS id FROM Customer;
Output:
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.