Joins · SQL SQL
Explain the semantic difference between placing a condition in the ON clause versus the WHERE clause when performing a LEFT OUTER JOIN.
Practice question from Joins (SQL SQL), with the correct answer and a step-by-step solution.
medium · Subjective · Q-215633
Explain the semantic difference between placing a condition in the ON clause versus the WHERE clause when performing a LEFT OUTER JOIN.
Answer: For a LEFT OUTER JOIN, conditions placed in the ON clause are evaluated before the outer join is constructed, meaning filtering on the right table still preserves unmatched left rows with NULLs. Conditions placed in the WHERE clause are evaluated after the join operation is complete, effectively turning the outer join into an inner join if you filter out the NULLs generated by non-matching rows.
Solution
Placing criteria in the ON clause filters the right table before joining, protecting the outer join behavior. Placing the same criteria in the WHERE clause filters the final result set, which can eliminate the NULL rows produced by the outer join and alter the query outcome.
More from Joins
- What is the primary difference between an INNER JOIN and a LEFT OUTER JOIN in ANSI SQL?
- Consider two tables: A with 5 rows and B with 4 rows. If you perform a CROSS JOIN between A and B without any WHERE clause, how many rows w…
- What will be the behavior of a FULL OUTER JOIN between Table X and Table Y if there are rows in Table Y that do not match any rows in Table…
- Which of the following queries demonstrates a self-join?
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed