PostgreSQL Aliases

SQL aliases in PostgreSQL are temporary names given to tables or columns to make queries more readable. They are especially useful when working with long column names, complex expressions, or multiple tables.

1. Column Aliases

A column alias renames a column in the result set. It is defined using the AS keyword (optional).

-- Rename a column in the output
            SELECT name AS employee_name, salary AS employee_salary
            FROM employees;

⚠️ The AS keyword is optional:

SELECT name employee_name, salary employee_salary
            FROM employees;

2. Aliases with Expressions

Column aliases are also useful for computed columns.

-- Calculate annual salary and give it an alias
            SELECT name, salary * 12 AS annual_salary
            FROM employees;

3. Table Aliases

Table aliases provide a temporary name for a table, often used in joins or subqueries for brevity.

-- Using table aliases in a join
            SELECT e.name, d.department_name
            FROM employees AS e
            INNER JOIN departments AS d ON e.department_id = d.id;

⚠️ Again, AS is optional:

SELECT e.name, d.department_name
            FROM employees e
            INNER JOIN departments d ON e.department_id = d.id;

4. Aliases with Subqueries

Table aliases are mandatory for subqueries in the FROM clause.

-- Subquery with alias
            SELECT sub.department, sub.total_salary
            FROM (
                SELECT department_id AS department, SUM(salary) AS total_salary
                FROM employees
                GROUP BY department_id
            ) AS sub
            WHERE sub.total_salary > 200000;

5. Aliases with JOINs and Aggregates

-- Count employees per department using aliases
            SELECT d.department_name AS dept, COUNT(e.id) AS total_employees
            FROM employees e
            INNER JOIN departments d ON e.department_id = d.id
            GROUP BY d.department_name
            ORDER BY total_employees DESC;

6. Best Practices

• Use meaningful aliases to improve query readability.
• Always alias computed columns to avoid ambiguous results.
• Use table aliases when joining multiple tables to shorten references.
• In subqueries, always provide a table alias.

Conclusion

SQL aliases in PostgreSQL are simple yet powerful for improving query readability and maintainability. They make your SELECT statements cleaner and are especially useful in joins, subqueries, and computed columns. In the next tutorial, we will explore the DISTINCT keyword to remove duplicate rows in query results.