Sub Queries and NOT IN Trap in MySQL

A subquery is simply a SELECT statement written inside another SQL statement.

1. Outer query = the main query you run.
2. Inner query = the SELECT written inside it.
3. SQL evaluates the inner query first, then uses its result to finish the outer query.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name    VARCHAR(50) NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product    VARCHAR(50) NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  user_id    INT NOT NULL,
  product_id INT NULL,
  amount     DECIMAL(10,2) NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(user_id),
  CONSTRAINT fk_orders_product
    FOREIGN KEY (product_id)
    REFERENCES products(product_id)
) ENGINE=InnoDB;

INSERT INTO users (user_id, name) VALUES
(1, 'Ayaan'),
(2, 'Sneha'),
(3, 'Rohit'),
(4, 'Kavya'),
(5, 'Zoya'),
(6, 'Arjun'),
(7, ''),
(8, NULL);

INSERT INTO products (product_id, product) VALUES
(10, 'Tablet'),
(11, 'Headphones'),
(12, 'Keyboard');

INSERT INTO orders (order_id, user_id, product_id, amount) VALUES
(101, 1, 10, 1200.00),
(102, 1, 11,  300.00),
(103, 1, 12,  150.00),
(104, 3, 10, 1100.00),
(105, 3, 11,  350.00),
(106, 4, 11,  400.00),
(107, 1, 11,  250.00),
(108, 1, 12,  NULL),
(109, 3, 11,  349.00),
(110, 3, 11,   50.00),
(111, 4, NULL,  50.00),
(112, 4, 11,   10.00);

So, here is a basic example on Subquery how to write it and read it

Question: Fetch each order along with the user’s name

SELECT
  o.order_id,
    (SELECT u.name 
     FROM users u 
     WHERE u.user_id = o.user_id) as name
From orders o

1) Outer query picks orders

The outer query reads each row from the orders table (o) and outputs:

• o.order_id

So it produces one output row per order.

2) Subquery fetches user name for that order

For every order row, this subquery runs:

SELECT u.name
FROM users u
WHERE u.user_id = o.user_id

• It uses the current order’s o.user_id.
• It finds the matching row in users.
• It returns the name of that user.

Question 2: Find users who bought all products

SELECT
  u.user_id,
  u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM products p
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
      AND o.product_id = p.product_id
  )
);

For each user, the query checks every product in products. If the user is missing even one product (no order for it), the user is removed. Only users who have bought every product remain.

Now you may be thinking is it the only way to write down query?
But No, this query can be written down in many forms

1. Standard Inner Join (Recommended)

This is the most common, readable, and generally highest-performing way to write this query. It fetches orders that have a matching user.

SELECT 
    o.order_id, 
    u.name 
FROM orders o
JOIN users u ON o.user_id = u.user_id;

2. Left Join (Preserves all orders)

If your original query could return NULL for the name when an order has no matching user (like a LEFT JOIN), use this to ensure all orders are still listed even if the user record is missing.

SELECT 
    o.order_id, 
    u.name 
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;

3. Using the USING Clause

If both tables share the exact same column name (user_id), you can shorten the ON condition using USING.

SELECT 
    o.order_id, 
    u.name 
FROM orders o
JOIN users u USING (user_id);

4. Implicit Join (Legacy Syntax)

An older, comma-separated syntax (though explicit JOIN is modern industry standard).

SELECT 
    o.order_id, 
    u.name 
FROM orders o, users u
WHERE o.user_id = u.user_id;

The NOT IN Trap

So, I have a question what will this query return based on below table

SELECT name
FROM employees
WHERE salary NOT IN (
 SELECT salary
 FROM employees
 WHERE dept_id = 10
);

Table: To query NOT IN

If you says: Neha, Pooja and Karan then you are wrong. So, let’s understand how NOT IN actually work

Get Sahil Phougat’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The output of this query depends entirely on whether any employee in dept_id = 10 has a NULL salary.

Because of how SQL handles NULL values with NOT IN, this query can behave in two completely different ways:

Scenario A: If there are NO NULL salaries in Department 10

If all employees in dept_id = 10 have valid, non-null salary values (e.g., 50000, 60000), the query will successfully return:

• The names of all employees whose salary does NOT match any salary in department 10.

Scenario B: If even ONE salary in Department 10 is NULL (The NOT IN Trap)

If the subquery returns even a single NULL value, the query will return 0 rows (an empty result set), no matter what.

Why does this happen?

When SQL evaluates NOT IN, it translates to a series of AND conditions with != (not equal):

-- Conceptual evaluation for an employee with a salary of 70000, 
-- if department 10 has salaries (50000, NULL):
WHERE salary != 50000 AND salary != NULL

• 70000 != 50000 evaluates to TRUE.
• 70000 != NULL evaluates to UNKNOWN (because comparisons with NULL in SQL always result in unknown/null).
• TRUE AND UNKNOWN evaluates to UNKNOWN.

Since a WHERE clause only keeps rows where the condition evaluates to strictly TRUE, an UNKNOWN result causes the row to be discarded. One single NULL breaks the entire query for every row.

How to Write It Safely

To avoid the NULL trap entirely, it is much safer to use NOT EXISTS (which you looked at previously) or a LEFT JOIN:

SELECT e1.name
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e2 
    WHERE e2.dept_id = 10 
      AND e2.salary = e1.salary
);

NOT EXISTS handles NULL values safely because it checks for the existence of rows rather than performing a list comparison.

Performance and Safety

In many database systems, EXISTS is faster than IN. Why? Because IN requires the database to build a complete list of all possible values from the inner query first. EXISTS just needs to find one single match and can stop looking immediately. This “short-circuit” behavior makes it very efficient.

Even more importantly, NOT EXISTS is much safer than NOT IN. If a subquery used with NOT IN returns even one NULL value, the entire query can fail or return zero rows. NOT EXISTS does not have this problem and handles NULLs logically.

The Hidden Cost

Correlated subqueries are incredibly flexible, but they come at a high performance cost. Because they run in a loop (once for every row of the outer table), they can be extremely slow on large datasets.

• If your outer table has 1,000 rows, the inner query runs 1,000 times.
• If your outer table has 1,000,000 rows, the inner query runs 1,000,000 times.

In many modern databases, the optimizer is smart enough to rewrite these as a faster JOIN. However, in older systems or very complex queries, they can become a major bottleneck. Always test your query performance.

Common Mistakes to Avoid

• Selecting Columns in EXISTS: Beginners often write SELECT * or SELECT column inside an EXISTS clause. This is a waste of resources. The database only cares if a row is returned, not what is in it. Always use SELECT 1 for better performance.
• The NOT IN Trap: As mentioned, if the subquery for NOT IN contains a NULL, your query will break. This is the number one reason developers prefer NOT EXISTS for finding missing relationships.
• Forgetting the Correlation: The subquery inside EXISTS must be “Correlated,” meaning it must link back to the outer query (e.g., WHERE o.user_id = u.user_id). If you forget this, the subquery will check the same thing for every row, giving you the wrong answer.