Home / SQL / Aliases

Aliases

Overview

Aliases in SQL are temporary names given to tables or columns for the duration of a query. They make queries more readable and can shorten complex SQL statements.

Aliases are created using the AS keyword, though the AS keyword is optional in most databases. They are particularly useful when working with long table names or when multiple tables have columns with the same name.

Column Aliases

Column aliases rename columns in the result set, making output more readable:

Column Alias Syntax

SELECT column_name AS alias_name
FROM table_name;

-- AS keyword is optional
SELECT column_name alias_name
FROM table_name;

Examples

Example 1: Simple Column Alias

Rename column

SELECT 
    customer_id AS id,
    name AS customer_name,
    email AS email_address
FROM Customers;

Example 2: Alias with Calculations

Calculated column alias

SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total_value
FROM Products;

Example 3: Alias with Aggregate Functions

Aggregate function alias

SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS average_price,
    SUM(quantity) AS total_quantity
FROM Products
GROUP BY category;

Example 4: Alias without AS Keyword

AS is optional

-- Both are equivalent
SELECT name customer_name FROM Customers;
SELECT name AS customer_name FROM Customers;

Example 5: Alias with String Concatenation

Full name alias

SELECT 
    first_name || ' ' || last_name AS full_name
FROM Customers;

-- Or using CONCAT (database-specific)
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM Customers;

Table Aliases

Table aliases shorten table names, especially useful in JOINs and when tables have long names:

Table Alias Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

-- AS keyword is optional for tables too
SELECT column_name(s)
FROM table_name alias_name;

Example 1: Simple Table Alias

Short table name

SELECT 
    c.customer_id,
    c.name,
    c.email
FROM Customers AS c;

Example 2: Table Alias in JOIN

Aliases in JOINs

SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers AS c
INNER JOIN Orders AS o ON c.customer_id = o.customer_id;

Example 3: Multiple Table Aliases

Complex JOIN with aliases

SELECT 
    c.name AS customer,
    p.product_name AS product,
    oi.quantity,
    oi.unit_price
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
INNER JOIN OrderItems oi ON o.order_id = oi.order_id
INNER JOIN Products p ON oi.product_id = p.product_id;

Aliases in WHERE Clause

Important: You cannot use column aliases in WHERE clauses. You can use them in ORDER BY and HAVING:

Alias usage limitations

-- ❌ WRONG: Cannot use alias in WHERE
SELECT 
    name AS customer_name,
    email
FROM Customers
WHERE customer_name = 'John';  -- Error!

-- ✅ CORRECT: Use original column name in WHERE
SELECT 
    name AS customer_name,
    email
FROM Customers
WHERE name = 'John';  -- Use original column name

-- ✅ CORRECT: Can use alias in ORDER BY
SELECT 
    name AS customer_name,
    email
FROM Customers
ORDER BY customer_name;  -- Alias works here

Aliases in ORDER BY

You can use aliases in ORDER BY clauses:

Sort by alias

SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category
ORDER BY product_count DESC;  -- Using alias

Aliases in HAVING

You can use aliases in HAVING clauses (though some databases may require the full expression):

HAVING with alias

SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category
HAVING product_count > 10;  -- Using alias (works in most databases)

Quoted Aliases

Use quotes for aliases containing spaces or special characters:

Alias with spaces

SELECT 
    customer_id AS "Customer ID",
    name AS "Customer Name",
    email AS "Email Address"
FROM Customers;

Best Practices

Use descriptive names: Make aliases meaningful and clear
Keep aliases short: Especially for tables in JOINs
Be consistent: Use consistent naming conventions
Use AS keyword: More explicit, even though optional
Quote when needed: Use quotes for spaces or special characters
Avoid reserved words: Don't use SQL keywords as aliases

Common Use Cases

Renaming columns for better readability
Shortening long table names in JOINs
Creating meaningful names for calculated columns
Resolving column name conflicts in JOINs
Improving query readability

Alias Naming Conventions

Common conventions for aliases:

Type	Convention	Example
Table Aliases	Single letter or abbreviation	`c` for Customers, `o` for Orders
Column Aliases	Descriptive, readable names	`customer_name`, `total_amount`
Aggregate Aliases	Descriptive of the operation	`product_count`, `average_price`

Next Steps

Learn about combining tables with SQL Joins, where aliases are particularly useful.

← Between Joins →