How to use Aliases in SQL

As a software developer, understanding SQL is an essential skill. Structured Query Language (SQL) is a programming language used to manage relational databases. It allows developers to enter, manipulate, and retrieve data in a relational database management system (RDBMS). Using SQL aliases can make writing SQL queries more efficient and user-friendly.

Aliases, or temporary names, are commonly used in SQL to give a new name to a table or column in a SELECT statement, subquery or inner join. Using a new name (or alias) can make a query shorter, more readable, and easier to maintain. This article will discuss the benefits of using aliases in SQL, how to create aliases and some commonly used aliases.

Why use Aliases in SQL

Aliases can provide the following benefits:

• Enhances Readability: When writing complex SQL queries, aliases can make code easier to read by shortening it and reducing the chances of errors.
• Makes Maintenance Easier: Aliases can also make maintenance easier by providing flexibility in the column names. When there is a need to change the names of columns or tables or rename them, modifying them across the entire query can be time-consuming, but if aliases are used, the changes can be made quickly and easily.
• Easy to type: Aliases are easier to type than complex table or column names as they can be shortened.
• Avoid ambiguity: When multiple tables are used in a query, aliases can help avoid ambiguities by indicating which table or column is being referred to.

Creating Aliases in SQL

SQL Aliases can be established in several places throughout SQL queries. Columns, tables or subqueries can all benefit from aliasing.

Alias for Table

Given the following code:

SELECT employeeID, firstname, lastname FROM employees;

We can shorten the table name to a single character using the AS keyword, and still produce the same result:

SELECT e.employeeID, e.firstname, e.lastname FROM employees AS e;

Alias for Column

The SELECT statement retrieves columns from a table. Often, the given name is not what you want the column to be called. For example, the employeeID column from the customers table could be selected with its short name “id” using the following code:

SELECT id = customerID FROM customers;

Aliasing using AS

Aliases are often created using the AS keyword.

SELECT employeeID AS id, firstname AS name, lastname AS surname FROM employees;

Aliasing without using AS

You can also create an alias without using AS as shown below.

SELECT employeeID id, firstname fName, lastname lName FROM employees;

Aliases in Subqueries

Aliasing can also be used in subqueries. A subquery can be used as an alias for a table or as a derived table. Let’s consider the following example:

SELECT * FROM orders
WHERE employeeID IN (
  SELECT employeeID FROM employees
  WHERE title = 'Sales Representative'
);

The subquery (SELECT employeeID FROM employees WHERE jobtitle=’Sales Representative’) can be aliased as follows:

SELECT * FROM orders
WHERE employeeID IN (
  SELECT s.employeeID FROM employees AS s
  WHERE s.title = 'Sales Representative'
);

Common Usage of Aliases

Aliases can be used in numerous ways in SQL, the most common ways are with the SELECT and JOIN functions.

SELECT Aliases

When a query has many columns, aliases can be used to shorten their names in the SELECT clause. The following example creates two aliases for the FirstName and LastName columns in the employees table.

SELECT FirstName AS fName, LastName AS lName FROM employees;

JOIN Aliases

Aliases are often used in joins to avoid confusion when performing joins with several tables. In the following example, the aliases “o” and “c” are used instead of typing out the entire names of the orders and customers tables:

SELECT * FROM orders AS o
JOIN customers AS c ON o.customerID = c.customerID;

Nested Aliases

Aliases can also be nested. When the same table is used multiple times in a query, the aliases can be nested to avoid ambiguity, as shown below:

SELECT p.name as Product, s.name as Supplier, d.quantity as Quantity FROM products p
INNER JOIN suppliers s ON p.supplierID = s.supplierID
INNER JOIN (SELECT productID, COUNT(*) Quantity FROM order_details GROUP BY productID) d ON d.productID=p.productID;

Frequently Asked Questions (FAQs)

Conclusion

In SQL, aliases can make the process of writing SQL queries more efficient, user-friendly, and easier to maintain. They help to reduce the complexity of the code and enhance its readability. Aliases can significantly reduce the number of keystrokes required when typing complex SQL queries. As a software developer, understanding SQL and its features is an essential part of your skill-set. Use aliases in SQL to make your code shorter, readable, and more maintainable.

📕 Related articles about MySQL

• MySQL Data Manipulation Statements: Everything You Need to Know
• Understanding MySQL Data Definition Statements: A Comprehensive Guide
• MySQL CREATE FUNCTION Statement: An In-Depth Guide
• Creating and Selecting a Database
• Installing MySQL on Solaris: A Comprehensive Guide
• MySQL Delete Data: A Comprehensive Guide to Data Deletion in MySQL