1 Introduction

Today I would like to discuss a topic that is used in almost every SQL statement: Aliases

2 What is an Alias ?

Aliases are “replacement names” for the columns and tables that can be used in an SQL statement.

The keyword to create an alias is AS. This (AS) keyword can be used to specify a new name for a column or table in your SQL statement.

The AS keyword is only optional in some cases, but it is recommended to always use it when creating the SQL statement, as it makes it clearer what is happening there.

3 Setting up the database and the required tables

I use the same database and tables in this post as in my post about 1:m relationships.

Here is the corresponding ERD:

Since I would like to have the output of SSMS always in English I use the following syntax.

SET LANGUAGE ENGLISH

How I create the database and the tables can be read in my post Create a Database.

CREATE DATABASE School_Class;


USE School_Class;

CREATE TABLE Pupils
    (Pupils_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;

CREATE TABLE Teacher
    (Teacher_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL,
    Subject_ VARCHAR(100) NOT NULL)
    ;

Now I add the relationship of the two tables to each other:

ALTER TABLE Pupils ADD Teacher_ID INT NOT NULL FOREIGN KEY REFERENCES Teacher(Teacher_ID);

The underlying ERD now looks like this:

In addition, I fill the tables with sample values:

INSERT INTO Teacher (Teacher_ID, First_Name, Last_Name, Subject_) VALUES (317, 'Andrew', 'Evergreen', 'History')

INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (1, 'Max', 'Steel', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (2, 'Jessy', 'Williams', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (3, 'Marc', 'Pike', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (4, 'Emily', 'Taylor', 317)
INSERT INTO Pupils (Pupils_ID, First_Name, Last_Name, Teacher_ID) VALUES (5, 'Alex', 'Smith', 317)

You have to pay attention to the order in which you add the values to the tables, as described here.

Here are the final tables:

SELECT * FROM Pupils
SELECT * FROM Teacher

4 Aliases

Now we can begin…

SELECT First_Name AS Forename, 
       Last_Name AS Surname,
       Teacher_ID AS [Teacher ID]
    FROM Pupils
    ;

SELECT p.First_Name, p.Last_Name
    FROM Pupils AS p
    ;

SELECT p.First_Name AS Forename,
       p.Last_Name AS Surname
    FROM Pupils AS p
    ;

SELECT p.First_Name AS Pupils_First_Name, 
       t.First_Name AS Teachers_First_Name
    FROM Pupils AS p,
         Teacher AS t
    WHERE p.First_Name='Max' AND p.Teacher_ID = t.Teacher_ID
    ;

SELECT *
    FROM Pupils
        LEFT JOIN Teacher
            ON Pupils.Teacher_ID = Teacher.Teacher_ID
    ;

SELECT First_Name
    FROM Pupils
        LEFT JOIN Teacher
            ON Pupils.Teacher_ID = Teacher.Teacher_ID
    ;

SELECT p.First_Name AS Pupils_First_Name, 
       t.First_Name AS Teachers_First_Name
    FROM Pupils AS p
        LEFT JOIN Teacher AS t
            ON p.Teacher_ID = t.Teacher_ID
    ;

SELECT Pupils.First_Name AS Pupils_First_Name, 
       Teacher.First_Name AS Teachers_First_Name
    FROM Pupils
        LEFT JOIN Teacher
            ON Pupils.Teacher_ID = Teacher.Teacher_ID
    ;

5 Conclusion

In this post I showed how you can use Alias to make your programming life extremely easy.