Beginner LevelSQL & MySQL
What are Subqueries (Scalar, Correlated) and how does EXISTS work?
❓ Interview Prompt: Explain Subquery types, Scalar subqueries, Multi-row subqueries, Correlated subqueries, and `EXISTS` vs `IN` performance.
A Subquery is an SQL query nested inside a parent 'SELECT', 'INSERT', 'UPDATE', or 'DELETE' statement:
Subquery Categories:
1
Scalar Subquery: Returns a single value (1 row, 1 column).
2
Multi-Row Subquery: Returns a list of values evaluated with 'IN', 'ANY', or 'ALL'.
3
Correlated Subquery: References columns from the outer query, executing once per row evaluated by the outer query.
4
'EXISTS' Operator: Returns 'TRUE' as soon as a subquery returns at least 1 row, stopping evaluation early (faster than 'IN' for large datasets!).
Code Implementation Example
sql
-- Correlated Subquery using EXISTS
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id AND o.total_amount > 500.00
);💡Using a correlated subquery with EXISTS to filter users with large orders.
🎯 Interview Key Takeaway
Use EXISTS over IN when evaluating correlated subqueries against large target tables for faster short-circuit evaluation.
#SQL#Subqueries#EXISTS#Correlated#Beginner
Back to all SQL & MySQL Questions→