SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEAdvanced

SQL Subqueries: How to Write Nested Queries

Introduction & Core Concept

A Subquery (or nested query) is an independent inner SELECT statement embedded directly inside another query's WHERE, FROM, or SELECT layer.

SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

This nested architectural pattern allows you to run analytical evaluations in stages—for instance, computing the global corporate benchmark average inside the inner subquery first, and then comparing individual employee rows against that dynamic benchmark in the outer parent layer.

Why & Where We Use It
  • Why We Use It: Allows you to perform complex multi-step filtering without hardcoding static numbers that become stale over time.
  • Where We Use It: Identifying top performers above department averages, filtering against dynamic sub-lists, and finding records associated with the most recent transaction dates.
    • Real-World Example

    StaffCorp HR executives want a report identifying top-performing employees who earn strictly more than the average salary of their specific corporate department. They use a Correlated Subquery where the inner average calculation dynamically filters to match the outer employee's department ID.

    Best Practices: What to Do & What NOT to Do
  • What to Do: If your subquery returns a single scalar value, use =, <, or >. If your subquery returns a list of multiple values, use IN.
  • What NOT to Do: Avoid writing deeply nested correlated subqueries on massive multi-million row tables if a simple JOIN or Window Function can achieve the same result, as correlated subqueries execute repeatedly for every single outer row!
    • Syntax & Pro Tips
    SELECT name, salary FROM employees WHERE department_id IN (SELECT id FROM departments WHERE city = 'New York');
    Interactive Sandboxed Terminal (Preloaded DB Schema: STAFFCORP)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1