Aliases in SQL
In SQL, aliases are used to give a table or a column a temporary name. This can make our SQL queries clearer and more concise and can also be useful when we need to perform operations on the same table or column more than once within a query. Aliases are created using the AS keyword, but it is optional and often omitted.
a) Column Alias A column alias allows us to provide a temporary name for a column in the result set.
SQL
SELECT column_name AS alias_name FROM table_name;
Example: Create an Employees table.
SQL
CREATE TABLE Employees ( EmployeeID INT, FirstName VARCHAR(20), LastName VARCHAR(20), Age int );
Insert the data into the Employees table.
SQL
insert into Employees values (1, "Ashish", "Goel", 25), (2, "Kirshan", "Gupta", 30), (3, "Anjali", "Sharma", 25), (4, "Manju", "Saini", 15), (5, "Katrina", "Kaif", 18), (6, "Esha", "Dixit", 20), (7, "Ankita", "Verma", 19), (8, "Meenakshi", "Chikkara", 35), (9, "Alia", "Bhatt", 16);
To see all the data in the Employees table, we have to execute the following query.
SQL
The above query returned the result given below:
Table: Employees
EmployeeID | FirstName | LastName | Age |
---|---|---|---|
1 | Ashish | Goel | 25 |
2 | Kirshan | Gupta | 30 |
3 | Anjali | Sharma | 25 |
4 | Manju | Saini | 15 |
5 | Katrina | Kaif | 18 |
6 | Esha | Dixit | 20 |
7 | Ankita | Verma | 19 |
8 | Meenakshi | Chikkara | 35 |
9 | Alia | Bhatt | 16 |
Let’s create a SQL query, here we need to select the EmployeeID, FirstName and Age columns from the table. The EmployeeID column is ordered in descending order. Here we are using the column alias as the EmployeeID is renamed to ID column.
SQL
SELECT EmployeeID as ID, FirstName, Age FROM Employees ORDER BY ID DESC;
In this query:
- EmployeeID is renamed to ID in the result set.
- FirstName and Age column are not given any temporary name.
The above query returned the result given below:
ID | FirstName | Age |
---|---|---|
9 | Alia | 16 |
8 | Meenakshi | 35 |
7 | Ankita | 19 |
6 | Esha | 20 |
5 | Katrina | 18 |
4 | Manju | 15 |
3 | Anjali | 25 |
2 | Kirshan | 30 |
1 | Ashish | 25 |
Example: Create a “Users” table.
SQL
CREATE TABLE Users ( EmployeeID INT, Name VARCHAR(20), state VARCHAR(20), Age int );
Load the data in the users table.
SQL
insert into Users values (1, "Anjali", "DL", 25), (2, "Manju", "HR", 15), (3, "Katrina", "UP", 18), (4, "Esha", "UP", 20), (5, "Ankita", "DL", 19), (6, "Meenakshi", "HR", 35), (7, "Alia", "DL", 16);
By executing the following query, we can see the data in the table.
SQL
Table: Users
EmployeeID | Name | State | Age |
---|---|---|---|
1 | Anjali | DL | 25 |
2 | Manju | HR | 15 |
3 | Katrina | UP | 18 |
4 | Esha | UP | 20 |
5 | Ankita | DL | 19 |
6 | Meenakshi | HR | 35 |
7 | Alia | DL | 16 |
SQL
SELECT EmployeeID, name, 'Haryana' AS MyState, state, Age FROM Users WHERE state = 'HR';
The following are some findings:
- Name, state, age: These are the columns in the Users table.
- 'Haryana' AS MyState: This part creates a derived column with a constant value of 'Haryana'. The AS MyState syntax is used to give this derived column an alias (or temporary name) called MyState. This means every row returned will display 'Haryana' in the MyState column, regardless of the actual state of the user.
- In this query, where condition is state = ‘HR’, which means only rows where the state column has the value 'HR' will be included in the results.
The above query returned the following result:
EmployeeID | Name | MyState | State | Age |
---|---|---|---|---|
2 | Manju | Haryana | HR | 15 |
6 | Meenakshi | Haryana | HR | 35 |
The query effectively selects only rows in which the state value is 'HR' while labeling the MyState as 'Haryana' for all selected rows.
b) Table Alias A table alias allows us to give a table a temporary name, which is useful for shortening table names.
SQL Syntax
SELECT column_name FROM table_name AS alias_name;
Example: Create an Employees table.
SQL
CREATE TABLE Employees ( EmployeeID INT, FirstName VARCHAR(20), LastName VARCHAR(20), Age int );
Insert the data into the Employees table.
SQL
insert into Employees values (1, "Ashish", "Goel", 25), (2, "Kirshan", "Gupta", 30), (3, "Anjali", "Sharma", 25), (4, "Manju", "Saini", 15), (5, "Katrina", "Kaif", 18), (6, "Esha", "Dixit", 20), (7, "Ankita", "Verma", 19), (8, "Meenakshi", "Chikkara", 35), (9, "Alia", "Bhatt", 16);
To see all the data in the Employees table, we have to execute the following query.
SQL
The above query returned the result given below:
Table: Employees
EmployeeID | FirstName | LastName | Age |
---|---|---|---|
1 | Ashish | Goel | 25 |
2 | Kirshan | Gupta | 30 |
3 | Anjali | Sharma | 25 |
4 | Manju | Saini | 15 |
5 | Katrina | Kaif | 18 |
6 | Esha | Dixit | 20 |
7 | Ankita | Verma | 19 |
8 | Meenakshi | Chikkara | 35 |
9 | Alia | Bhatt | 16 |
Create an Accounts table.
SQL
CREATE TABLE Accounts ( EmployeeID INT, Salary VARCHAR(20) );
Insert the data into the Accounts table.
SQL
insert into Accounts values (1, "25000"), (2, "30000"), (3, "25000"), (4, "15000"), (5, "18000"), (6, "20000"), (7, "9000"), (8, "15000"), (9, "16000");
To see all the data in the Accounts table, we have to execute the following query.
SQL
The above query returns the following result:
Table: Accounts
EmployeeID | Salary |
---|---|
1 | 25000 |
2 | 30000 |
3 | 25000 |
4 | 15000 |
5 | 18000 |
6 | 20000 |
7 | 9000 |
8 | 15000 |
9 | 16000 |
Let’s create a SQL query, here we are using the table alias. As employees table is renamed to emp and Accounts table is renamed to acc.
SQL
SELECT emp.EmployeeID, emp.FirstName, acc.Salary FROM employees emp INNER JOIN Accounts acc ON emp.EmployeeID = acc.EmployeeID;
In this query:
The above query returns the following result:
EmployeeID | FirstName | Salary |
---|---|---|
1 | Ashish | 25000 |
2 | Kirshan | 30000 |
3 | Anjali | 25000 |
4 | Manju | 15000 |
5 | Katrina | 18000 |
6 | Esha | 20000 |
7 | Ankita | 9000 |
8 | Meenakshi | 15000 |
9 | Alia | 16000 |