When is a join often preferred over a subquery?

Mediumsqljoins-vs-subqueries

Last reviewed 21 Aug 2026

Joins can never be used alongside subqueries
When you need to retrieve columns from both tables in the resultCorrect answer
When the tables involved have no relationship at all
When you never need any data from either table

Show answer & explanation

Correct answer: B. When you need to retrieve columns from both tables in the result

Explanation

The deciding factor is the SELECT list. A subquery in WHERE answers a yes-or-no question about the inner table and then vanishes; PostgreSQL notes that for EXISTS, "the output list of the subquery is normally unimportant" because only the existence of a matching row matters. So a filter such as WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id) can identify customers who ordered, but it cannot place the order date in the result. A join can. SQLite describes the joined dataset as carrying all the columns of the left-hand dataset followed by all the columns of the right-hand dataset, which is the property required when both tables must appear in the output.

Resist the folklore that joins are inherently faster. PostgreSQL documents that the planner merges sub-queries into upper queries, and MySQL transforms IN and EXISTS predicates into semijoins, so the two forms frequently compile to the same plan. Option C fails on its face, since joins and subqueries routinely appear in one statement. Options B and D describe cases where no join is warranted at all.

Sources

Primary sourcePostgreSQL: Documentation: 18: 9.24. Subquery Expressions — PostgreSQL Global Development Group · accessed 21 Aug 2026
“Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally unimportant.”
Primary sourceSQLite Query Language: SELECT — SQLite Consortium · accessed 21 Aug 2026
“The columns of the cartesian product dataset are, in order, all the columns of the left-hand dataset followed by all the columns of the right-hand dataset.”
Primary sourcePostgreSQL: Documentation: 18: 19.7. Query Planning — PostgreSQL Global Development Group · accessed 21 Aug 2026
“The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items.”

Practise 3 questions on this topic

Take SQL Subqueries — Timed Test (3 questions) — scored instantly, explanation for every question, no login.

Start the test

When is a join often preferred over a subquery?

Related questions

When is a join often preferred over a subquery?

Related questions