IN vs EXISTS in SQL: Performance and Best Use Cases

IN and EXISTS often solve the same problem — checking whether a value relates to any row in another table — but they work differently under the hood, and choosing between them is one of the most frequently asked SQL interview questions. This lesson puts them side by side with the same query, rewritten both ways.

Key Definitions

• IN: An operator that checks whether a value matches any value in a list, often built from a subquery's full result set.
• EXISTS: An operator that checks only for the presence of at least one matching row via a correlated subquery, without needing the full result set materialized.

What You'll Learn

• Rewrite the same query using both IN and EXISTS to compare their behavior.
• Understand the general performance guidance for choosing between IN and EXISTS.
• Compare NOT IN against NOT EXISTS specifically for NULL safety.
• Apply a practical decision rule for choosing between the two in real queries.

Detailed Explanation

Both of these queries return patients with at least one Completed appointment: `WHERE patient_id IN (SELECT patient_id FROM appointments WHERE status = 'Completed')` and `WHERE EXISTS (SELECT 1 FROM appointments a WHERE a.patient_id = p.patient_id AND a.status = 'Completed')`. For small to moderately sized subquery results, MySQL's optimizer often produces very similar execution plans for both, so the practical performance difference can be minimal.

The general guidance that has held up well in practice: IN tends to read more naturally and can perform well when the subquery's result list is small and doesn't contain NULLs. EXISTS tends to perform more predictably and safely when the subquery could return a large number of rows or when NULL values might appear in the compared column, since EXISTS never suffers from the NOT IN NULL pitfall.

For the negated forms specifically, NOT EXISTS is almost universally recommended over NOT IN in production code, precisely because of the well-documented NULL behavior difference covered in the previous lesson. Ultimately, since modern MySQL query optimizers frequently rewrite IN and EXISTS into equivalent internal execution plans for straightforward cases, the choice increasingly comes down to readability and NULL-safety rather than raw performance alone — always verify with EXPLAIN on your actual data and MySQL version when performance is critical.

Visual Summary

Two side-by-side query boxes labeled 'IN Version' and 'EXISTS Version', both pointing down into a single shared box labeled 'MySQL Optimizer', which then produces a single 'Execution Plan' box below — illustrating that both forms often converge to similar underlying execution strategies for straightforward queries.

Quick Reference

Scenario	Generally Preferred
Small, NULL-free subquery result list	IN — often equally fine and reads naturally
Large or uncertain-size subquery result	EXISTS — can short-circuit on first match
Subquery result might contain NULL	EXISTS — NULL-safe by design
Negated check (absence)	NOT EXISTS — avoids the NOT IN NULL pitfall

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Version 1: using IN
SELECT patient_name
FROM patients p
WHERE p.patient_id IN (
  SELECT patient_id FROM appointments WHERE status = 'Completed'
);

-- Version 2: using EXISTS (equivalent result)
SELECT patient_name
FROM patients p
WHERE EXISTS (
  SELECT 1 FROM appointments a
  WHERE a.patient_id = p.patient_id AND a.status = 'Completed'
);

-- Use EXPLAIN to compare how MySQL actually plans to execute each version
EXPLAIN SELECT patient_name FROM patients p
WHERE p.patient_id IN (SELECT patient_id FROM appointments WHERE status = 'Completed');

Both versions return the identical result set: Amit Rao, Neha Joshi, and Divya Nair. The IN version builds a conceptual list of matching patient_id values and checks membership; the EXISTS version correlates row by row and checks for presence. Running EXPLAIN on both versions in your actual MySQL environment is the definitive way to confirm which execution plan MySQL chooses for your specific data and indexes, since general guidance can vary by MySQL version and table statistics.

Real-World Examples

• Database performance review checklists at many companies specifically flag NOT IN usage, recommending a rewrite to NOT EXISTS as a standard code review comment.
• Query optimization consultants routinely compare IN versus EXISTS execution plans using EXPLAIN when tuning slow-running reporting queries.
• SQL style guides at data-heavy companies often codify 'prefer EXISTS/NOT EXISTS over IN/NOT IN for existence checks' as a written best practice.

Common Mistakes to Avoid

• Assuming EXISTS is always strictly faster than IN without verifying against actual data using EXPLAIN.
• Using NOT IN in production code without being aware of its NULL-related risk.
• Not considering readability and team conventions when the performance difference is negligible for a given query.

Interview Questions

Q1. What is the main functional difference between IN and EXISTS?

IN checks whether a value matches any value within a list, typically built from a subquery's full result set. EXISTS checks only for the presence of at least one matching row via a correlated subquery, without necessarily needing the full result materialized.

Q2. When would you specifically choose EXISTS over IN?

When the subquery's result set could be large, when the compared column might contain NULL values, or for negated absence checks, where NOT EXISTS avoids the well-known NULL pitfall that affects NOT IN.

Q3. Is EXISTS always faster than IN?

Not necessarily. For small, NULL-free subquery results, modern MySQL optimizers often produce similar execution plans for both. The safest approach is to verify actual performance using EXPLAIN on your specific data rather than assuming one is universally faster.

Practice MCQs

1. Which is generally the safer choice for a negated existence check?

1. NOT IN
2. NOT EXISTS
3. Both are equally unsafe
4. Neither can be negated

Answer: B. NOT EXISTS

Explanation: NOT EXISTS avoids the well-known NULL pitfall that can cause NOT IN to silently return zero rows when the subquery result contains a NULL value.

2. What is the best way to determine whether IN or EXISTS performs better for a specific query?

1. Always assume EXISTS is faster
2. Always assume IN is faster
3. Run EXPLAIN on both versions with your actual data
4. Performance is identical in all databases

Answer: C. Run EXPLAIN on your actual data

Explanation: Since performance can vary by MySQL version, table size, and indexing, using EXPLAIN to compare actual execution plans is the most reliable way to decide.

Quick Revision Points

• IN and EXISTS often produce equivalent results but differ in execution strategy and NULL handling.
• NOT EXISTS is the generally recommended, NULL-safe alternative to NOT IN.
• Always verify with EXPLAIN rather than assuming performance differences universally.

Conclusion

• IN and EXISTS can solve the same problem but differ in execution strategy and NULL safety.
• NOT EXISTS is the safer, generally recommended choice over NOT IN for absence checks.
• Use EXPLAIN on your actual data to make performance-based decisions rather than relying on general assumptions.

IN and EXISTS frequently solve the same existence-checking problem but work differently under the hood: IN checks membership against a list, while EXISTS checks for row presence via a correlated subquery and can short-circuit on the first match. For their negated forms, NOT EXISTS is broadly recommended over NOT IN due to a well-documented NULL-handling pitfall in NOT IN. While general guidance favors EXISTS for large or NULL-uncertain subquery results, the definitive way to choose is running EXPLAIN on your actual data, since modern MySQL optimizers often rewrite both forms into similar execution plans for straightforward cases.