create table cs1 (a int)engine=columnstore;
|
insert into cs1 values (1), (2), (3), (4), (null);
|
|
create table cs2 (b int, c int)engine=columnstore;
|
insert into cs2 values (1, 100), (1, 101), (2, 200),
|
(3, 300), (3, 301), (3, 302), (null, null);
|
|
MariaDB [test]> select * from cs1 where hex(a*10) not in (select hex(b*10) from cs2);
|
+------+
|
| a |
|
+------+
|
| 4 |
|
+------+
|
1 row in set (0.073 sec)
|
|
MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3)));
|
+------+
|
| a |
|
+------+
|
| 2 |
|
| 3 |
|
| 4 |
|
| NULL |
|
+------+
|
4 rows in set (0.029 sec)
|
|
MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a not in (2,3)));
|
Empty set (0.030 sec)
|
|
MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b=3);
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| NULL |
|
+------+
|
4 rows in set (0.017 sec)
|
|
MariaDB [test]> select * from cs1 where a not in (select count(*) over (partition by b) from cs2 where b is null);
|
+------+
|
| a |
|
+------+
|
| 2 |
|
| 3 |
|
| 4 |
|
| NULL |
|
+------+
|
4 rows in set (0.017 sec)
|
|
MariaDB [test]> select * from cs1 where a not in (select c from cs2 where b is not null);
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
| NULL |
|
+------+
|
5 rows in set (0.019 sec)
|
|
MariaDB [test]> select * from cs1 where (a+a) not in (select (b+b) from cs2 where b is not null);
|
+------+
|
| a |
|
+------+
|
| 4 |
|
| NULL |
|
+------+
|
2 rows in set (0.024 sec)
|
|
MariaDB [test]> select * from cs1 where (a+1) not in (select b from cs2 where b is not null);
|
+------+
|
| a |
|
+------+
|
| 3 |
|
| 4 |
|
| NULL |
|
+------+
|
3 rows in set (0.018 sec)
|
|
MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3) and a is not null) and b is not null);
|
+------+
|
| a |
|
+------+
|
| 2 |
|
| 3 |
|
| 4 |
|
| NULL |
|
+------+
|
4 rows in set (0.034 sec)
|