Q12: What is the difference between EXISTS and IN operators?
- IN: Evaluates full inner subquery result lists before matching values against outer query expressions. If the subquery set contains
NULLvalues,NOT INqueries can evaluate toUNKNOWNand return zero rows. - EXISTS: Uses semi-join short-circuit logic. It checks for the existence of matching rows and returns
TRUEas soon as a single match is found, ignoring subsequent rows.
Key Takeaways
- EXISTS utilizes short-circuit processing, stopping evaluation immediately upon finding a matching row.
- Be careful with
NOT INif the target subquery set containsNULLvalues.