Non-Correlated Subquery in SQL Explained

Most of the subqueries introduced earlier in this module — the AVG salary filter, the IN membership check — share one important trait: they never reference anything from the outer query. This makes them non-correlated subqueries, and understanding this category clearly is the natural counterpart to correlated subqueries covered in the previous lesson.

Key Definitions

• Non-correlated subquery: A subquery that does not reference any column from the outer query, allowing it to be executed once, completely independently, before the outer query uses its result.

What You'll Learn

• Define a non-correlated subquery and its independent execution.
• Confirm why a subquery qualifies as non-correlated by checking for outer references.
• Understand the performance advantage of non-correlated subqueries over correlated ones.
• Review several non-correlated subquery examples from earlier in this module.

Detailed Explanation

A non-correlated subquery could, in principle, be run entirely on its own, copy-pasted into a separate query window, and it would produce a meaningful result without needing any information from an outer query. `(SELECT AVG(salary) FROM doctors)` is a perfect example — it doesn't care what table or row it's eventually compared against; it simply computes the average salary in isolation.

This independence is exactly why non-correlated subqueries are generally more efficient than correlated ones: MySQL can evaluate them a single time, cache the result, and reuse it for every row of the outer query, rather than recomputing it repeatedly. When choosing between a correlated and non-correlated approach to solve the same problem, preferring a non-correlated formulation — often by rewriting with a JOIN or a derived table instead of a per-row correlated lookup — is a common and valuable optimization technique in real-world query tuning.

Visual Summary

A single box labeled [Inner Query: SELECT AVG(salary) FROM doctors] with a single arrow labeled 'Executes ONCE' pointing to a small result box '95250', which then has multiple arrows fanning out to every row of the outer query's result table, all reusing the same cached value.

Quick Reference

Subquery	References Outer Query?	Correlated or Non-Correlated?
(SELECT AVG(salary) FROM doctors)	No	Non-correlated
(SELECT patient_id FROM appointments WHERE status='Completed')	No	Non-correlated
(SELECT MAX(d2.salary) FROM doctors d2 WHERE d2.department_id = d1.department_id)	Yes, references d1	Correlated

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

-- Non-correlated subquery: no reference to any outer query column
SELECT doctor_name, salary
FROM doctors
WHERE salary > (SELECT AVG(salary) FROM doctors);

-- Also non-correlated: standalone list of patient IDs
SELECT patient_name
FROM patients
WHERE patient_id IN (
  SELECT patient_id FROM appointments WHERE status = 'Completed'
);

Both subqueries here stand completely on their own — neither references doctors or patients from an outer alias. You could run `SELECT AVG(salary) FROM doctors` by itself and get a meaningful answer without any outer context, confirming it's non-correlated. MySQL computes each of these subqueries exactly once, then applies the result to every row of the respective outer query.

Real-World Examples

• Most simple reporting filters — 'show items above the average price,' 'show employees in any active department' — are naturally non-correlated and represent the majority of real-world subquery usage.
• Business intelligence tools generate many non-correlated subqueries automatically when translating simple filter conditions from a visual query builder.
• Database performance audits often specifically look for opportunities to rewrite correlated subqueries as non-correlated ones or JOINs for better efficiency.

Common Mistakes to Avoid

• Assuming all subqueries are correlated by default, when most simple filtering subqueries are actually non-correlated.
• Not recognizing when an unnecessarily correlated subquery could be rewritten as a more efficient non-correlated one or a JOIN.
• Overlooking the performance benefit of preferring non-correlated formulations where the business logic allows it.

Interview Questions

Q1. What is a non-correlated subquery?

A non-correlated subquery is a subquery that does not reference any column from the outer query, meaning it can be executed independently and only needs to run once, regardless of how many rows the outer query processes.

Q2. Why are non-correlated subqueries generally faster than correlated subqueries?

Because a non-correlated subquery has no dependency on the outer row, MySQL can compute it a single time and reuse the cached result for every row of the outer query, avoiding the repeated re-execution that correlated subqueries require.

Q3. How can you quickly determine if a subquery is correlated or non-correlated?

Check whether the subquery references any column belonging to a table or alias from the outer query. If it does, it's correlated; if it can run entirely on its own without any outer context, it's non-correlated.

Practice MCQs

1. What defines a non-correlated subquery?

1. It must use an aggregate function
2. It does not reference any column from the outer query
3. It must be placed in the WHERE clause
4. It always returns multiple rows

Answer: B. It does not reference any column from the outer query

Explanation: A non-correlated subquery is independent of the outer query, referencing none of its columns, which allows it to execute just once.

2. How many times does a non-correlated subquery typically execute?

1. Once, regardless of outer row count
2. Once per outer row
3. Twice always
4. Zero times unless forced

Answer: A. Once, regardless of outer row count

Explanation: Since it has no dependency on the outer query, a non-correlated subquery is evaluated a single time and its result is reused throughout the outer query's execution.

Quick Revision Points

• A non-correlated subquery has zero references to the outer query and executes exactly once.
• Most basic subqueries covered earlier in this module (AVG filters, IN checks) are non-correlated.
• Non-correlated subqueries are generally more performant than correlated subqueries on large tables.

Conclusion

• A non-correlated subquery is fully independent of the outer query and runs only once.
• This independence generally makes non-correlated subqueries more efficient than correlated ones.
• Most everyday filtering subqueries are naturally non-correlated.

A non-correlated subquery has no dependency on the outer query — it references none of its columns and could be run entirely on its own to produce a meaningful result. Because of this independence, MySQL evaluates a non-correlated subquery exactly once, regardless of how many rows the outer query processes, making it generally more efficient than a correlated subquery, which must re-execute per row. Most of the everyday subqueries covered earlier in this module, such as AVG-based filters and IN membership checks, fall into this non-correlated category.