Subquery vs JOIN in SQL: Which One Should You Use?

By now you've learned two different ways to combine and relate data across tables: JOIN, covered in Module 5, and subqueries, covered throughout this module. Many real problems can be solved with either approach, and knowing when to reach for which one — and being able to translate between them — is a hallmark of SQL fluency that interviewers specifically look for.

Key Definitions

• Equivalent query: Two syntactically different queries that produce the same logical result, such as a subquery-based and a JOIN-based version of the same question.
• Query rewriting: The practice of expressing the same logical question using a different SQL construct, often for readability or performance reasons.

What You'll Learn

• Solve the same problem using both a subquery and an equivalent JOIN.
• Understand the general readability and performance tradeoffs between the two approaches.
• Recognize which problems are naturally suited to a JOIN versus a subquery.
• Practice converting between subquery and JOIN formulations.

Detailed Explanation

Consider finding patients who have had at least one Completed appointment. The subquery-based version uses EXISTS or IN, as covered in earlier lessons. The equivalent JOIN version looks like: `SELECT DISTINCT p.patient_name FROM patients p JOIN appointments a ON p.patient_id = a.patient_id WHERE a.status = 'Completed'`. Both return the same patient names, but they arrive there differently: the subquery checks each patient independently for a related match, while the JOIN combines the tables directly and then relies on DISTINCT to collapse any duplicate patient rows caused by multiple matching appointments.

A general rule of thumb: use a JOIN when you need to display columns from both tables together in the final result — a subquery can't easily pull columns from the related table into your SELECT list. Use a subquery (particularly EXISTS) when you only need to filter based on a relationship without displaying the related table's columns at all, since EXISTS avoids the need for DISTINCT and doesn't risk row duplication from a one-to-many relationship.

On performance, modern MySQL optimizers often rewrite simple subqueries into semantically equivalent join-like execution plans internally, so the difference is frequently smaller than beginners expect. However, correlated subqueries executed row-by-row (Lesson 7.8) can sometimes underperform compared to an equivalent JOIN, especially on very large tables — always verify with EXPLAIN rather than assuming.

Visual Summary

Two side-by-side approaches solving the same question 'which patients have a Completed appointment?'. Left box: [Subquery/EXISTS approach — filters patients, returns only patient columns]. Right box: [JOIN approach — combines patient and appointment columns, needs DISTINCT to avoid duplicates]. Both arrows converge into a shared box labeled 'Same logical answer, different execution path'.

Quick Reference

Scenario	Better Fit
Need columns from both tables in the result	JOIN
Only need to filter based on a relationship, no related columns needed	Subquery (EXISTS/IN)
One-to-many relationship risks duplicate rows	Subquery avoids DISTINCT; JOIN needs DISTINCT or GROUP BY
Complex per-group aggregation needed alongside detail rows	Often JOIN + GROUP BY, or a derived table

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);

-- Subquery approach: only patient names, no duplicate risk
SELECT patient_name
FROM patients p
WHERE EXISTS (
  SELECT 1 FROM appointments a
  WHERE a.patient_id = p.patient_id AND a.status = 'Completed'
);

-- JOIN approach: same logical question, but can also show appointment details
SELECT DISTINCT p.patient_name, a.appointment_date
FROM patients p
JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.status = 'Completed';

The EXISTS version returns exactly one row per qualifying patient, with no risk of duplication, but cannot include appointment-specific columns like appointment_date. The JOIN version can include appointment_date alongside the patient name, but requires DISTINCT to prevent a patient with multiple Completed appointments from appearing more than once — a tradeoff worth understanding clearly rather than reaching for either construct by habit.

Real-World Examples

• Reporting dashboards typically favor JOIN when a full row of related details needs to be displayed together, such as an order alongside its customer and product info.
• Validation and eligibility checks — 'does this customer qualify for a discount' — typically favor EXISTS-based subqueries since only a yes/no filter is needed.
• Data engineering teams commonly benchmark both approaches with EXPLAIN when optimizing slow production queries before deciding which to keep.

Common Mistakes to Avoid

• Using a JOIN when only a filter is needed, then forgetting DISTINCT and getting unexpected duplicate rows.
• Assuming one approach is always faster than the other without verifying with EXPLAIN on actual data.
• Defaulting to subqueries out of habit even when the final result genuinely needs columns from the related table, requiring an unnecessary rewrite later.

Interview Questions

Q1. When would you choose a JOIN over a subquery?

Choose a JOIN when the final result needs to display columns from both related tables together, since a subquery used purely for filtering cannot easily surface columns from the related table into the outer SELECT list.

Q2. When would you choose a subquery (like EXISTS) over a JOIN?

Choose a subquery, especially EXISTS, when you only need to filter rows based on whether a relationship exists, without needing to display any columns from the related table — this also avoids the duplicate-row risk that a one-to-many JOIN can introduce.

Q3. Is a JOIN always faster than an equivalent subquery?

Not necessarily. Modern MySQL optimizers frequently produce similar execution plans for straightforward equivalent queries. Performance should be verified with EXPLAIN on your actual data rather than assumed, though correlated subqueries can sometimes underperform compared to an equivalent JOIN on very large tables.

Practice MCQs

1. Why might a JOIN-based query return duplicate rows that an EXISTS-based subquery would not?

1. JOIN always duplicates data
2. A one-to-many relationship can produce multiple joined rows per outer row without DISTINCT
3. EXISTS is broken by design
4. JOIN cannot use WHERE clauses

Answer: B. A one-to-many relationship can produce multiple joined rows per outer row without DISTINCT

Explanation: If a patient has multiple matching appointments, a JOIN produces one row per matching appointment, requiring DISTINCT to collapse duplicates, while EXISTS only checks for presence and never duplicates the outer row.

2. When is a subquery generally preferred over a JOIN?

1. When you need columns from both tables
2. When you only need to filter based on a relationship without displaying related columns
3. When you need to sort results
4. Subqueries should always be avoided

Answer: B. When you only need to filter based on a relationship without displaying related columns

Explanation: A subquery, especially EXISTS, is ideal for pure existence-based filtering without needing to display any data from the related table.

Quick Revision Points

• JOIN is generally preferred when related columns must appear in the final result.
• Subqueries (especially EXISTS) are generally preferred for pure existence-based filtering, avoiding duplicate-row risk.
• Performance comparisons should always be verified with EXPLAIN rather than assumed.

Conclusion

• JOIN and subqueries can often solve the same problem, but each has natural strengths.
• Use JOIN when related columns need to appear in the result; use a subquery when only filtering is needed.
• Always verify performance assumptions with EXPLAIN rather than relying on general rules alone.

Subqueries and JOINs frequently overlap in what problems they can solve, but each has a natural strength: JOIN excels when the final result needs to display columns from multiple related tables together, while a subquery — particularly EXISTS — excels at pure existence-based filtering without needing to surface related columns, naturally avoiding the duplicate-row risk that a one-to-many JOIN can introduce. Rather than defaulting to one construct out of habit, understanding both approaches and verifying performance with EXPLAIN when it matters is the mark of genuine SQL fluency.