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

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:

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

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:

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

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

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: