Course contentsShow
← Data Science
Lesson 959 of 2,14525. SQL Subqueries and CTEsFree lesson

Introduction to Subqueries in WHERE

Learn what subqueries are, why they're useful in WHERE clauses, and basic syntax for filtering with subquery results.

Introduction to Subqueries in WHERE

What you'll learn: How to use nested queries inside WHERE clauses to filter rows based on dynamic, calculated criteria.

What Is a Subquery?

A subquery (also called a "nested query" or "inner query") is a complete SELECT statement embedded inside another SQL query. In the WHERE clause specifically, subqueries let you filter rows based on results computed on-the-fly from another table or the same table.

Think of it like asking a two-step question: "Show me all employees whose salary is above the company average." First, you need to calculate the average (step 1), then use that result to filter employees (step 2). A subquery does both in one query.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE column_name operator (SELECT column FROM other_table WHERE condition);

The subquery (in parentheses) runs first and returns a value or set of values. The outer query then uses that result to filter rows.

Why Use Subqueries in WHERE?

  • Dynamic filtering: Filter based on calculated values (like averages, maximums) rather than hardcoded numbers
  • Compare across tables: Filter rows in one table based on criteria from another
  • Self-contained logic: Keep related calculations within a single query instead of multiple separate queries

Simple Example Pattern

Instead of manually finding the average salary and then writing a second query, you can nest:

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

The subquery calculates the average; the outer query filters based on that result.

Key Takeaway: Subqueries in WHERE clauses let you filter rows dynamically using results from nested SELECT statements, enabling complex comparisons without hardcoding values.