############# Test case I've downloaded and kept it at support's test boxes, can be shared if required. ############# 8.1.0 -- build cat docs/INFO_SRC commit: 7d3b61ea23a44d7dbd34dfdcb0c25ffb20699be8 date: 2023-06-21 09:39:09 +0200 build-date: 2023-06-21 07:53:44 +0000 short: 7d3b61ea23a branch: mysql-8.1.0-release MySQL source 8.1.0 -- start up rm -rf 112362/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/112362 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/112362 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/112362/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.1.0 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) bin/mysql -uroot -S /tmp/mysql.sock test < ../mysql-8.0.34/a_b.sql bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.1.0 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> explain SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+---------+----------+-----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+---------+----------+-----------------------------------+ | 1 | SIMPLE | b | NULL | index | ix_value | ix_value | 402 | NULL | 9262721 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | | NULL | eq_ref | | | 403 | test.b.value | 1 | 100.00 | Using where; Not exists; Distinct | | 2 | MATERIALIZED | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+---------+----------+-----------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); Empty set (2.78 sec) mysql> SELECT DISTINCT value FROM b WHERE value NOT IN (SELECT value FROM a); Empty set (2.78 sec) mysql> SELECT DISTINCT value FROM b WHERE value NOT IN (SELECT value FROM a); Empty set (2.78 sec) mysql> explain WITH -> b2 AS (SELECT DISTINCT value FROM b), -> a2 AS (SELECT VALUE value FROM a) -> SELECT value FROM b2 -> WHERE value NOT IN (SELECT value FROM a2); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+-------+----------+--------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 10001 | 100.00 | NULL | | 1 | PRIMARY | | NULL | eq_ref | | | 403 | b2.value | 1 | 100.00 | Using where; Not exists | | 3 | MATERIALIZED | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | | 2 | DERIVED | b | NULL | range | ix_value | ix_value | 402 | NULL | 10001 | 100.00 | Using index for group-by | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+-------+----------+--------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> WITH -> b2 AS (SELECT DISTINCT value FROM b), -> a2 AS (SELECT VALUE value FROM a) -> SELECT value FROM b2 -> WHERE value NOT IN (SELECT value FROM a2); Empty set (0.07 sec) ############# 8.0.34 -- build cat docs/INFO_SRC commit: 5a8b5ea7251bc6fec043a69b434d34366c6899ec date: 2023-06-22 09:38:31 +0200 build-date: 2023-06-22 11:09:16 +0000 short: 5a8b5ea7251 branch: mysql-8.0.34-release MySQL source 8.0.34 -- start up rm -rf 112362/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/112362 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/112362 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/112362/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.34 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; source a_b.sql; Database changed Query OK, 0 rows affected (0.00 sec) . . Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+---------+----------+-----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+---------+----------+-----------------------------------+ | 1 | SIMPLE | b | NULL | index | ix_value | ix_value | 402 | NULL | 9109894 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | | NULL | eq_ref | | | 403 | test.b.value | 1 | 100.00 | Using where; Not exists; Distinct | | 2 | MATERIALIZED | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+---------+----------+-----------------------------------+ 3 rows in set, 1 warning (0.01 sec) mysql> SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); Empty set (3.37 sec) mysql> explain WITH -> b2 AS (SELECT DISTINCT value FROM b), -> a2 AS (SELECT VALUE value FROM a) -> SELECT value FROM b2 -> WHERE value NOT IN (SELECT value FROM a2); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+-------+----------+--------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 10001 | 100.00 | NULL | | 1 | PRIMARY | | NULL | eq_ref | | | 403 | b2.value | 1 | 100.00 | Using where; Not exists | | 3 | MATERIALIZED | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | | 2 | DERIVED | b | NULL | range | ix_value | ix_value | 402 | NULL | 10001 | 100.00 | Using index for group-by | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------+-------+----------+--------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> WITH -> b2 AS (SELECT DISTINCT value FROM b), -> a2 AS (SELECT VALUE value FROM a) -> SELECT value FROM b2 -> WHERE value NOT IN (SELECT value FROM a2); Empty set (0.08 sec) mysql> ############# 8.0.11 - looks like fine -- build used cat docs/INFO_SRC commit: 4725959caed23a8f1626f5808356dd458f730e1b date: 2018-04-08 08:17:02 +0200 build-date: 2018-04-08 08:45:05 +0200 short: 4725959 branch: mysql-8.0.11-release MySQL source 8.0.11 -- start up rm -rf 112362/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/112362 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/112362 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/112362/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.05 sec) mysql> \q Bye bin/mysql -uroot -S /tmp/mysql.sock test < ../mysql-8.0.34/a_b.sql bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> explain SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+---------------------------------------+ | 1 | PRIMARY | b | NULL | range | ix_value | ix_value | 402 | NULL | 10001 | 100.00 | Using where; Using index for group-by | | 2 | SUBQUERY | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec) mysql> SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); Empty set (0.08 sec) mysql> explain WITH -> b2 AS (SELECT DISTINCT value FROM b), -> a2 AS (SELECT VALUE value FROM a) -> SELECT value FROM b2 -> WHERE value NOT IN (SELECT value FROM a2); +----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+-------+----------+--------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 10001 | 100.00 | Using where | | 3 | SUBQUERY | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | | 2 | DERIVED | b | NULL | range | ix_value | ix_value | 402 | NULL | 10001 | 100.00 | Using index for group-by | +----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+-------+----------+--------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> WITH -> b2 AS (SELECT DISTINCT value FROM b), -> a2 AS (SELECT VALUE value FROM a) -> SELECT value FROM b2 -> WHERE value NOT IN (SELECT value FROM a2); Empty set (0.08 sec) ############# 5.7.42 - build used cat docs/INFO_SRC commit: 4346a1d04fc6ef8683bd16e5bc35ff6a7993b493 date: 2023-06-21 14:58:21 +0200 build-date: 2023-06-21 13:02:22 +0000 short: 4346a1d04fc branch: mysql-5.7.43-release MySQL source 5.7.43 -- start up rm -rf 112362/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/112362 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/112362 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/112362/log.err --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.43 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> \q Bye bin/mysql -uroot -S /tmp/mysql.sock test < ../mysql-8.0.34/a_b.sql bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.43 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> explain SELECT DISTINCT value FROM b -> WHERE value NOT IN (SELECT value FROM a); +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+---------------------------------------+ | 1 | PRIMARY | b | NULL | range | ix_value | ix_value | 402 | NULL | 9501 | 100.00 | Using where; Using index for group-by | | 2 | SUBQUERY | a | NULL | index | ix_value_unique | ix_value_unique | 402 | NULL | 10000 | 100.00 | Using index | +----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+-------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> SELECT DISTINCT value FROM b WHERE value NOT IN (SELECT value FROM a); Empty set (0.08 sec) mysql> SELECT DISTINCT value FROM b WHERE value NOT IN (SELECT value FROM a); Empty set (0.07 sec) ############# To sum-up In MySQL 5.7.43 this returns "Empty set (0.07 sec)" In MySQL 8.0.11 this returns "Empty set (0.08 sec)" In MySQL 8.0.34 this returns "Empty set (3.37 sec)" In MySQL 8.1.0 this returns "Empty set (2.78 sec)"