Aliases in SQL
In SQL, Aliases are a powerful feature that allow you to assign temporary names to tables or columns in your SQL queries. Aliases can improve the readability of your queries, make them shorter and simplify complex SQL statements. In this article, we will explore what SQL aliases are, why they are useful, and how to use them with detailed examples. After reading this article, you will be able to use aliases easily.
Syntax
For ColumnSELECT column_name AS alias_name
FROM table_name;
OR
SELECT column_name alias_name
FROM table_name;SELECT column_name(s)
FROM table_name AS alias_name;
OR
SELECT column_name(s)
FROM table_name alias_name;Demo Database
CREATE TABLE `Users` ( `Id` int NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Mobile` varchar(10) DEFAULT NULL, `DOB` date DEFAULT NULL, `Weight` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`Id`) );
Id Name Mobile DOB Weight ----------------------------------------------------------------- 1 Deepak 9999999991 1993-01-15 60.00 2 Smith 9999999999 1994-11-20 85.00 3 Naveen 9999999992 1997-01-15 55.00 4 Deepak Sharma 9999999993 1998-08-03 null 5 Jane Smith 9999999995 1993-10-05 73.00 6 Jone Head 9999999991 1983-01-15 90.00 7 Deepak Kumar 9999999999 1984-11-20 85.00 8 Jack 9999999992 1987-01-15 59.00 9 Ramesh 9999999993 1988-08-03 86.00 10 David 9999999995 1983-10-05 70.00
CREATE TABLE `UserAddress` (
`Id` int NOT NULL AUTO_INCREMENT,
`UserId` int NOT NULL,
`AddressType` enum('Permanent','Temporary') DEFAULT NULL,
`Address` varchar(300) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL,
`State` varchar(50) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`PostalCode` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Id`)
);Id UserId AddressType Address Country State City PostalCode ------------------------------------------------------------------------------------------------- 1 1 Permanent 23 Oak Street India Maharashtra Mumbai 400001 2 1 Temporary 456 Oak Ave USA New York New York City 10001 3 2 Permanent 789 Elm Rd Canada Ontario Toronto 550002 4 2 Temporary 56 Maple Road India Delhi New Delhi 110001 5 3 Permanent 23 Oak Street India Maharashtra Mumbai 400001 6 3 Temporary 456 Oak Ave USA California Los Angeles 10001 7 4 Permanent 888 Birch Dr USA California LG 2000 8 4 Temporary 999 Willow Ave India Maharashtra pune 3000 9 5 Permanent 101 Pineapple Germany Bavaria Munich 80331 10 5 Temporary 202 Mango Ave Germany Berlin Berlin 10115
Using Alias for Columns
Column aliases are helpful when we need to rename a column name in your result set or when performing calculations or using functions and we want the resulting column to have a meaningful name.
SQL query to change Mobile field into PhoneNumber in result set.
SELECT Id, Name, Mobile PhoneNumber, DOB, Weight FROM Users WHERE Id=5;
Id Name PhoneNumber DOB Weight ---------------------------------------------------------------- 5 Jane Smith 9999999995 1993-10-05 73.00
SQL query to change column name after performing calculations (dividing weight by 100).
SELECT Id, Name, Mobile, DOB, Weight/100 NormWeight FROM Users WHERE Id=5;
Id Name PhoneNumber DOB NormWeight ---------------------------------------------------------------- 5 Jane Smith 9999999995 1993-10-05 0.730000
Using Alias for Tables
Table alias in SQL are used to assign a temporary name to a table in a query. Table alias are especially useful when joining multiple tables, long table names or complex joins. It make SQL statements shorter and more readable.
SQL query to change column name , applying condition (on join), handling ambiguity (of column Id in both table) when joining multiple tables.
SELECT u.Id AS UserId, ua.id UserAddressId, Name, AddressType, Country FROM Users AS u Join UserAddress as ua on u.id= ua.UserId;
UserId UserAddressId Name AddressType Country ------------------------------------------------------------------ 1 1 Deepak Permanent India 1 2 Deepak Temporary USA 2 3 Smith Permanent Canada 2 4 Smith Temporary India 3 5 Naveen Permanent India 3 6 Naveen Temporary USA 4 7 Deepak Sharma Permanent USA 4 8 Deepak Sharma Temporary India 5 9 Jane Smith Permanent Germany 5 10 Jane Smith Temporary Germany
Using Alias In Nested Query
Using aliases in nested queries to retrieve the desired data.
SELECT Id AS UserId, UserAddressId, Name, ua.AddressType, Country
FROM Users AS u
Join (SELECT Id as UserAddressId, UserId, AddressType, Country
FROM UserAddress
WHERE AddressType = "Permanent" and Country = "India") as ua on ua.UserId = u.id;UserId UserAddressId Name AddressType Country -------------------------------------------------------- 1 1 Deepak Permanent India 3 5 Naveen Permanent India
Key Characteristics of Alias
- Aliases are temporary names to tables or columns in your SQL queries.
- Aliases are temporary and only exist for the duration of the query.
- `AS` keyword is optional but recommended for clarity.
- If you want an alias contains spaces then it must be enclosed in double quotes like "User Address Id".
- It improve the readability, conciseness and clarity of your SQL queries.
- Aliases are crucial for self-joins where a table is joined with itself.
- Avoid using SQL reserved words as aliases to prevent confusion and potential errors.