SQL workshop: ALIAS


3 min read

In SQL, an alias is a temporary name assigned to a table or a column for the purpose of a particular SQL query. Aliasing can make queries more readable or allow you to perform operations like self-joins where you join a table with itself.

Why use Aliasing?

  1. Readability: It can help in making your SQL statements more readable.

  2. Space Saving: For large table or column names, it's easier to refer to them with shorter aliases.

  3. Necessity in Self Joins: In cases where you want to join a table with itself, aliasing is essential to differentiate between the two uses of the table.


An alias only exists for the duration of that query. An alias is created with the AS keyword.

For tables:

SELECT column1, column2, ...
FROM table_name AS alias_name
WHERE condition;

For columns:

SELECT column_name AS alias_name
FROM table_name
WHERE condition;


  1. Table Alias:
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Orders AS o, Customers AS c
WHERE o.CustomerID = c.CustomerID;
  1. Column Alias:
SELECT CustomerName AS Name, ContactName AS Contact
FROM Customers;

Workshop Exercise:

Objective: To understand the concept of aliasing in SQL.

Prerequisites: Basic understanding of SQL, access to a MySQL environment, a sample database, and tables.


  1. Database & Table Creation:

    Don't forget to add access permissions to the database for the user to access the database via DBeaver. More info at Troubleshoot Access Denied Error: Connect DBeaver to a local MySQL database
     CREATE DATABASE aliasing_workshop;
     USE aliasing_workshop;
     GRANT ALL PRIVILEGES ON aliasing_workshop.* TO 'user'@'localhost';
     CREATE TABLE Students (
         FirstName VARCHAR(50),
         LastName VARCHAR(50),
         DateOfBirth DATE
     INSERT INTO Students (ID, FirstName, LastName, DateOfBirth)
     VALUES (1, 'John', 'Doe', '2000-01-01'),
            (2, 'Jane', 'Smith', '2001-02-03'),
            (3, 'Bob', 'Johnson', '1999-04-05');


  1. Column Aliasing:

    • Task: Fetch the FirstName and LastName of all students, but rename the columns as First and Last respectively in the output.

    • Solution:

        SELECT FirstName AS First, LastName AS Last
        FROM Students;

  2. Table Aliasing with Join:

    • Task: Create a self-join to get pairs of students who have the same DateOfBirth.

      Before that insert additional two student's entries to the table

    • Solution:

        SELECT A.FirstName AS Student1, B.FirstName AS Student2, A.DateOfBirth
        FROM Students A, Students B
        WHERE A.ID <> B.ID AND A.DateOfBirth = B.DateOfBirth;
      Students A: This refers to the Students table, and it is temporarily given the alias A for the duration of the query. Students B: Similarly, the Students table is again referred to and given another alias, B.

  3. Combining Both:

    • Task: Using both table and column aliasing, fetch pairs of students with the same birth year and present their names as Name1 and Name2.

    • Solution:

        SELECT A.FirstName AS Name1, B.FirstName AS Name2
        FROM Students A, Students B
        WHERE YEAR(A.DateOfBirth) = YEAR(B.DateOfBirth) AND A.ID <> B.ID;


  1. SQL Aliases