Why subqueries are needed in PostgreSQL

Introduction

Subqueries help you get data step-by-step inside a bigger query. They let you ask a question inside another question.

When you want to find data based on a calculation or filter done first.

When you need to compare each row to a group result like max or average.

When you want to break a complex question into smaller parts.

When you want to use the result of one query as input for another.

When you want to filter rows based on values from another table.

Syntax

PostgreSQL

SELECT column1 FROM table WHERE column2 = (SELECT column2 FROM table2 WHERE condition);

A subquery is a query inside parentheses used in WHERE, FROM, or SELECT clauses.

Subqueries run first, then their result is used by the main query.

Examples

Find employees who work in the Sales department by first finding the department id.

PostgreSQL

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Find products priced higher than the average price.

PostgreSQL

SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products);

Find orders made on the most recent date.

PostgreSQL

SELECT customer_id, order_date FROM orders WHERE order_date = (SELECT MAX(order_date) FROM orders);

Sample Program

This query finds all employees who work in the Marketing department by first finding the department's id.

PostgreSQL

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Marketing');

Important Notes

Subqueries can return one value (scalar), a list of values, or a table.

Using subqueries can make queries easier to read and understand.

Too many subqueries can slow down your query; sometimes joins are faster.

Summary

Subqueries let you ask a question inside another question.

They help break complex queries into smaller steps.

Use them to filter or compare data based on other query results.