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

SELECT * FROM Employees;

The above query returned the result given below:

Table: Employees

EmployeeIDFirstNameLastNameAge
1AshishGoel25
2KirshanGupta30
3AnjaliSharma25
4ManjuSaini15
5KatrinaKaif18
6EshaDixit20
7AnkitaVerma19
8MeenakshiChikkara35
9AliaBhatt16

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:

The above query returned the result given below:

IDFirstNameAge
9Alia16
8Meenakshi35
7Ankita19
6Esha20
5Katrina18
4Manju15
3Anjali25
2Kirshan30
1Ashish25

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

SELECT * FROM Users;

Table: Users

EmployeeIDNameStateAge
1AnjaliDL25
2ManjuHR15
3KatrinaUP18
4EshaUP20
5AnkitaDL19
6MeenakshiHR35
7AliaDL16

SQL

SELECT EmployeeID, name, 'Haryana' AS MyState, state, Age
FROM Users
WHERE state = 'HR';  

The following are some findings:

The above query returned the following result:

EmployeeIDNameMyStateStateAge
2ManjuHaryanaHR15
6MeenakshiHaryanaHR35

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

SELECT * FROM Employees;

The above query returned the result given below:

Table: Employees

EmployeeIDFirstNameLastNameAge
1AshishGoel25
2KirshanGupta30
3AnjaliSharma25
4ManjuSaini15
5KatrinaKaif18
6EshaDixit20
7AnkitaVerma19
8MeenakshiChikkara35
9AliaBhatt16

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

SELECT * FROM Accounts;

The above query returns the following result:

Table: Accounts

EmployeeIDSalary
125000
230000
325000
415000
518000
620000
79000
815000
916000

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 employees table is renamed to emp in the query.
  • The Accounts table is renamed to acc in the query.
  • The above query returns the following result:

    EmployeeIDFirstNameSalary
    1Ashish25000
    2Kirshan30000
    3Anjali25000
    4Manju15000
    5Katrina18000
    6Esha20000
    7Ankita9000
    8Meenakshi15000
    9Alia16000