QuizzesSubquery vs JOIN vs CTE — When to Use Which — Concept Check
intermediate
sql

Subquery vs JOIN vs CTE — When to Use Which — Concept Check

Questions on Subquery vs JOIN vs CTE — When to Use Which. Subqueries, JOINs, and CTEs often produce the same execution plan, but the right choice depends on whether you need columns from both tables, multiple references, or readable multi-step logic.

8 minutes 8 questions Pass at 70%
Time Limit
8 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

Questions in this quiz

8 questions on Subquery vs JOIN vs CTE — When to Use Which — Concept Check. Work through them here, then start the quiz to answer them and see your score.

  1. 1.A database query needs to list order IDs and amounts for orders completed in 2024, including customer cities and product categories. Which approach is best?

    • AUse a correlated subquery for each column.
    • BUse a CTE for single-step logic.
    • CUse a JOIN to get columns from both tables efficiently.
    • DUse a subquery for existence checks.
  2. 2.You need to check if any users from Mumbai have placed orders. Which SQL construct is most appropriate?

    • AUse a correlated subquery in SELECT.
    • BUse a JOIN to combine orders and users.
    • CUse a subquery with EXISTS for the check.
    • DUse a CTE to list all users first.
  3. 3.A developer uses NOT IN to filter orders without matching users, but gets unexpected results. Why?

    • ANOT IN fails with NULLs, unlike NOT EXISTS.
    • BNOT IN is faster than NOT EXISTS.
    • CNOT IN requires a JOIN to work correctly.
    • DNOT IN is always equivalent to NOT EXISTS.
  4. 4.A query with multiple steps and repeated intermediate results runs slowly. What should you consider using?

    • AUse a JOIN to combine tables more efficiently.
    • BUse a subquery in WHERE for better performance.
    • CUse a CTE to organize and reuse intermediate results.
    • DUse a correlated subquery to reduce steps.
  5. 5.While optimizing a query, a developer finds that both JOINs and subqueries produce similar execution plans. What should guide their choice?

    • ACTEs are always more efficient than subqueries.
    • BSubqueries are always faster than JOINs.
    • CThe need for columns from both tables suggests a JOIN.
    • DJOINs are only for existence checks.
  6. 6.A query with a CTE is unexpectedly slow. What might be the cause?

    • AThe CTE might be scanned twice if not MATERIALIZED.
    • BThe optimizer ignores CTEs during execution.
    • CCTEs are inherently slower than subqueries.
    • DCTEs always require more memory than JOINs.
  7. 7.You need to ensure a query checks for user existence without retrieving user details. Which method is best?

    • AUse a correlated subquery for each user.
    • BUse a subquery with EXISTS for efficient checks.
    • CUse a CTE to list all user details first.
    • DUse a JOIN to combine user data.
  8. 8.A developer mistakenly uses a subquery in WHERE to select columns from both tables. What is the issue?

    • ASubqueries are faster than JOINs, so this is not an issue.
    • BSubqueries in WHERE cannot project columns from both tables.
    • CSubqueries can only be used for existence checks.
    • DSubqueries always require a correlated subquery.