Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

Sub-queries in SQL

A subquery (also called an inner query or nested query) is a query inside another SQL query.

It’s used to filter, compare, or derive values dynamically.

🧠 Why Use Subqueries?

  • To compare data between tables

  • To fetch data based on other query results

  • To use aggregates (like MAX, AVG) in filters

📦 Basic Structure

SELECT name, age
FROM students
WHERE age > (
  SELECT AVG(age) FROM students
);

🧠 The inner SELECT runs first, then its result is used by the outer query

🔁 Example 1: Subquery in WHERE (Scalar)

✔️ Returns student(s) with highest marks

🧾 Example 2: Subquery with IN (Multiple Values)

✔️ Returns students enrolled in Math

💬 Example 3: Subquery in FROM (Inline View)

✔️ Subquery acts like a temporary table

📊 Example 4: Subquery in SELECT (Scalar Value)

✔️ Shows each student with their number of courses

🧩 Types of Subqueries

Type

Location

Returns

Example Keyword

🧮 Scalar Subquery

WHERE, SELECT

Single value

=, <, >

📃 Multi-row Subquery

WHERE

Multiple rows

IN, ANY, ALL

🧾 Correlated Subquery

WHERE, SELECT

Varies (uses outer row)

(See below)

📦 Inline View (FROM)

FROM clause

Table-like result

Used as virtual table

🔁 Correlated Subquery (Dynamic per row)

✔️ Compares each student's marks to average of their own section

✅ Summary

Subquery Location

Common Use

Keyword Examples

WHERE

Compare values or lists

IN, =, >, <

FROM

Treat as temporary table

SELECT * FROM (...)

SELECT

Add computed columns

SELECT (subquery)

Correlated

Compare using outer query data

Dynamic per row