How to Use Subqueries and CTEs in PostgreSQ

Using Subqueries and CTEs in SQL

We can nest SQL inside SQL to create a new table as a result. This becomes necessary when we need to filter something that has already been filtered, requiring multiple WHERE clauses that can't simply be chained with AND.

Setting Up the Environment

Start PostgreSQL by clicking the Start PostgreSQL button. Once the environment is set up, open VS Code. In VS Code, open the integrated terminal by clicking the three lines menu, then Terminal, then New Terminal. Maximize the terminal window for more space.

Verify PostgreSQL is running:

sudo systemctl status postgresql

The output should show it's active and running. Connect to the database:

sudo -u postgres psql -d company

Finding Employees Earning More Than Their Department Average

Suppose we want to find every employee who earns more than the average salary of their own department, not the company-wide average. The employees table doesn't have a department average column, so we need a nested query.

The outer query selects name, department, and salary from the employees table, aliased as employee1. The WHERE clause filters salary to be greater than the department average, which comes from an inner query:

SELECT name, department, salary
FROM employees AS employee1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees AS employee2
    WHERE employee1.department = employee2.department
);

The inner query calculates the average salary for each department. For every row in the outer query, the inner query runs again for each department. This means for Alice and Bob, both in engineering, the average is calculated twice. This approach is slow and the SQL becomes cumbersome.

Using a Common Table Expression (CTE)

A CTE (Common Table Expression) lets you name a subquery with WITH and reuse it like a temporary table. First, create the CTE:

WITH dept_averages AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)

Now rewrite the query using the CTE:

SELECT employees.name, employees.department, employees.salary, dept_averages.avg_salary
FROM employees
JOIN dept_averages ON employees.department = dept_averages.department
WHERE employees.salary > dept_averages.avg_salary;

The dot notation (employees.department, dept_averages.department) avoids ambiguity since both tables have a department column. The JOIN connects the employees table with the dept_averages table. The WHERE clause filters for salaries greater than the department average.

Running this returns five rows showing employees who earn more than their department average, including Carla in engineering with a salary well above average.

The CTE reads from top to bottom, is cleaner, and can be reused. The dept_averages table can be referenced multiple times in the same query.

Using IN with Subqueries

The IN operator checks if a value matches any row returned by a subquery:

SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    WHERE salary > 90000
);

This returns all departments where at least one employee earns more than 90,000. In this case, only engineering appears.

Using ANY, ALL, MIN, and MAX

The ANY operator checks if a condition is true for at least one value returned by the subquery:

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'HR'
);

This returns employees whose salary is greater than at least the minimum salary in the HR department.

ALL, MIN, and MAX work similarly: ALL returns results if the condition meets every row in the subquery, MIN gets the minimum, and MAX gets the maximum.

Using EXISTS

EXISTS checks whether the subquery returns any rows at all, without needing to know the actual values:

SELECT DISTINCT department
FROM employees AS e1
WHERE EXISTS (
    SELECT 1
    FROM employees AS e2
    WHERE e2.department = e1.department
    AND e2.salary > 90000
);

The SELECT 1 is a convention; the actual value doesn't matter because we only check for existence. The DISTINCT ensures unique department names. This returns only engineering, the same result as the IN example.

EXISTS is often more efficient for yes/no checking on large tables because it stops scanning as soon as it finds a match.

CTE vs Subquery

Both CTEs and subqueries return the same answer. The difference is readability and structure. CTEs read from top to bottom and can be reused as a separate table multiple times. Subqueries go from inside to outside and can become messy with multiple nested queries.