In the following test case the two subqueries should produce the same
empty result, however, the first one produces all rows of the outer table,
while the second query correctly doesn't return any rows.

The only difference in the two queries is LEFT vs INNER join in the
subquery, however the two subqueries produce the same result if
run separately. The only difference in the results of the subqueries
is the nullability of the result column. This is shown in the two tables
t2_inr, and t2_outr below.

drop table if exists t1;

CREATE TABLE t1 (
pk INT PRIMARY KEY,
int_key INT,
varchar_key VARCHAR(5) UNIQUE
);
INSERT INTO t1 VALUES (9, 7,NULL), (10,8,'p');

– Wrong result - should be empty
EXPLAIN
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');

– Correct empty result
EXPLAIN
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');

– Verify that the result from the two subqueries is the same.

SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';

SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';

create table t2_inr as
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';

create table t2_outr as
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';

explain t2_inr;
explain t2_outr;