SQL Aliases

How to use a SQL alias?

We can use SQL alias in these ways: 

1. Column Alias:

Place the desired alias immediately after the column name in the SELECT statement using the AS keyword. For example:

SELECT employee_id AS ID, employee_name AS Name
FROM employees;

2. Table Alias: Specify the alias for the table after the table name in the FROM clause. Use this alias to reference columns from the table throughout the query. For example:

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;

Why Use SQL Aliases?

Readability and Simplicity

Aliases make your queries more readable. This is particularly helpful when you're dealing with complex queries or columns/tables with long or complicated names. Let's look at an example:

Without alias:

Let’s see the Employee table:

The departments table:

SELECT employees.FirstName, employees.LastName, departments.DepartmentName
FROM employees
JOIN departments ON employees.DepartmentId = departments.DepartmentId;

Output

With alias:
Output:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM employees AS e
JOIN departments AS d ON e.DepartmentId = d.DepartmentId;

Constructing Complex Queries

Aliases are not just about aesthetics and simplicity. They are indispensable when you're writing complex SQL queries, such as self-joins (a table joined with itself) or subqueries.

For instance, here's how you might use an alias in a self-join to find pairs of employees who work in the same department:

SELECT e1.FirstName, e1.LastName, e2.FirstName, e2.LastName 
FROM employees AS e1
JOIN employees AS e2 ON e1.DepartmentId = e2.DepartmentId AND e1.Employee_Id != e2.Employee_Id;

Output

Advantages of SQL Alias

The use of SQL aliases provides several advantages in query writing and result presentation. Here are the key benefits:

• Aliases make SQL queries more readable by providing shorter and more meaningful names for tables and columns. This is especially helpful when dealing with complex queries involving multiple joins and columns.
   
• Long or complex column names can be simplified using aliases, making the output more concise and improving the overall readability of the query.
   
• When multiple tables are involved in a query, and those tables have columns with the same name, aliases help avoid ambiguity. They allow you to clearly specify which table's column you are referencing.
   
• In scenarios where a table is joined with itself (self-join), aliases are necessary to distinguish between the columns from the same table.
   
• Aliases reduce the effort required for typing long table or column names throughout the query. This is particularly useful in situations where tables or columns are referenced frequently.

Frequently Asked Questions

1. Can the alias name be anything I choose?

Yes, but avoid using SQL keywords as aliases. Also, if the alias consists of multiple words, wrap it in quotation marks.

2. Are aliases permanent?

No, aliases exist only for the duration of the query.

3. Can I use aliases in UPDATE or DELETE statements?

No, aliases can only be used in SELECT statements and inside the FROM clause.

Conclusion

SQL aliases are an efficient tool that brings clarity and simplicity to your SQL statements. They streamline the syntax, increase readability, and enable the construction of more complex queries. Incorporating aliases into your SQL toolbox will undeniably help in managing your database more effectively.

Here are some more related articles:

• MySQL: Case Statement 
• LIMIT in SQL
• MySQL Introduction
• MySQL Features

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning!!