Structured Query Language (SQL or SEQUEL) is a domain-specific programming language for storing, manipulating, retrieving, and managing data stored in a relational database. Structured Query Language is used to issue the CRUD commands (Create/Insert data, Read/Select data, Update data, Delete data ) to a database.

When running a query, an SQL alias creates a temporary name for a column or a table. The original name for the column or table remains unchanged without modifications to the database's actual data. There are several reasons why aliases are essential, from making a table name readable to naming an aggregate value for a particular column. In this article, I will discuss the importance of aliases and highlight some use cases.

Prerequisites

Having a basic familiarity with SQL queries is beneficial to understand SQL aliases effectively. It is also necessary to have a database management system (DBMS) such as PostgreSQL or MySQL accessible. These may be installed locally or accessed remotely. A way to interact with the DBMS, such as the Command Line Interface (CLI) or Graphical User Interface (GUI) tools, such as MySQL workbench or pgAdmin, is also required.

SQL Alias Syntax

Generally, aliases are specified after column names, usually preceded by the AS keyword:

SELECT table_name AS table_alias
SELECT column_name AS column_alias;

However, an alias can also be specified without using a keyword:

SELECT table_name table_alias
SELECT column_name column_alias;

This syntax works perfectly in many SQL flavours.

In this article, we will be working with the employee database, which contains three tables:

• The employee records table;

• The employee department table and

• The salaries table.

Next, let's examine some scenarios for using SQL aliases.

String Concatenation

Concatenation is the process of appending one string to the end of another, forming a new string. SQL supports string concatenation for character (CHAR), variable character (VARCHAR) and TEXT data types. In MySQL, the CONCAT() function is used to concatenate strings.

SELECT
CONCAT (column1, ‘ ’, column2) AS column_x
FROM table_name;

Using aliases when concatenating strings is not just best practice but is highly necessary. An alias is essential because the column name becomes readable and not a function.

Scenario

In the employee records table, select the employee's first and last name as fullName.

SELECT CONCAT(firstName, ' ' , lastName) AS fullName
FROM employeeRecords;

Otherwise, the query returns the CONCAT argument as the column name without an alias.

Self Joins

SQL joins retrieve data from multiple tables in a database, allowing you to combine records into a resulting table. A join is achieved using primary and foreign keys or values common to each table. A self-join is a type of join that compares values from part of a table to other values from within the same table.

SELECT t1.column1, t2.column2
FROM table_name AS t1
JOIN table_name AS t2
ON t1.column3 = t2.column3

Using aliases when comparing a table to itself is not just best practice but is highly necessary. An alias is essential in this case for several reasons; one, the table is referenced more than once and adding an alias renames it to make it distinguishable. Second, the database treats each alias as if it were two different tables.

Scenario

Employees have been assigned managers in the employee records table and perform an inner join to determine who supervises.

SELECT employee.employeeID, 
        employee.firstName,
        supervisor.firstName
FROM employeeRecords AS employee
INNER JOIN employeeRecords AS supervisor
ON employee.supervisorId = supervisor.employeeId;

Without aliasing, the query result shows two columns with firstName, which makes it hard to distinguish between the employee and their supervisor.

Rewrite the query to include aliases for the employee and supervisor names.

SELECT employee.employeeID, 
        employee.firstName AS employee,
        supervisor.firstName AS manager
FROM employeeRecords AS employee
INNER JOIN employeeRecords AS supervisor
ON employee.supervisorId = supervisor.employeeId;

Arithmetic Operators

SQL supports arithmetic operations on numeric values. An operator is a symbol or keyword reserved for SQL operations. These are; addition (+), subtraction (-), multiplication (*), division (/), and modulo (%) operators.

To add a number to a column that contains numeric values:

SELECT numeric_column + numeric_value
FROM table_name;

Rewrite this query to calculate employees’ annual income.

SELECT salary * 12
FROM employeesalaries;

The query returns the arithmetic operation as the column name without an alias.

Rewrite the query with an alias called annualPay for an employee’s annual salary.

Scenario

For the last month of the year, the company will pay all employees a bonus of 10% of their salary. Let’s determine each employee’s December salary.

SELECT salary, role,
salary + salary * 0.1  AS decemberSalary
FROM employeesalaries;

If an alias was omitted in this query, the arithmetic operation gets printed out as the column name.

Aggregate Functions

Aggregate functions in SQL compute a set of values and return a single value. The most common aggregate functions are; Count, Sum, Average, Min and Max. Aggregate functions are suffixed with a parenthesis, and a column name is passed as the argument.

SELECT column_name,
        FUNC(column_name)
FROM table_name

• COUNT(*) - counts the total number of rows in a table.

• SUM(column_name) - adds all data in a column; this works only for numeric data.

• AVG(column_name) - returns the average value of a column.

• MAX(column_name) - returns the maximum value in a column.

• MIN(column_name) - returns the minimum value in a column.

The employee salaries table determines the total pay for all employees in a year without aliasing the mathematical function.

SELECT AVG(salary * 12)
FROM employeesalaries;

Scenario

Determine the total salary the company pays in December for all its employees.

Our company will pay $118,800 for December wages. But what an odd column name!

Rewrite the query to include an alias.

SELECT salary, role,
SUM(salary + salary * 0.1)  AS totaldecemberSalary
FROM employeesalaries
GROUP BY salary;

Conclusion

This article explained some use cases of SQL Alias. Additionally, aliases makes queries easier to read, helps to avoid repetition and allows you to reference tables easily.

I hope you enjoyed reading this article. Thank you for reading.

See you next time.