PostgreSQL Aliases

What are Aliases?

Aliases are temporary names assigned to tables or columns to make queries more readable or concise. Aliases exist only during the execution of the query.

Column Aliases

Basic Syntax

SELECT column_name AS alias_name
FROM table_name;

-- Or omit the AS keyword
SELECT column_name alias_name
FROM table_name;

Column Alias Examples

-- Using AS keyword
SELECT 
    first_name AS fname,
    last_name AS lname,
    email AS contact_email
FROM users;

-- Omitting AS keyword
SELECT 
    first_name fname,
    last_name lname,
    email contact_email
FROM users;

-- Aliases with spaces (use quotes)
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    email AS "Email Address"
FROM users;

Aliases for Calculated Columns

-- Arithmetic operations
SELECT 
    product_name AS name,
    price AS original_price,
    price * 0.9 AS discounted_price,
    price * 0.1 AS savings
FROM products;

-- String concatenation
SELECT 
    first_name || ' ' || last_name AS full_name,
    UPPER(email) AS email_upper
FROM users;

-- Aggregate functions
SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS average_age,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

Aliases with Functions

-- Date functions
SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    AGE(order_date) AS age_since_order
FROM orders;

-- String functions
SELECT 
    name AS original_name,
    UPPER(name) AS uppercase_name,
    LOWER(name) AS lowercase_name,
    LENGTH(name) AS name_length
FROM products;

Table Aliases

Basic Syntax

SELECT alias.column_name
FROM table_name AS alias;

-- Or omit the AS keyword
SELECT alias.column_name
FROM table_name alias;

Table Alias Examples

-- Simple table alias
SELECT 
    u.id,
    u.name,
    u.email
FROM users AS u;

-- Omitting AS keyword
SELECT 
    u.id,
    u.name,
    u.email
FROM users u;

-- Aliases in multi-table queries
SELECT 
    u.name AS user_name,
    o.order_number,
    o.total AS order_total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Table Aliases in Complex Queries

-- Three-table join
SELECT 
    c.name AS customer_name,
    o.order_number,
    p.product_name,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- Self-join (aliases required)
SELECT 
    e1.name AS employee,
    e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Aliases in Subqueries

Derived Table Aliases

-- Subqueries must have aliases
SELECT 
    dept_stats.department,
    dept_stats.avg_salary,
    dept_stats.employee_count
FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE dept_stats.avg_salary > 50000;

CTE (Common Table Expression) Aliases

-- Aliases in WITH clause
WITH high_value_customers AS (
    SELECT 
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 10000
)
SELECT 
    c.name AS customer_name,
    hvc.total_spent
FROM high_value_customers hvc
INNER JOIN customers c ON hvc.customer_id = c.id;

Using Aliases in Different Clauses

WHERE Clause

-- Note: Cannot use column aliases directly in WHERE
-- Incorrect example
SELECT 
    price * quantity AS total
FROM order_items
WHERE total > 100;  -- Error!

-- Correct: Repeat the expression
SELECT 
    price * quantity AS total
FROM order_items
WHERE price * quantity > 100;

-- Or use a subquery
SELECT * FROM (
    SELECT 
        price * quantity AS total
    FROM order_items
) AS items
WHERE total > 100;

ORDER BY Clause

-- Can use column aliases in ORDER BY
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- Can also use column position
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary
FROM employees
ORDER BY 2 DESC;  -- Sort by 2nd column

GROUP BY Clause

-- Cannot use column aliases directly in GROUP BY
-- Incorrect example
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS order_count
FROM orders
GROUP BY year;  -- Error!

-- Correct: Repeat the expression
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

-- PostgreSQL-specific: Can use column position
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1;

HAVING Clause

-- Can use aggregate aliases in HAVING (PostgreSQL extension)
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;  -- PostgreSQL allows this

-- Standard SQL approach (more compatible)
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Practical Use Cases

Use Case 1: Report Generation

SELECT 
    p.product_name AS "Product Name",
    c.category_name AS "Category",
    COUNT(oi.id) AS "Times Sold",
    SUM(oi.quantity) AS "Total Quantity",
    SUM(oi.quantity * oi.price) AS "Total Revenue",
    AVG(oi.price) AS "Average Price"
FROM products p
INNER JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.product_name, c.category_name
ORDER BY "Total Revenue" DESC;

Use Case 2: Data Analysis

SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT customer_id) AS active_customers,
    COUNT(*) AS total_orders,
    SUM(total) AS monthly_revenue,
    AVG(total) AS avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY month
ORDER BY month DESC;

Use Case 3: Complex Calculations

SELECT 
    e.name AS employee_name,
    e.salary AS base_salary,
    e.salary * 0.1 AS bonus,
    e.salary * 1.1 AS total_compensation,
    d.name AS department_name,
    (
        SELECT AVG(salary) 
        FROM employees 
        WHERE department_id = e.department_id
    ) AS dept_avg_salary,
    e.salary - (
        SELECT AVG(salary) 
        FROM employees 
        WHERE department_id = e.department_id
    ) AS salary_difference
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Alias Naming Conventions

Good Practices

-- Use meaningful names
SELECT 
    customer_id AS cust_id,
    first_name AS fname,
    last_name AS lname
FROM customers;

-- Table aliases: short but clear
SELECT 
    c.name,
    o.order_number,
    p.product_name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

Practices to Avoid

-- Avoid: Too short, unclear
SELECT 
    a.x,
    b.y
FROM table1 a
INNER JOIN table2 b ON a.id = b.id;

-- Avoid: Using reserved words
SELECT 
    name AS select,  -- Bad
    price AS from    -- Bad
FROM products;

-- Avoid: Overly long aliases
SELECT 
    name AS this_is_a_very_long_and_unnecessary_alias_name
FROM products;

Special Cases

Quoted Aliases

-- Quotes required for:
-- 1. Spaces
SELECT name AS "Product Name" FROM products;

-- 2. Special characters
SELECT price AS "Price ($)" FROM products;

-- 3. Reserved keywords
SELECT status AS "Order" FROM orders;

-- 4. Case sensitivity (PostgreSQL is case-insensitive by default)
SELECT name AS "ProductName" FROM products;

Numeric Aliases

-- Can start with numbers (requires quotes)
SELECT 
    name AS "1st_name",
    email AS "2nd_email"
FROM users;

Performance Considerations

Aliases themselves don't affect query performance, but they can:

1. Improve readability: Make complex queries easier to understand
2. Simplify maintenance: Only need to change alias definition when table names change
3. Avoid ambiguity: Clarify column sources in multi-table joins

-- Use EXPLAIN to view execution plan
EXPLAIN ANALYZE
SELECT 
    u.name AS user_name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Best Practices

1. Column Aliases:

   • Use AS keyword for better readability
   • Provide meaningful names for calculated columns
   • Use descriptive aliases in reports

2. Table Aliases:

   • Always use table aliases in multi-table queries
   • Use short but clear aliases
   • Self-joins require aliases

3. Naming Conventions:

   • Use lowercase with underscores
   • Avoid reserved keywords
   • Maintain consistent naming style

4. Readability:

   • Use aliases to improve readability in complex queries
   • Explicitly use table aliases in JOIN conditions
   • Provide meaningful aliases for subqueries

Summary

Aliases are an important SQL query tool:

• Column Aliases: Provide temporary names for columns or expressions
• Table Aliases: Simplify table references, especially in JOINs
• AS Keyword: Optional but recommended
• Quotes: Required for spaces or special characters
• Scope: Only valid during query execution
• Limitations: Cannot be used directly in certain clauses

Mastering aliases makes SQL queries clearer and more maintainable.