Antijoin optimization sample with nullable table.sql

Antijoin optimization sample with nullable table.sql

--------------------------------------------
-- Definition
--------------------------------------------
mysql> show create table nulltest\G
*************************** 1. row ***************************
       Table: nulltest
Create Table: CREATE TABLE `nulltest` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> select * from nulltest;
+------+
| c1   |
+------+
| NULL |
|    0 |
|    1 |
|    2 |
+------+
4 rows in set (0.00 sec)

--------------------------------------------
-- NOT IN(subquery)
--------------------------------------------
mysql> select * from nulltest where c1 not in (select * from nulltest);
Empty set (0.00 sec)

mysql> explain format=tree select * from nulltest where c1 not in (select * from nulltest)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: <in_optimizer>(nulltest.c1,nulltest.c1 in (select #2) is false)  (cost=0.65 rows=4)
    -> Table scan on nulltest  (cost=0.65 rows=4)
    -> Select #2 (subquery in condition; run only once)
        -> Table scan on nulltest  (cost=0.65 rows=4)

1 row in set (0.00 sec)

--------------------------------------------
-- IN(subquery) IS NOT TRUE
--------------------------------------------
mysql> select * from nulltest where c1 in (select * from nulltest) is not true;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> explain format=tree select * from nulltest where c1 in (select * from nulltest) is not true\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>

1 row in set (0.00 sec)



--------------------------------------------
-- Add index
--------------------------------------------
mysql> alter table nulltest add index idx(c1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


--------------------------------------------
-- NOT IN(subquery)
--------------------------------------------
mysql> explain format=tree select * from nulltest where c1 not in (select * from nulltest)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: <in_optimizer>(nulltest.c1,<exists>(select #2) is false)  (cost=0.65 rows=4)
    -> Index scan on nulltest using idx  (cost=0.65 rows=4)
    -> Select #2 (subquery in condition; dependent)
        <not executable by iterator executor>

1 row in set (0.00 sec)


--------------------------------------------
-- IN(subquery) IS NOT TRUE
--------------------------------------------
mysql> explain format=tree select * from nulltest where c1 in (select * from nulltest) is not true\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop anti-join  (cost=2.05 rows=4)
    -> Index scan on nulltest using idx  (cost=0.65 rows=4)
    -> Index lookup on nulltest using idx (c1=nulltest.c1)  (cost=0.28 rows=1)

1 row in set (0.00 sec)