Subquery vs JOIN
intermediatesubqueries
2 min read
Subquery vs JOIN
Both subqueries and JOINs can retrieve related data from multiple tables, but they have different strengths. Understanding when to use each approach helps you write clearer, faster queries.
Comparison
-- Subquery approach
SELECT * FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 1);
-- JOIN approach
SELECT DISTINCT s.*
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.course_id = 1;When to Use Subqueries
- Existence checks (EXISTS / NOT EXISTS)
- Comparing to aggregated values
- When you only need columns from one table
- When logic is clearer as a nested question
When to Use JOINs
- When you need columns from multiple tables in the result
- When performance matters and indexes support the join
- When you need to aggregate across the joined data
Key Points
- Optimizer Equivalence — PostgreSQL often converts IN subqueries to joins internally.
- EXISTS vs JOIN — For existence checks, EXISTS is usually cleaner and equally fast.
- Columns Needed — If you need columns from both tables, JOIN is the natural choice.
- Readability — Choose the approach that makes the query's intent clearest.
- EXPLAIN ANALYZE — When in doubt, check both approaches with EXPLAIN ANALYZE.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Subquery vs JOIN Challenge
Write a query that find students enrolled in course 1 using a subquery.
Expected result
Names of students enrolled in course 1.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
Subquery vs JOIN
Initializing database...Each run starts from fresh sample data.
More Examples
Equivalent JOIN approach
The same result using a JOIN.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Are subqueries always slower than JOINs?
No. PostgreSQL's query optimizer often produces the same execution plan for both. Use EXPLAIN ANALYZE to compare actual performance.
When should I definitely use a subquery?
When checking existence (EXISTS), comparing to aggregates, or when you only need columns from the outer table. These cases are naturally expressed as subqueries.