Understanding Subqueries in PostgreSQL- Part 1

Subqueries, often called nested queries or inner queries, are a powerful feature in SQL that allow you to execute one query inside another. In PostgreSQL, subqueries enable you to break down complex logic into smaller, modular components, making your SQL statements more flexible, readable, and efficient. Whether you’re filtering data, performing calculations, or driving conditional logic, subqueries help you achieve results that would be difficult or impossible with a single standalone query. This makes them an essential tool for anyone aiming to write advanced, optimized SQL in PostgreSQL.

What is a Subquery?

A subquery (also called a nested query) is a query that is written inside another query.
It allows you to use the result of one query inside another query.

• The inner query is called the subquery.
• The outer query is the main query that uses the subquery’s result.

Subqueries allow you to:

• Filter results based on another query
• Compare values between different tables
• Calculate data dynamically (like averages, totals, or counts)
• Use the output of one query inside another query

Instead of running multiple queries separately, a subquery helps you accomplish the same result in one single SQL statement.

Why do we use Subqueries?

Subqueries are especially helpful when you want to:

• Filter rows using results from another computation
• Compare table values without writing complex joins
• Create on-the-fly calculated tables
• Process per-row calculations (like departmental averages)
• Update/Delete records based on dynamic conditions

They are flexible, readable, and powerful—making them an essential SQL tool for any PostgreSQL user.

 Example 1: Subquery in the WHERE Clause

Scenario:

We want to find all employees who earn more than the average salary of all employees.

Tables:

employees(emp_id, emp_name, emp_salary)

Query:

SELECT emp_name, emp_salary
FROM employees
WHERE emp_salary > (SELECT AVG(emp_salary) FROM employees);

Explanation:

The inner query (subquery) :

SELECT AVG(emp_salary) FROM employees;   
--- Let's call the output from this query avgsal for the sake of simplicity 

The outer query:

SELECT emp_name, emp_salary
FROM employees
WHERE emp_salary > avgsal             --- avgsal = (output of the subquery)

 It retrieves only employees whose salaries are greater than the average.

 Result: You get a list of employees earning above-average salaries.

 Example 2: Subquery in the FROM Clause

Scenario:

You want to list each department along with its average salary.

Tables:

employees(emp_id, emp_name, emp_salary, dept_id)
departments(dept_id, dept_name)

Query :

SELECT d.dept_name, avg_s.avg_salary
FROM departments d
JOIN (
    SELECT dept_id, AVG(emp_salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) avg_s
ON d.dept_id = avg_s.dept_id;

Explanation:

• The subquery (inside FROM) calculates the average salary per department.
• The outer query joins that subquery result with the departments table to show the department names.

 Example 3: Subquery in the SELECT Clause

Scenario:

You want to list each employee’s name and their department’s average salary.

Query

SELECT 
    emp_name,
    emp_salary,
    (SELECT AVG(emp_salary) 
     FROM employees e2 
     WHERE e2.dept_id = e1.dept_id) AS dept_avg_salary
FROM employees e1;

Explanation:

• For each employee, the subquery finds the average salary of their department.
• The subquery runs once for each employee row (called a correlated subquery).

Example 4: Subquery in an UPDATE Statement

Scenario:

I want to increase the salary of employees who earn less than the average salary.

Query:

samdb=# UPDATE employees
SET emp_salary = emp_salary * 1.10
WHERE emp_salary < (SELECT AVG(emp_salary) FROM employees);
UPDATE 5000

Explanation:

• The subquery calculates the average salary.
• The outer UPDATE increases the salary by 10% for those below the average.

Summary:

In Part 1 of this series, we explored the fundamental concepts of subqueries and how they operate within different parts of a SQL statement. We have learned what subqueries are, why they are used, and how both correlated and non-correlated subqueries behave during execution. We walked through practical, real-world examples demonstrating subqueries in the WHERE, FROM, and SELECT clauses, as well as inside UPDATE statements, giving you a solid understanding of how subqueries help simplify complex logic in PostgreSQL.

Looking ahead to Part 2, we will dive even deeper into the power of subqueries. You’ll learn how subqueries work within INSERT and DELETE statements, explore advanced correlated subquery patterns, and understand important performance considerations. We will also look at when it’s better to replace subqueries with JOINs, and we’ll wrap up with best practices and real-world examples that help you write cleaner, faster, and more efficient SQL.