SQL Alias

SQL alias is a temporary name used to rename a table or a column for the duration of a query. Aliases help make queries more readable and simplify column or table references, especially when using complex expressions or multiple joins.

 

Types of SQL Aliases

1. Column Alias
2. Table Alias

1. Column Alias

A column alias is used to rename a column for the duration of the query. The alias name is provided using the AS keyword (although it is optional).

Syntax:

SELECT column_name AS alias_name
FROM table_name;

Example:

SELECT first_name AS "Employee Name", salary AS "Monthly Salary"
FROM employees;

• The first_name column is renamed to Employee Name, and salary is renamed to Monthly Salary in the result set.

2. Table Alias

A table alias is used to assign a temporary name to a table, which is helpful when working with multiple tables in complex queries (like with JOIN operations). The alias is typically a short name to make the query easier to read.

Syntax:

SELECT column_name
FROM table_name AS alias_name;

Example:

SELECT e.first_name, e.salary
FROM employees AS e
WHERE e.salary > 3000;

• The employees table is given the alias e, and this alias is used to reference the table columns.

 

Key Features of SQL Aliases

1. Column Alias Without AS

While the AS keyword is optional, you can also create an alias without using it, as long as you separate the column name and alias with a space.

Example:

SELECT first_name "Employee Name", salary "Monthly Salary"
FROM employees;

• Both first_name and salary columns are aliased without using AS.

2. Table Alias Without AS

Similarly, you can use table aliases without the AS keyword.

Example:

SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > 3000;

• The employees table is aliased as e, without using AS.

SQL Alias in Joins

When using joins with multiple tables, aliases make the query easier to understand by avoiding the repetition of long table names.

Example:

SELECT e.first_name, e.salary, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;

• Here, the employees table is aliased as e and departments as d, making the query concise.

SQL Alias in Aggregations

You can also use aliases in aggregate functions to make the result more meaningful.

Example:

SELECT department, COUNT(*) AS "Number of Employees"
FROM employees
GROUP BY department;

• The count result is aliased as "Number of Employees".

 

Summary

SQL aliases help simplify queries and enhance readability by giving temporary names to columns and tables. Aliases are especially useful in queries with multiple tables or complex expressions, like JOINs, and make it easier to reference specific columns. Aliases can be applied with or without the AS keyword, though using it is recommended for clarity.