What are EXISTS and NOT EXISTS and how do they differ from IN?
This question compares boolean checks against set membership. It tests optimization and NULL handling.
Why Interviewers Ask This
EXISTS is often more efficient and handles NULLs better than IN. Interviewers check if you know the semantic differences and performance characteristics.
How to Answer This Question
Explain EXISTS checks for row existence (boolean). Contrast with IN which compares values. Highlight NULL sensitivity of NOT IN. Mention stopping at first match.
Key Points to Cover
- EXISTS returns boolean
- Stops at first match
- NOT IN is NULL-sensitive
- Performance differences
Sample Answer
EXISTS checks if a correlated subquery returns at least one row, returning a boolean and stopping at the first match. IN compares a value against a list. EXISTS is robust with NULLs, whereas NOT IN can return unknown if the subquery yields NULL. EXISTS often wins for large datasets.
Common Mistakes to Avoid
- Ignoring NULL behavior in NOT IN
- Thinking IN is always faster
- Confusing with JOIN logic
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
Describe a PRIMARY KEY and how it differs from a UNIQUE key
Medium
What is ER model in the DBMS?
Medium
FlipkartWhat is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is the difference between UNION and UNION ALL?
Easy
What is Join and explain its types?
Medium
FlipkartWhat is the ER model in DBMS?
Easy
Flipkart