SQL - Aliases

SQL Aliases

SQL aliases provide a way to give a table or a column a temporary, more descriptive name within a query. This is done using the AS keyword. Aliases are essential for improving query readability, handling columns with ambiguous names, and performing self-joins.

Column Aliases

Column aliases are used to make the column headings in your result set more user-friendly or to rename a column resulting from an expression or aggregate function.

Syntax

SELECT column_name AS alias_name
FROM table_name;

If the alias contains spaces or special characters, it must be enclosed in double quotes (standard SQL) or square brackets (SQL Server).

Example

Let’s retrieve customer information, but with more readable column names for a report.

SELECT
    ID AS "Customer ID",
    NAME AS "Customer Name",
    SALARY * 12 AS "Annual Salary"
FROM CUSTOMERS;

Output

Customer ID	Customer Name	Annual Salary
1	Ramesh	24000.00
2	Khilan	18000.00
3	Kaushik	24000.00
…	…	…

Table Aliases

Table aliases are used to shorten table names in a query, making it less verbose and easier to read, especially in queries involving multiple joins.

Syntax

SELECT c.ID, c.NAME, o.AMOUNT
FROM CUSTOMERS AS c
INNER JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID;

Note: In most SQL dialects, the AS keyword is optional for table aliases, but it’s often considered good practice to include it for clarity.

Practical Application: The Self-Join

A self-join is a join where a table is joined with itself. Table aliases are mandatory for self-joins to allow the database to distinguish between the two instances of the same table.

A classic use case is finding employees who earn more than their managers from a single EMPLOYEES table.

Example: Self-Join

Consider an EMPLOYEES table where each employee has a ManagerID that references the ID of another employee in the same table.

-- Sample EMPLOYEES table structure
CREATE TABLE EMPLOYEES (
    ID INT PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    SALARY DECIMAL(10, 2),
    ManagerID INT
);

-- We join the table to itself, treating one instance as 'Employees' and the other as 'Managers'.
SELECT
    e.NAME AS "Employee Name",
    e.SALARY AS "Employee Salary",
    m.NAME AS "Manager Name",
    m.SALARY AS "Manager Salary"
FROM
    EMPLOYEES AS e
INNER JOIN
    EMPLOYEES AS m ON e.ManagerID = m.ID
WHERE
    e.SALARY > m.SALARY;

In this query, e is the alias for the employee’s record, and m is the alias for the manager’s record. This allows us to compare e.SALARY with m.SALARY in the WHERE clause clearly and without ambiguity.

Best Practices for Aliasing

• Be Consistent: Use a consistent style for your aliases (e.g., first letter of the table, or a short, meaningful abbreviation).
• Be Clear: While short aliases like c and o are common, for very complex queries, a more descriptive alias like cust or ord can improve readability.
• Qualify All Columns: When using joins, it’s a best practice to prefix every column in your SELECT list and WHERE clause with its table alias (e.g., c.ID instead of just ID). This prevents ambiguity and makes the query easier to maintain.