Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › SQL

Subqueries

Why does NOT IN return no rows at all?

A subquery is another SELECT placed inside a SELECT statement, in parentheses. There are several kinds: one that returns a single value, one that returns a list (IN), one that only checks whether a row exists (EXISTS), and one that is run again for every outer row (a correlated subquery). This lab shows the result of each kind, and how NOT IN and NOT EXISTS differ when the result of the subquery contains a NULL.

Checked against real execution · The SQL for the 36 combinations was run as is on MySQL 8.4.11, and the error numbers, every row of the results, the select_type in EXPLAIN, and the number of times a correlated subquery was run (EXPLAIN ANALYZE) were checked against the results

Concepts in this lab

Subquery
A SELECT statement that is contained inside another SQL statement. It can be used in the SELECT clause to compute a single value, in the FROM clause like a temporary table, and in the WHERE clause as the thing a condition compares against. A correlated subquery, which refers to columns of the outer query, can run once for each outer row, so watch its performance.
Scalar subquery
A subquery that returns a single value (one row and one column). You can use it where a value is expected, as in WHERE id = (SELECT ...), and also where a column goes in the SELECT list. If it returns two or more rows, MySQL rejects the statement with error 1242 (Subquery returns more than 1 row). If it returns 0 rows, the result is NULL, not an error, so a comparison is never true and the result quietly becomes empty. Even if the current data gives one row, there is no guarantee that it always will, so write the condition so that only one row comes out, or use IN.
Correlated subquery
A subquery that uses a column of the outer query. The value of that column differs for each outer row, so the subquery cannot be run by itself. It is run again with that value every time an outer row is fixed. In MySQL EXPLAIN, its select_type is shown as DEPENDENT SUBQUERY. When there are many outer rows, the subquery is also run that many times and can be slow. However, the optimizer sometimes rewrites an EXISTS or IN subquery as a join, so a statement that has the shape of a correlated subquery is not necessarily run once for every row.
IN and EXISTS
Both are conditions that filter the outer rows by the result of a subquery. IN compares an outer value with the list that the subquery returns, and EXISTS only looks at whether the subquery returns even one row. Their negations, NOT IN and NOT EXISTS, handle NULL differently. If the list has even one NULL, NOT IN cannot be true for any row, so the result is 0 rows. NOT EXISTS only asks whether a row exists, so a NULL does not affect it. So for "finding what is missing", use NOT EXISTS, or filter out the NULLs in the subquery of NOT IN.
NULL and three-valued logic
NULL stands for "no value" or "unknown", not for 0 or an empty string. A comparison with NULL (=, <>, <, and so on) is neither true nor false but "unknown" (UNKNOWN), so a condition in SQL has three values: true, false, and unknown. WHERE and HAVING keep only the rows for which the condition is true, so a row for which it is unknown is removed, just like a row for which it is false. NOT applied to unknown is still unknown. To check for NULL, use IS NULL and IS NOT NULL, not = NULL.

Try these

Running with the default settings

  1. CREATE TABLE, INSERT: There are 4 members and 5 orders. Alice placed 2 orders, Bob and Carol placed 1 each, and Dave has no orders. Order 105 is a guest order, so its member_id is NULL.
  2. (SELECT member_id FROM orders): The subquery in parentheses does not use any value from the outer query, so it can be run by itself. It returns the list 1, 1, 2, 3, NULL, which is the member_id of each order. The list contains a NULL because of the guest order.
  3. Alice (id 1) NOT IN (1, 1, 2, 3, NULL): The list contains 1, so IN is true and NOT IN is false. The row is removed.
  4. Bob (id 2) NOT IN (1, 1, 2, 3, NULL): The list contains 2, so IN is true and NOT IN is false. The row is removed.
  5. Carol (id 3) NOT IN (1, 1, 2, 3, NULL): The list contains 3, so IN is true and NOT IN is false. The row is removed.
  6. Dave (id 4) NOT IN (1, 1, 2, 3, NULL): NOT IN means "different from every value in the list". The value 4 is different from 1, 2, and 3, but it is not known whether it is different from NULL. If even one comparison is unknown, the whole condition becomes "unknown", and WHERE keeps only the rows for which it is true, so Dave, who has no orders, is removed too.
  7. EXPLAIN: The select_type of the subquery row is SUBQUERY. It does not depend on the outer row, so the list is built only once, and each outer row is compared with that list.

Result of this run

Code

CREATE TABLE member (
  id   INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);
CREATE TABLE orders (
  id        INT PRIMARY KEY,
  member_id INT,
  item      VARCHAR(30) NOT NULL
);

INSERT INTO member (id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Carol'),
  (4, 'Dave');
INSERT INTO orders (id, member_id, item) VALUES
  (101, 1, 'keyboard'),
  (102, 1, 'mouse'),
  (103, 2, 'keyboard'),
  (104, 3, 'monitor'),
  (105, NULL, 'chair');

SELECT name
  FROM member
 WHERE id NOT IN (SELECT member_id
                    FROM orders)
 ORDER BY id;

Assumptions of this model

This is a small example with 4 members and 4 or 5 orders. For all 36 combinations (6 shapes × with or without the NULL order × 3 items to look for; the item to look for changes the statement only in the "single value" shape), the SQL shown on the screen was run as is on a real MySQL 8.4.11, and the error numbers and messages, every row of the result, the result of running only the subquery in parentheses on its own, and the select_type in EXPLAIN were checked against the model and matched. That a correlated subquery is run for every outer row was confirmed by the loops of the subquery part in EXPLAIN ANALYZE being equal to the number of outer rows (4), and that the subquery of NOT IN is run only once was confirmed by "run only once" being shown in EXPLAIN ANALYZE. The steps on the screen are a logical explanation that follows the meaning of the condition, and they may differ from how MySQL actually runs the statement. The MySQL 8.4 optimizer ran IN and EXISTS subqueries as semijoins and NOT EXISTS as an antijoin, which is why select_type came out as SIMPLE. This rewriting depends on the version, the settings (optimizer_switch), and the amount of data, and the EXPLAIN of the same statement sometimes also showed a MATERIALIZED row. When the members and orders were increased to 1,000 rows each, the select_type classification was the same. Versions before MySQL 8.0.16 do not rewrite EXISTS subqueries as semijoins, so they may show DEPENDENT SUBQUERY (this part follows the MySQL documentation, not execution). NOT IN came out as SUBQUERY instead of being rewritten as a join because member_id is a column that allows NULL. For a table where member_id was declared NOT NULL, the same NOT IN statement came out as SIMPLE (antijoin), which was confirmed by running it. Apart from select_type, the result rows and errors follow the definition of SQL, so they are the same in other databases. Among the contents of the summary, "if you use IN instead of =, Alice and Bob come out", "LEFT JOIN and GROUP BY give the same result", and "if you add IS NOT NULL to the subquery, Dave comes out" were also run separately on the same MySQL and confirmed. That a subquery in the place of a single value becomes NULL when it returns 0 rows agrees between the explanation in the MySQL documentation and the execution result (0 rows, no error).

Previous lab: JOIN and GROUP BY · Next lab: Window functions

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these