As SQL queries become more complex, you may need to use the result of one query inside another query. Two techniques that are useful for handling this are subqueries and Common Table Expressions (CTEs).
Both are widely used in PostgreSQL and are especially useful for data analysis, reporting, and working with large datasets.
What Is a Subquery?
A subquery is a SQL query written inside another SQL query.
The inner query produces a result that is then used by the outer query.
For example, suppose we have an employees table containing employee names and salaries. We want to find employees who earn more than the average salary.
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query:
SELECT AVG(salary)
FROM employees;
calculates the average salary.
The outer query then uses that result to find employees whose salary is greater than the average.
The basic idea is:
Inner query → produces a result
↓
Outer query → uses that result
This makes subqueries useful when one calculation depends on another query.
Why Use Subqueries?
Subqueries are useful when you need to perform an intermediate calculation without creating a separate table.
For example, you might want to:
- Compare a value with an average, maximum, or minimum
- Filter records based on another query
- Find customers who have placed orders
- Find products that meet a particular condition
- Check whether related records exist
- Use one query's result as input to another query
Subqueries are particularly useful for answering questions such as:
Which employees earn more than the average salary?
Which customers have placed an order?
Which products cost more than the average product price?
Subqueries with Aggregate Functions
One of the most common uses of subqueries is with aggregate functions such as:
AVG()
SUM()
MAX()
MIN()
COUNT()
For example, to find products that cost more than the average price:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
The subquery calculates the average price, while the outer query identifies products above that average.
Another example is finding the employee with the highest salary:
SELECT name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
Here, the subquery returns the highest salary, and the outer query finds the employee who earns it.
Subqueries with IN
A subquery can return multiple values.
When this happens, operators such as IN are useful.
Suppose we want to find customers who have placed orders worth more than KSh 50,000.
SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE amount > 50000
);
The inner query identifies the customer IDs associated with orders above KSh 50,000.
The outer query then uses those IDs to find the customers' names.
This is useful when the subquery returns multiple rows.
Subqueries with EXISTS
The EXISTS operator checks whether a subquery returns at least one record.
For example, to find customers who have placed at least one order:
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
The important question here is not what the subquery returns.
The question is:
Does a matching order exist?
If an order exists for a customer, that customer is returned.
EXISTS is particularly useful when you are interested in the existence of related records.
Subqueries in the FROM Clause
A subquery can also be placed inside the FROM clause.
For example:
SELECT department, average_salary
FROM (
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
) AS department_stats;
The inner query creates a temporary result containing the average salary for each department.
The outer query then works with that result as if it were a table.
What is a CTE?
CTE stands for Common Table Expression.
A CTE allows you to define a temporary named result using the WITH keyword.
Basic syntax:
WITH cte_name AS (
SELECT ...
)
SELECT *
FROM cte_name;
For example:
WITH average_salary AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (
SELECT avg_salary
FROM average_salary
);
The CTE is called average_salary.
Giving the intermediate result a name can make complex queries easier to understand.
Using multiple CTEs
One of the main advantages of CTEs is that you can create multiple steps within the same query.
For example:
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id
),
average_sales AS (
SELECT AVG(total_sales) AS avg_sales
FROM customer_sales
)
SELECT
customer_id,
total_sales
FROM customer_sales
WHERE total_sales > (
SELECT avg_sales
FROM average_sales
)
ORDER BY total_sales DESC;
The query can be understood as:
Calculate customer sales
↓
Calculate average customer sales
↓
Find customers above average
↓
Sort the results
This structure is often easier to read than using several deeply nested subqueries.
Subquery vs CTE
Subqueries and CTEs can sometimes solve the same problem, but they are structured differently.
| Subquery | CTE |
|---|---|
| Nested inside another query | Defined using WITH
|
| Good for simple operations | Good for multi-step operations |
| Can become difficult to read when deeply nested | Usually easier to organize |
| Usually used for a specific calculation | Can represent an intermediate dataset |
| Useful for filtering and comparisons | Useful for complex analysis |
| Does not support recursive queries | Supports recursive CTEs |
For example, a simple subquery is often perfectly suitable:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
There is no need to introduce a CTE when the logic is this simple.
However, when the query contains several stages, a CTE may provide a cleaner solution.
When Should You Use Subqueries?
Use a subquery when the logic is relatively simple and the intermediate result is only needed in one part of the query.
Common situations include:
- Comparing values with
AVG(),MAX(), orMIN() - Filtering with
IN - Checking records with
EXISTS - Finding records based on another query
- Performing a small intermediate calculation
For example:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
When should you use CTEs?
Use a CTE when your SQL query contains several logical steps or when you want to make complicated SQL easier to read.
CTEs are particularly useful for:
- Complex data analysis
- Reporting queries
- Multiple transformations
- Reusing intermediate results
- Organizing long SQL queries
- Recursive data structures
For example:
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT *
FROM monthly_sales
ORDER BY month;
The CTE separates the calculation of monthly sales from the final query.
Best Practices
Keep subqueries as simple as possible. If a query becomes heavily nested and difficult to understand, consider whether a CTE or JOIN would make the logic clearer.
Give CTEs meaningful names.
Instead of:
WITH x AS (...)
use:
WITH customer_sales AS (...)
A good name makes your SQL easier for other developers and analysts to understand.
Conclusion
Subqueries and CTEs are important tools for writing more powerful SQL queries.
A subquery is a query inside another query. It is particularly useful for simple calculations, filtering, comparisons, and checking whether related records exist.
A CTE uses the WITH keyword to create a named intermediate result. CTEs are especially useful when a query involves multiple logical steps and needs to be easier to read and maintain.
A simple rule to remember is:
Use subqueries for simple nested logic and CTEs for complex, multi-step SQL queries.
Once you understand both techniques, you can write cleaner and more effective PostgreSQL queries for data analysis, reporting, dashboards, and business intelligence.
Top comments (0)