Skip to main content
Practice

5

deck​

1. IN: Evaluate Subquery First​

SELECT name
FROM users
WHERE user_id IN (SELECT user_id FROM logins);
  • The subquery runs first and returns a static list
  • The outer query then checks each user_id against that list
  • Slower when the list is large or includes NULL

===

2. EXISTS: Row-by-Row Check​

SELECT name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM logins l WHERE l.user_id = u.user_id
);
  • The subquery runs once per outer row
  • It stops at the first match found
  • More efficient for correlated subqueries

===

3. NULL Trap with IN​

SELECT name
FROM users
WHERE user_id IN (SELECT user_id FROM logins);

If the subquery returns a NULL, even one, the whole condition might fail.

EXISTS is not affected by NULLs


===

4. Summary: Which Should You Use?​

Use CaseRecommendation
Comparing to a short listIN
Checking if match existsEXISTS
Subquery may return NULLsEXISTS
Correlated subqueryEXISTS

In general, EXISTS is safer and more efficient for real-world logic checks.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.