SQL Aliases: Column and Table Aliases

SQL Aliases are temporary names given to columns or tables in a query, enhancing readability and making complex queries easier to manage. Column aliases simplify output by providing meaningful names for columns, while table aliases enable concise syntax in joins and subqueries. Here, we’ll cover both types of aliases, demonstrate their usage, and highlight any differences in syntax among SQL Server, MySQL, and PostgreSQL.

1. Column Aliases

Column aliases provide a new name for a column in the output, which is useful for presenting results more clearly. The AS keyword is typically used to define an alias, although it is optional in some databases.

Syntax (Column Alias)

SELECT column_name AS alias_name
FROM table_name;

Example: Using Column Aliases

Suppose we have an Employees table:

Querying the table with column aliases:

SELECT EmployeeID AS ID, FirstName AS "First Name", LastName AS "Last Name", HireDate AS "Date of Hire"
FROM Employees;

Result:

Notes:

• Quotation marks around aliases with spaces or special characters are required in all SQL dialects.
• In MySQL, backticks () may also be used, while double quotes (“`) are preferred in PostgreSQL and SQL Server.

Additional Example (Concatenated Alias)

For derived columns, you can combine fields into a single alias:

SELECT FirstName + ' ' + LastName AS FullName
FROM Employees;

This displays both FirstName and LastName as a single FullName column.

2. Table Aliases

Table aliases give a temporary name to tables in a query, making them especially helpful in complex joins or when referencing a table multiple times in a query.

Syntax (Table Alias)

SELECT column_name
FROM table_name AS alias_name;

Example: Table Aliases in Joins

Let’s assume two tables, Employees and Departments:

Employees Table:

Departments Table:

Using table aliases to perform an inner join:

SELECT e.FirstName AS "First Name", e.LastName AS "Last Name", d.DepartmentName AS "Department"
FROM Employees AS e
JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;

Result:

Notes:

• Aliases e and d make the query easier to read, especially if there are multiple tables involved.
• The AS keyword is optional when assigning table aliases in MySQL and SQL Server, so Employees e is also valid.
• PostgreSQL recommends using AS for clarity, though it’s also optional.

Combined Example: Column and Table Aliases in Subqueries

Using both column and table aliases in a complex query with a subquery:

SELECT d.DepartmentName AS Department, COUNT(e.EmployeeID) AS "Number of Employees"
FROM Departments AS d
JOIN (SELECT EmployeeID, DepartmentID FROM Employees WHERE HireDate > '2020-01-01') AS e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;

In this query:

• d and e are table aliases for Departments and the subquery on Employees, respectively.
• “Number of Employees” is a column alias providing clarity in the results.

Conclusion

SQL aliases enhance query readability and maintainability. Column aliases clarify output headers, while table aliases streamline references, especially in joins and subqueries. Understanding aliases can lead to cleaner, more efficient SQL code, particularly in large or complex databases.