Subqueries — Complete Guide
Subqueries — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of PostgreSQL Tutorial on Toolliyo Academy.
On this page
PostgreSQL Tutorial · Lesson 19 of 100
Subqueries
SQL → Advanced
SQL · 1 — Queries · ~6 min · PostgreSQL — SQL & Queries
What is this?
A subquery is a SELECT nested inside another statement — in WHERE, FROM, or SELECT list. It can return one value, one row, or a table used like a derived table.
Why should you care?
Find Swiggy customers who ordered from restaurants with avg rating above 4.5 — filter customers WHERE id IN (subquery on restaurants).
See it live — copy this example
Run in pgAdmin or psql.
SELECT customer_id, email
FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM orders o
WHERE o.total_amount > (
SELECT AVG(total_amount) FROM orders
)
);What happened?
- Inner AVG computes mean order value.
- Middle subquery lists customers above average.
- Outer query returns their emails.
- Three scopes nest cleanly.
Practice next
- Seed customers and orders with varied totals.
- Run SELECT AVG(total_amount) alone first.
- Run full query and verify only high spenders appear.
- Use EXISTS instead of IN for the same customer filter.
- Place subquery in FROM: SELECT * FROM (SELECT ...) s.
Remember
Subqueries answer questions in stages. IN, EXISTS, and comparison operators each fit different shapes. Often rewrite as JOIN for planner flexibility.
PostgresVerse VIP segment
CRM tags customers spending above average for a loyalty pilot.
Outcome: Campaign SQL lives in one query reproducible in pgAdmin.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
