[Solved] When might a subquery perform better than a JOIN? — Ans: When the subquery returns a small, filtered set used in a WHERE clause. | SQL

SQL - Subqueries

When might a subquery perform better than a JOIN?

AWhen joining large tables without indexes.

BWhen the database engine does not support JOINs.

CWhen the subquery returns a small, filtered set used in a WHERE clause.

DWhen the subquery is uncorrelated and returns many rows.

Step-by-Step Solution

Solution:

Step 1: Identify subquery advantages
Subqueries that return a small filtered set can reduce the data processed in the main query.
Step 2: Compare with JOIN scenarios
JOINs on large tables without indexes can be slower, but subqueries returning many rows usually perform worse.
Final Answer:
When the subquery returns a small, filtered set used in a WHERE clause. -> Option C
Quick Check:
Better subquery use = small filtered set [OK]

Quick Trick: Small filtered subqueries can outperform JOINs [OK]

Common Mistakes:

MISTAKES

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

When might a subquery perform better than a JOIN?