When writing database queries, you will often find that the default column names returned by your tables are cryptic, long, or represent complex calculations that lack a clean label. To resolve this and make your query outputs readable, developers use column aliases in sql to assign temporary, friendly names to columns and tables.

In this guide, we will examine how to define column aliases in sql, discuss table aliases, and explain the crucial execution rules that dictate where you can and cannot use aliases in your queries.

What is an Alias in SQL?

An alias is a temporary name assigned to a database column or table for the duration of a query’s execution. Aliases do not change the actual database schema; they only exist to rename columns or tables in the query’s final output set.

1. Column Aliases (AS Keyword)

To create a column alias, use the AS keyword followed by the temporary name. While the AS keyword is optional in most SQL engines, including it is a best practice for code readability. There are three main scenarios where column aliases are used:

Scenario A: Renaming Cryptic Column Names

// Rename cryptic database field name
SELECT emp_ph_no_primary AS phone_number 
FROM employees;

Scenario B: Naming Computed Expressions

// Calculate annual salary and assign a label
SELECT first_name, salary * 12 AS annual_salary 
FROM employees;

Scenario C: Naming Aggregated Results

// Assign a clean label to count values
SELECT department_id, COUNT(*) AS total_staff 
FROM employees 
GROUP BY department_id;

2. Table Aliases

In addition to columns, you can also assign aliases to tables. This is especially useful in multi-table JOIN operations to avoid writing long table names repeatedly, or when performing self-joins on the same table:

// Shorten table names using 'e' and 'd' aliases
SELECT e.first_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id;

Column Aliases vs. Table Aliases

The Golden Rule: Why Aliases Fail in the WHERE Clause

A classic database developer interview question is: *Why does the database throw an error if you reference a column alias in the WHERE clause?*

For example, this query will **fail** on almost all relational database engines:

// ERROR: column "annual_salary" does not exist
SELECT first_name, salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

This failure occurs due to the **logical query processing order** of SQL. The database engine executes query clauses in the following sequence:

1. FROM & JOIN (locate target data)
2. WHERE (filter raw rows)
3. GROUP BY (aggregate data)
4. HAVING (filter aggregated rows)
5. SELECT (**Column aliases are created here**)
6. ORDER BY (sort output results)

Because the WHERE clause is executed **before** the SELECT clause, the alias annual_salary does not exist yet when the filtering takes place. However, because ORDER BY executes **after** SELECT, you can safely use column aliases to sort your outputs:

// SUCCESS: ORDER BY recognizes "annual_salary"
SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

How to Filter by an Alias (The Workaround)

If you need to filter by a calculated column, you can wrap the query inside a Common Table Expression (CTE) or a Subquery, so the alias is evaluated in a previous step:

WITH SalaryCTE AS (
    SELECT first_name, salary * 12 AS annual_salary 
    FROM employees
)
SELECT * FROM SalaryCTE 
WHERE annual_salary > 100000;

Summary

In summary, using column aliases in SQL improves readability, but you must respect the query processing order when writing filters. For more details on writing advanced nested queries, see our guide on Subqueries in SQL. You can also explore the official Microsoft SQL Server SELECT Documentation for more advanced syntax variations.