SQL Subquery vs JOIN: Simple Explanation with Easy Examples

A subquery is a query (SQL statement) inside another query.It’s like asking one question inside another question to get your final answer.

Syntax:

sql

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);

The inner query runs first and returns a result.
The outer query then uses that result.

Example of a Subquery

Imagine two tables:

students

id	name	class_id
1	Ramesh	101
2	Priya	102
3	Amit	101

classes

class_id	class_name
101	Science
102	Arts

Question: Find students who belong to Science class.

Using subquery:

sql

SELECT name
FROM students
WHERE class_id = (SELECT class_id FROM classes WHERE class_name = 'Science');

Steps:

Inner query: (SELECT class_id FROM classes WHERE class_name = 'Science') → gives 101
Outer query: SELECT name FROM students WHERE class_id = 101 → gives Ramesh, Amit

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column.
Instead of asking one question inside another, you merge data together.

Example with JOIN:

sql

SELECT students.name, classes.class_name
FROM students
JOIN classes ON students.class_id = classes.class_id
WHERE classes.class_name = 'Science';

Result:

name	class_name
Ramesh	Science
Amit	Science

Difference Between Subquery and JOIN

Feature	Subquery (Nested Query)	JOIN (Table Combination)
Definition	Query inside another query	Combines rows from two or more tables
Execution	Inner query runs first, then outer query uses result	Both tables are scanned and matched at same time
Performance	Sometimes slower (depends on DB & indexes)	Often faster for related data
Readability	Easy for simple checks	Better for comparing columns across tables
Use case	When you need a value from another query (single/multi)	When you need columns from multiple tables

Think of it like real life:

Subquery = “I’ll first ask the librarian which room Science class is in (inner query), then I’ll ask which students are in that room (outer query).”
JOIN = “I’ll take the list of students and the list of classes, and directly match them together in one go.”