github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/subquery/subquery-with-in.result

SELECT 1 IN (SELECT 1);
1 IN (SELECT 1)
true
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
1
1
SELECT 1 FROM (SELECT 1 as a) b WHERE 1 not IN (SELECT (SELECT a));
1
SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);
id
1
SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);
invalid input: subquery returns more than 1 column
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
SQL parser error: No tables used
SELECT ((0,1) NOT IN (SELECT NULL,1)) IS NULL;
((0,1) NOT IN (SELECT NULL,1)) IS NULL
true
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
create table t4 (a int not null, b int not null);
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
insert into t2 values (100, 5);
select * from t3 where a in (select b from t2);
a
select * from t3 where a in (select b from t2 where b > 7);
a
select * from t3 where a not in (select b from t2);
a
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
false
select * from t3 where a in (select a,b from t2);
invalid input: subquery returns more than 1 column
select * from t3 where a in (select * from t2);
invalid input: subquery returns more than 1 column
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (s1 char(5), index s1(s1));
create table t2 (s1 char(5), index s1(s1));
insert into t1 values ('a1'),('a2'),('a3');
insert into t2 values ('a1'),('a2');
select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
s1	s1 NOT IN (SELECT s1 FROM t2)
a1	false
a2	false
a3	true
select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
s1	s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')
a1	false
a2	true
a3	true
drop table if exists t1;
drop table if exists t2;
create table t1(val varchar(10));
insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');
select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');
count(*)
0
DROP TABLE IF EXISTS t1;
create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
select * from t1 where id not in (select id from t1 where id < 8);
id	text
8	text8
9	text9
10	text10
11	text11
12	text12
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int, b int);
CREATE TABLE t3 (b int NOT NULL);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (1,10), (3,30);
select * from t1 where t1.a in (SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.a=t1.a);
a
1
2
3
4
SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t3.b IS NOT NULL OR t2.a > 10;
a	b	b
SELECT * FROM t1 WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t3.b IS NOT NULL OR t2.a > 10);
a
1
2
3
4
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
CREATE TABLE t1(a int, INDEX (a));
INSERT INTO t1 VALUES (1), (3), (5), (7);
INSERT INTO t1 VALUES (NULL);
CREATE TABLE t2(a int);
INSERT INTO t2 VALUES (1),(2),(3);
SELECT a, a IN (SELECT a FROM t1) FROM t2;
a	a IN (SELECT a FROM t1)
1	true
2	null
3	true
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
CREATE table t1 ( c1 int );
INSERT INTO t1 VALUES ( 1 );
INSERT INTO t1 VALUES ( 2 );
INSERT INTO t1 VALUES ( 3 );
CREATE TABLE t2 ( c2 int );
INSERT INTO t2 VALUES ( 1 );
INSERT INTO t2 VALUES ( 4 );
INSERT INTO t2 VALUES ( 5 );
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1);
c1	c2
1	1
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
c1	c2
1	1
drop table if exists t1;
drop table if exists t2;
DROP TABLE IF EXISTS c;
CREATE TABLE `c` (
`int_nokey` int(11) NOT NULL,
`int_key` int(11) NOT NULL
);
INSERT INTO `c` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4),
(1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7),
(5,2), (1,8), (7,0), (0,9), (9,5);
SELECT * FROM c WHERE `int_key` IN (SELECT `int_nokey`);
int_nokey	int_key
9	9
0	0
5	5
0	0
DROP TABLE IF EXISTS c;
drop table if exists t1;
drop table if exists t2;
CREATE TABLE t1(c INT);
CREATE TABLE t2(a INT, b INT);
INSERT INTO t2 VALUES (1, 10), (2, NULL);
INSERT INTO t1 VALUES (1), (3);
SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10);
a	b
drop table if exists t1;
drop table if exists t2;
CREATE TABLE t1 (
a1 char(8) DEFAULT NULL,
a2 char(8) DEFAULT NULL
);
CREATE TABLE t2 (
b1 char(8) DEFAULT NULL,
b2 char(8) DEFAULT NULL
);
INSERT INTO t1 VALUES
('1 - 00', '2 - 00'),('1 - 01', '2 - 01'),('1 - 02', '2 - 02');
INSERT INTO t2 VALUES
('1 - 01', '2 - 01'),('1 - 01', '2 - 01'),
('1 - 02', '2 - 02'),('1 - 02', '2 - 02'),('1 - 03', '2 - 03');
SELECT * FROM t2 WHERE b1 NOT IN ('1 - 00', '1 - 01', '1 - 02');
b1	b2
1 - 03	2 - 03
drop table if exists t1;
drop table if exists t2;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
CREATE TABLE `t1` (
`numeropost` int(8) unsigned NOT NULL,
`maxnumrep` int(10) unsigned NOT NULL default 0,
PRIMARY KEY  (`numeropost`)
) ;
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
CREATE TABLE `t2` (
`mot` varchar(30) NOT NULL default '',
`topic` int(8) unsigned NOT NULL default 0,
`dt` date,
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY  (`topic`)
) ;
INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);
mot	topic	dt	pseudo
joce	40143	2002-10-22	joce
joce	43506	2002-10-22	joce
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
mot	topic	dt	pseudo
SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
mot	topic	dt	pseudo
SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
mot	topic	dt	pseudo
joce	40143	2002-10-22	joce
SELECT * from t2 where topic NOT IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
mot	topic	dt	pseudo
joce	43506	2002-10-22	joce
SELECT * FROM t2 WHERE mot IN (SELECT 'joce');
mot	topic	dt	pseudo
joce	40143	2002-10-22	joce
joce	43506	2002-10-22	joce
drop table if exists t1;
drop table if exists t2;
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1),(2);
insert into t2 values (0),(1),(2),(3);
select a from t2 where a in (select a from t1);
a
1
2
select a from t2 having a in (select a from t1);
SQL syntax error: column "t2.a" must appear in the GROUP BY clause or be used in an aggregate function
drop table if exists t1;
drop table if exists t2;
create table t1 (oref int, grp int, ie int) ;
insert into t1 (oref, grp, ie) values(1, 1, 1),(1, 1, 1), (1, 2, NULL),(2, 1, 3),(3, 1, 4),(3, 2, NULL);
create table t2 (oref int, a int);
insert into t2 values(1, 1),(2, 2),(3, 3), (4, NULL),(2, NULL);
create table t3 (a int);
insert into t3 values (NULL), (NULL);
select a, oref, a in (select max(ie) from t1 where oref=t2.oref group by grp) Z from t2;
a	oref	Z
1	1	true
2	2	false
3	3	null
null	4	false
null	2	null
select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
a	oref
1	1
select a, oref, a in (
select max(ie) from t1 where oref=t2.oref group by grp union
select max(ie) from t1 where oref=t2.oref group by grp
) Z from t2;
a	oref	z
1	1	true
2	2	false
3	3	null
null	4	false
null	2	null
select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
a in (select max(ie) from t1 where oref=4 group by grp)
false
false
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (a int, oref int);
insert into t1 values(1, 1),(1, NULL),(2, 3),(2, NULL),(3, NULL);
create table t2 (a int, oref int);
insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
oref	a	Z
1	1	true
2	2	false
3	null	null
4	null	false
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
oref	a
1	1
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
oref	a	Z
0	null	false
0	null	false
0	null	false
0	null	false
drop table if exists t1;
drop table if exists t2;
drop table if exists t1;
drop table if exists t2;
CREATE TABLE t2 (id int(11) default NULL);
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
id
1
SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
'union' will be supported in future version.
SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id
2
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
'union' will be supported in future version.
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
'union' will be supported in future version.
SELECT * FROM t2 WHERE id NOT IN (SELECT 5 UNION SELECT 2);
'union' will be supported in future version.
drop table if exists t1;
drop table if exists t2;
create table t1 (a int);
insert into t1 values (1),(2),(3);
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
true
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
false
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
null
update t1 set a=NULL where a=2;
select 1 IN (SELECT * from t1);
1 IN (SELECT * from t1)
true
select 3 IN (SELECT * from t1);
3 IN (SELECT * from t1)
true
select 10 IN (SELECT * from t1);
10 IN (SELECT * from t1)
null
DROP TABLE IF EXISTS t1;
create table t1 (a varchar(20));
insert into t1 values ('A'),('BC'),('DEF');
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
true
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
false
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
null
update t1 set a=NULL where a='BC';
select 'A' IN (SELECT * from t1);
'A' IN (SELECT * from t1)
true
select 'DEF' IN (SELECT * from t1);
'DEF' IN (SELECT * from t1)
true
select 'XYZS' IN (SELECT * from t1);
'XYZS' IN (SELECT * from t1)
null
DROP TABLE IF EXISTS t1;
create table t1 (a float);
insert into t1 values (1.5),(2.5),(3.5);
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
true
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
false
select NULL IN (SELECT * from t1);
NULL IN (SELECT * from t1)
null
update t1 set a=NULL where a=2.5;
select 1.5 IN (SELECT * from t1);
1.5 IN (SELECT * from t1)
true
select 3.5 IN (SELECT * from t1);
3.5 IN (SELECT * from t1)
true
select 10.5 IN (SELECT * from t1);
10.5 IN (SELECT * from t1)
null
drop table if exists t1;
drop table if exists t2;
CREATE TABLE t1 (a int(11) NOT NULL default 0, PRIMARY KEY  (a));
CREATE TABLE t2 (a int(11) default 0, INDEX (a));
CREATE TABLE t3 (a int(11) default 0);
INSERT INTO t3 VALUES (1),(2),(3);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2),(3);
SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
a	t1.a in (select t2.a from t2)
1	true
2	true
3	true
4	false
SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
a	t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
1	true
2	true
3	true
4	false
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (a int);
insert into t1 values (-1), (-4), (-2), (NULL);
select -10 IN (select a from t1);
-10 IN (select a from t1)
null
DROP TABLE IF EXISTS t1;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
10.5 in (select * from t1 limit 1)
false
select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 46 near " UNION SELECT 1.5);";
select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1);
10.5 in (select * from t1 union select 1.5 limit 1)
false
DROP TABLE IF EXISTS t1;
create table t1 (a int, b real, c varchar(10));
insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
select (1, 1, 'a') IN (select a,b,c from t1);
(1, 1, 'a') IN (select a,b,c from t1)
true
select (1, 2, 'a') IN (select a,b,c from t1);
(1, 2, 'a') IN (select a,b,c from t1)
false
select (1, 1, 'a') IN (select b,a,c from t1);
(1, 1, 'a') IN (select b,a,c from t1)
true
select (1, 1, 'a') IN (select a,b,c from t1 where a is not null);
(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
true
select (1, 2, 'a') IN (select a,b,c from t1 where a is not null);
(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
false
select (1, 1, 'a') IN (select b,a,c from t1 where a is not null);
(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
true
select (1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
true
select (1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
false
select (1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
true
select (1, 1, 'a') IN (select b,a,c from t1 limit 2);
(1, 1, a) in (select b, a, c from t1 limit 2)
true
DROP TABLE IF EXISTS t1;
create table t1 (a integer, b integer);
select (1,(2,2)) in (select * from t1 );
Operand should contain 2 column(s)
select (1,(2,2)) = (select * from t1 );
Operand should contain 2 column(s)
select (select * from t1) = (1,(2,2));
Operand should contain 1 column(s)
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (a1 INT, a2 INT);
CREATE TABLE t2 (b1 INT, b2 INT);
INSERT INTO t1 VALUES (100, 200);
INSERT INTO t1 VALUES (101, 201);
INSERT INTO t2 VALUES (101, 201);
INSERT INTO t2 VALUES (103, 203);
SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL
false
false
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (
pk INTEGER,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_datetime_key DATETIME,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1, 1, 7, '2001-11-04 19:07:55.051133');
CREATE TABLE t2(field1 INT, field2 INT);
SELECT * FROM t2 WHERE (field1, field2) IN (
SELECT MAX(col_datetime_key), col_int_key
FROM t1
WHERE col_int_key > col_int_nokey
GROUP BY col_int_key);
field1	field2
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (a int, b int);
insert into t1 values (0,0), (2,2), (3,3);
create table t2 (a int, b int);
insert into t2 values (1,1), (3,3);
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
a	b	Z
0	0	false
2	2	false
3	3	true
insert into t2 values (NULL,4);
select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
a	b	Z
0	0	false
2	2	false
3	3	true
DROP TABLE IF EXISTS t1;
drop table if exists t2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (11);
SELECT a, (11, 12) = (SELECT a, 22), (11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
a	(11, 12) = (SELECT a, 22)	(11, 12) IN (SELECT a, 22)
1	0	false
2	0	false
11	0	false
SELECT a, (11, 12) = (SELECT a, 12), (11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
a	(11, 12) = (SELECT a, 12)	(11, 12) IN (SELECT a, 12)
1	0	false
2	0	false
11	1	true
SELECT a, (11, 12) = (SELECT a, 22), (11, 12) IN (SELECT a, 22) FROM t1;
a	(11, 12) = (SELECT a, 22)	(11, 12) IN (SELECT a, 22)
1	0	false
2	0	false
11	0	false
SELECT a, (11, 12) = (SELECT a, 12), (11, 12) IN (SELECT a, 12) FROM t1;
a	(11, 12) = (SELECT a, 12)	(11, 12) IN (SELECT a, 12)
1	0	false
2	0	false
11	1	true
SELECT a AS x, (11, 12) = (SELECT MAX(x), 22), (11, 12) IN (SELECT MAX(x), 22) FROM t1;
invalid input: column x does not exist
SELECT a AS x, (11, 12) = (SELECT MAX(x), 12), (11, 12) IN (SELECT MAX(x), 12) FROM t1;
invalid input: column x does not exist
DROP TABLE IF EXISTS t1;
SELECT (1,2) = (SELECT NULL, NULL), (1,2) IN (SELECT NULL, NULL);
(1,2) = (SELECT NULL, NULL)	(1,2) IN (SELECT NULL, NULL)
null	null
SELECT (1,2) = (SELECT   1,  NULL), (1,2) IN (SELECT    1, NULL);
(1,2) = (SELECT   1,  NULL)	(1,2) IN (SELECT    1, NULL)
null	null
SELECT (1,2) = (SELECT NULL,    2), (1,2) IN (SELECT NULL,    2);
(1,2) = (SELECT NULL,    2)	(1,2) IN (SELECT NULL,    2)
null	null
SELECT (1,2) = (SELECT NULL,    1), (1,2) IN (SELECT NULL,    1);
(1,2) = (SELECT NULL,    1)	(1,2) IN (SELECT NULL,    1)
false	false
SELECT (1,2) = (SELECT    1,    1), (1,2) IN (SELECT    1,    1);
(1,2) = (SELECT    1,    1)	(1,2) IN (SELECT    1,    1)
false	false
SELECT (1,2) = (SELECT    1,    2), (1,2) IN (SELECT    1,    2);
(1,2) = (SELECT    1,    2)	(1,2) IN (SELECT    1,    2)
true	true
create table t_out (subcase char(3),a1 char(2), b1 char(2), c1 char(2));
create table t_in  (a2 char(2), b2 char(2), c2 char(2));
insert into t_out values ('A.1','2a', NULL, '2a');
insert into t_out values ('A.3', '2a', NULL, '2a');
insert into t_out values ('A.4', '2a', NULL, 'xx');
insert into t_out values ('B.1', '2a', '2a', '2a');
insert into t_out values ('B.2', '2a', '2a', '2a');
insert into t_out values ('B.3', '3a', 'xx', '3a');
insert into t_out values ('B.4', 'xx', '3a', '3a');
insert into t_in values ('1a', '1a', '1a');
insert into t_in values ('2a', '2a', '2a');
insert into t_in values (NULL, '2a', '2a');
insert into t_in values ('3a', NULL, '3a');
select subcase,
(a1, b1, c1)     IN (select * from t_in where a2 = 'no_match') pred_in,
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
from t_out where subcase = 'A.1';
subcase	pred_in	pred_not_in
A.1	false	true
select subcase,
(a1, b1, c1)     IN (select * from t_in) pred_in,
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
from t_out where subcase = 'A.3';
subcase	pred_in	pred_not_in
A.3	null	null
select subcase,
(a1, b1, c1)     IN (select * from t_in) pred_in,
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
from t_out where subcase = 'A.4';
subcase	pred_in	pred_not_in
A.4	false	true
select subcase,
(a1, b1, c1)     IN (select * from t_in where a2 = 'no_match') pred_in,
(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in
from t_out where subcase = 'B.1';
subcase	pred_in	pred_not_in
B.1	false	true
select subcase,
(a1, b1, c1)     IN (select * from t_in) pred_in,
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
from t_out where subcase = 'B.2';
subcase	pred_in	pred_not_in
B.2	true	false
select subcase,
(a1, b1, c1)     IN (select * from t_in) pred_in,
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
from t_out where subcase = 'B.3';
subcase	pred_in	pred_not_in
B.3	null	null
select subcase,
(a1, b1, c1)     IN (select * from t_in) pred_in,
(a1, b1, c1) NOT IN (select * from t_in) pred_not_in
from t_out where subcase = 'B.4';
subcase	pred_in	pred_not_in
B.4	false	true
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'A.1' and
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'A.1' and
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
pred_not_in
T
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'A.1' and
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
not_pred_in
T
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'A.3' and
(a1, b1, c1) IN (select * from t_in);
pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'A.3' and
(a1, b1, c1) NOT IN (select * from t_in);
pred_not_in
F
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'A.3' and
NOT((a1, b1, c1) IN (select * from t_in));
not_pred_in
F
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
where subcase = 'A.3' and
((a1, b1, c1) IN (select * from t_in)) is NULL and
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
pred_in
N
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'A.4' and
(a1, b1, c1) IN (select * from t_in);
pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'A.4' and
(a1, b1, c1) NOT IN (select * from t_in);
pred_not_in
T
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'A.4' and
NOT((a1, b1, c1) IN (select * from t_in));
not_pred_in
T
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'B.1' and
(a1, b1, c1) IN (select * from t_in where a1 = 'no_match');
pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'B.1' and
(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match');
pred_not_in
T
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'B.1' and
NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match'));
not_pred_in
T
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'B.2' and
(a1, b1, c1) IN (select * from t_in);
pred_in
T
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'B.2' and
(a1, b1, c1) NOT IN (select * from t_in);
pred_not_in
F
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'B.2' and
NOT((a1, b1, c1) IN (select * from t_in));
not_pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'B.3' and
(a1, b1, c1) IN (select * from t_in);
pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'B.3' and
(a1, b1, c1) NOT IN (select * from t_in);
pred_not_in
F
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'B.3' and
NOT((a1, b1, c1) IN (select * from t_in));
not_pred_in
F
select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out
where subcase = 'B.3' and
((a1, b1, c1) IN (select * from t_in)) is NULL and
((a1, b1, c1) NOT IN (select * from t_in)) is NULL;
pred_in
N
select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out
where subcase = 'B.4' and
(a1, b1, c1) IN (select * from t_in);
pred_in
F
select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out
where subcase = 'B.4' and
(a1, b1, c1) NOT IN (select * from t_in);
pred_not_in
T
select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out
where subcase = 'B.4' and
NOT((a1, b1, c1) IN (select * from t_in));
not_pred_in
T
DROP TABLE IF EXISTS t_out;
DROP TABLE IF EXISTS t_in;
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (a int);
create table t2 (b int);
insert into t1 values (1),(2);
insert into t2 values (1);
select a from t1 where a in (select a from t1 where a in (select b from t2));
a
1
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (a int, b int);
create table t2 (a int, b int);
select * from t1 where (a,b) in (select a,b from t2);
a	b
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (a int);
insert into t1 values (1), (2), (3);
SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
1
1
1
1
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2( a INT, b INT );
SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2);
a	b
SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
a
1
SELECT * FROM t1 WHERE a IN    ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
a
1
SELECT * FROM t1 WHERE a IN    ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
a
1
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
drop table if exists t5;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int(11) default null,
b int(11) default null
);
insert into t1 select A.a+10*(B.a+10*C.a),A.a+10*(B.a+10*C.a) from t0 A, t0 B, t0 C;
create table t2 (a int(11) default null);
insert into t2 values (0),(1);
create table t3 (a int(11) default null);
insert into t3 values (0),(1);
create table t4 (a int(11) default null);
insert into t4 values (0),(1);
create table t5 (a int(11) default null);
insert into t5 values (0),(1),(0),(1);
select * from t2, t3
where
t2.a < 10 and
t3.a+1 = 2 and
t3.a in (select t1.b from t1
where t1.a+1=t1.a+1 and
t1.a < (select t4.a+10
from t4, t5 limit 2));
internal error: scalar subquery returns more than 1 row
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
drop table if exists t5;
CREATE TABLE t1 (i1 int DEFAULT NULL,i2 int DEFAULT NULL) ;
INSERT INTO t1 VALUES (1,    NULL);
INSERT INTO t1 VALUES (2,    3);
INSERT INTO t1 VALUES (4,    NULL);
INSERT INTO t1 VALUES (4,    0);
INSERT INTO t1 VALUES (NULL, NULL);
CREATE TABLE t2 (i1 int DEFAULT NULL,i2 int DEFAULT NULL) ;
INSERT INTO t2 VALUES (4, NULL);
INSERT INTO t2 VALUES (5, 0);
SELECT i1, i2
FROM t1
WHERE (i1, i2)
NOT IN (SELECT i1, i2 FROM t2);
i1	i2
1	null
2	3
INSERT INTO t1 VALUES (NULL, NULL);
SELECT i1, i2
FROM t1
WHERE (i1, i2)
NOT IN (SELECT i1, i2 FROM t2);
i1	i2
1	null
2	3
drop table if exists t1;
drop table if exists t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(a INT);
INSERT INTO t2 VALUES(1),(1),(1),(1),(1);
SELECT
(
(SELECT 1 IN (SELECT 1 FROM t1 AS x1))
IN
(
SELECT 1 FROM t2
WHERE a IN (SELECT 4 FROM t1 AS x2)
)
) AS result
FROM t1 AS x3;
result
false
SELECT
(
(36, (SELECT 1 IN (SELECT 1 FROM t1 AS x1)))
IN
(
SELECT 36, 1 FROM t2
WHERE a IN (SELECT 4 FROM t1 AS x2)
)
) AS result
FROM t1 AS x3;
result
false
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (
id int(11) default NULL
) ;
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
CREATE TABLE t2 (
id int(11) default NULL,
name varchar(15) default NULL
) ;
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
update t2 set t2.name='lenka' where t2.id in (select id from t1);
select * from t2;
id	name
4	vita
1	lenka
2	lenka
1	lenka
delete from t1 where t1.id in  (select id from t2);
select * from t1;
id
3
DROP TABLE IF EXISTS t1;
drop table if exists t2;
DROP TABLE IF EXISTS t1;
drop table if exists t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (a int(11) NOT NULL default 0, PRIMARY KEY  (a));
CREATE TABLE t2 (a int(11) default 0);
CREATE TABLE t3 (a int(11) default 0);
INSERT INTO t3 VALUES (1),(2),(3);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2),(3);
SELECT t1.a FROM t1 where t1.a in (select t2.a from t2 order by t2.a desc) ;
a
1
2
3
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE `t1` ( `aid` int(11) NOT NULL default 0, `bid` int(11) NOT NULL default 0);
CREATE TABLE `t2` ( `aid` int(11) NOT NULL default 0, `bid` int(11) NOT NULL default 0);
insert into t1 values (1,1),(1,2),(2,1),(2,2);
insert into t2 values (1,2),(2,2);
select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);
aid	bid
1	1
2	1
select * from t1 where t1.aid in (select aid from t2 where bid=t1.bid);
aid	bid
1	2
2	2
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
INSERT INTO t1 VALUES (1, 1);
CREATE TABLE t2 (select_id BIGINT, values_id BIGINT);
INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
SELECT values_id FROM t1
WHERE values_id IN (SELECT values_id FROM t2  WHERE select_id IN (1, 0));
values_id
1
SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id BETWEEN 0 AND 1);
values_id
1
SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id = 0 OR select_id = 1);
values_id
1
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (a INT NOT NULL);
INSERT INTO t1 VALUES (1),(-1), (65),(66);
CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO t2 VALUES (65),(66);
SELECT a FROM t1 WHERE a NOT IN (65,66);
a
1
-1
SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
a
1
-1
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2 (placeholder CHAR(11));
INSERT INTO t2 VALUES("placeholder");
SELECT (1, 2) IN (SELECT t1.a, 2)         FROM t1 GROUP BY t1.a;
(1, 2) IN (SELECT t1.a, 2)
true
SELECT (1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a;
(1, 2) IN (SELECT t1.a, 2 FROM t2)
true
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE false);
1
1
1
1
1
1
1
DROP TABLE IF EXISTS t1;
drop table if exists t2;
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (c int, d int);
CREATE TABLE t3 (e int);
INSERT INTO t1 VALUES
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
INSERT INTO t2 VALUES
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
INSERT INTO t3 VALUES (10), (30), (10), (20) ;
SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d);
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d);
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2  WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e));
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2  WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2  WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e));
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2  WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
SQL syntax error: aggregate function max not allowed in WHERE clause
SELECT a FROM t1 GROUP BY a  HAVING a IN (SELECT c FROM t2  WHERE MIN(b) < d AND EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
SQL syntax error: aggregate function min not allowed in WHERE clause
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (a int, b int);
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
create table t2 (a int, b int);
insert into t2 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
update t2 set b=1;
create table t3 (a int, oref int);
insert into t3 values (1, 1), (NULL,1), (NULL,0);
select a, oref,t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z from t3;
a	oref	Z
1	1	true
null	1	null
null	0	false
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (a int NOT NULL, b int NOT NULL);
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
create table t2 (a int, b int);
insert into t2 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
update t2 set b=1;
create table t3 (a int, oref int);
insert into t3 values (1, 1), (NULL,1), (NULL,0);
select a, oref,t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z from t3;
a	oref	Z
1	1	true
null	1	null
null	0	false
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
create table t1 (oref int, grp int);
insert into t1 (oref, grp) values(1, 1),(1, 1);
create table t2 (oref int, a int);
insert into t2 values(1, NULL),(2, NULL);
select a, oref, a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
SQL syntax error: column "t2.oref" must appear in the GROUP BY clause or be used in an aggregate function
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (a int, b int, primary key (a));
insert into t1 values (1,1), (3,1),(100,1);
create table t2 (a int, b int);
insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
select a,b, a in (select a from t1 where t1.b = t2.b union select a from t1 where t1.b = t2.b) Z from t2 ;
a	b	z
1	1	true
2	1	false
null	1	null
null	0	false
select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
a	b	Z
1	1	true
2	1	false
null	1	null
null	0	false
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t3 (a int);
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, b int, oref int);
insert into t2 values (NULL,1, 100), (NULL,2, 100);
create table t1 (a int, b int, c int);
insert into t1 select 2*A, 2*A, 100 from t3;
create table t4 (x int);
insert into t4 select A.a + 10*B.a from t1 A, t1 B;
select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) z from t2;
a	b	oref	z
null	1	100	false
null	2	100	null
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
insert into t1 (oref, grp, ie1, ie2) values('aa', 10, 2, 1),('aa', 10, 1, 1),('aa', 20, 2, 1),('bb', 10, 3, 1),('cc', 10, 4, 2),('cc', 20, 3, 2),('ee', 10, 2, 1),('ee', 10, 1, 2),('ff', 20, 2, 2),('ff', 20, 1, 2);
create table t2 (oref char(4), a int, b int);
insert into t2 values('ee', NULL, 1),('bb', 2, 1),('ff', 2, 2),('cc', 3, NULL),('bb', NULL, NULL),('aa', 1, 1),('dd', 1, NULL);
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
oref	a	b	Z
cc	3	null	null
insert into t2 values ('new1', 10,10);
insert into t1 values ('new1', 1234, 10, NULL);
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
oref	a	b	Z
new1	10	10	null
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (oref char(4), grp int, ie int);
insert into t1 (oref, grp, ie) values ('aa', 10, 2),('aa', 10, 1),('aa', 20, NULL),('bb', 10, 3),('cc', 10, 4),('cc', 20, NULL),('ee', 10, NULL),('ee', 10, NULL),('ff', 20, 2),('ff', 20, 1);
create table t2 (oref char(4), a int);
insert into t2 values('ee', NULL),('bb', 2),('ff', 2),('cc', 3),('aa', 1),('dd', NULL),('bb', NULL);
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
oref	a	Z
ee	null	null
bb	2	false
ff	2	true
cc	3	null
aa	1	true
dd	null	false
bb	null	null
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
oref	a
ff	2
aa	1
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
oref	a
bb	2
dd	null
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref	a	Z
ee	null	null
bb	2	false
ff	2	false
cc	3	null
aa	1	true
dd	null	false
bb	null	null
select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref group by grp);
oref	a
aa	1
select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref group by grp);
oref	a
bb	2
ff	2
dd	null
update t1 set ie=3 where oref='ff' and ie=1;
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref	a	Z
ee	null	null
bb	2	false
ff	2	true
cc	3	null
aa	1	true
dd	null	false
bb	null	null
select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref group by grp);
oref	a
ff	2
aa	1
select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref group by grp);
oref	a
bb	2
dd	null
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp having min(ie) > 1) Z from t2;
oref	a	Z
ee	null	false
bb	2	false
ff	2	true
cc	3	false
aa	1	false
dd	null	false
bb	null	null
select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref group by grp having min(ie) > 1);
oref	a
ff	2
select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref group by grp having min(ie) > 1);
oref	a
ee	null
bb	2
cc	3
aa	1
dd	null
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (oref char(4), grp int, ie1 int, ie2 int);
insert into t1 (oref, grp, ie1, ie2) values ('aa', 10, 2, 1),('aa', 10, 1, 1),('aa', 20, 2, 1),('bb', 10, 3, 1),('cc', 10, 4, 2),('cc', 20, 3, 2),('ee', 10, 2, 1),('ee', 10, 1, 2),('ff', 20, 2, 2),('ff', 20, 1, 2);
create table t2 (oref char(4), a int, b int);
insert into t2 values('ee', NULL, 1),('bb', 2, 1), ('ff', 2, 2),('cc', 3, NULL),('bb', NULL, NULL),('aa', 1, 1),('dd', 1, NULL);
select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
oref	a	b	Z
ee	null	1	null
bb	2	1	false
ff	2	2	true
cc	3	null	null
bb	null	null	null
aa	1	1	true
dd	1	null	false
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
oref	a	b
ff	2	2
aa	1	1
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
oref	a	b
bb	2	1
dd	1	null
select oref, a, b,(a,b) in (select min(ie1),max(ie2) from t1 where oref=t2.oref group by grp) Z from t2;
oref	a	b	Z
ee	null	1	false
bb	2	1	false
ff	2	2	false
cc	3	null	null
bb	null	null	null
aa	1	1	true
dd	1	null	false
select oref, a, b from t2 where (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
oref	a	b
aa	1	1
select oref, a, b from t2 where (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
oref	a	b
ee	null	1
bb	2	1
ff	2	2
dd	1	null
DROP TABLE IF EXISTS t1;
drop table if exists t2;
create table t1 (oref char(4), grp int, ie int primary key);
insert into t1 (oref, grp, ie) values('aa', 10, 2),('aa', 10, 1),('bb', 10, 3),('cc', 10, 4),('cc', 20, 5),('cc', 10, 6);
create table t2 (oref char(4), a int);
insert into t2 values  ('ee', NULL),('bb', 2),('cc', 5),('cc', 2),('cc', NULL),('aa', 1),('bb', NULL);
select oref, a, a in (select ie from t1 where oref=t2.oref) z from t2;
oref	a	z
ee	null	false
bb	2	false
cc	5	true
cc	2	false
cc	null	null
aa	1	true
bb	null	null
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
oref	a
cc	5
aa	1
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
oref	a
ee	null
bb	2
cc	2
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) z from t2;
oref	a	z
ee	null	false
bb	2	false
cc	5	true
cc	2	false
cc	null	null
aa	1	true
bb	null	null
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int, PRIMARY KEY(b));
INSERT INTO t1 VALUES (1), (NULL), (4);
INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
a
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
a
1
4
DROP TABLE IF EXISTS t1;
drop table if exists t2;
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int PRIMARY KEY);
CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
INSERT INTO t1 VALUES (2), (NULL), (3), (1);
INSERT INTO t2 VALUES (234), (345), (457);
INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3  WHERE t3.name='xxx' AND t2.id=t3.id);
id
2
null
3
1
SELECT (t1.id IN (SELECT t2.id FROM t2,t3  WHERE t3.name='xxx' AND t2.id=t3.id)) AS x FROM t1;
x
false
false
false
false
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
CREATE TABLE t1 (
pk INT PRIMARY KEY,
int_key INT,
varchar_key VARCHAR(5) UNIQUE,
varchar_nokey VARCHAR(5)
);
INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p');
SELECT varchar_nokey 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{'
);
varchar_nokey
DROP TABLE IF EXISTS t1;
drop table if exists t1;
drop table if exists t2;
CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TABLE t1s(i INT);
INSERT INTO t1s VALUES (10), (20), (30);
CREATE TABLE t2s(i INT);
INSERT INTO t2s VALUES (100), (200), (300);
SELECT * FROM t1
WHERE t1.i NOT IN
(
SELECT t2s.i
FROM
t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i
HAVING t2s.i = 999
);
SQL syntax error: column "t2s.i" must appear in the GROUP BY clause or be used in an aggregate function
SELECT * FROM t1
WHERE t1.I IN
(
SELECT t2s.i
FROM
t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i
HAVING t2s.i = 999
);
SQL syntax error: column "t2s.i" must appear in the GROUP BY clause or be used in an aggregate function
SELECT * FROM t1
WHERE NOT t1.I = ANY
(
SELECT t2s.i
FROM
t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i
HAVING t2s.i = 999
);
SQL syntax error: column "t2s.i" must appear in the GROUP BY clause or be used in an aggregate function
SELECT * FROM t1
WHERE t1.i = ANY (
SELECT t2s.i
FROM
t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i
HAVING t2s.i = 999
);
SQL syntax error: column "t2s.i" must appear in the GROUP BY clause or be used in an aggregate function
DROP TABLE IF EXISTS t1;
drop table if exists t2;
drop table if exists t3;
CREATE TABLE parent (id int);
INSERT INTO parent VALUES (1), (2);
CREATE TABLE child (parent_id int, other int);
INSERT INTO child VALUES (1,NULL);
SELECT    p.id, c.parent_id
FROM      parent p
LEFT JOIN child  c
ON        p.id = c.parent_id
WHERE     c.parent_id NOT IN (
SELECT parent_id
FROM   child
WHERE  parent_id = 3
);
id	parent_id
1	1
2	null
SELECT    p.id, c.parent_id
FROM      parent p
LEFT JOIN child  c
ON        p.id = c.parent_id
WHERE     c.parent_id IN (
SELECT parent_id
FROM   child
WHERE  parent_id = 3
);
id	parent_id
SELECT    p.id, c.parent_id
FROM      parent p
LEFT JOIN child  c
ON        p.id = c.parent_id
WHERE     c.parent_id IN (
SELECT parent_id
FROM   child
WHERE  parent_id = 3
);
id	parent_id
DROP TABLE IF EXISTS parent;
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS cc;
DROP TABLE IF EXISTS bb;
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS b;
CREATE TABLE cc (
pk INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk)
);
INSERT INTO cc VALUES (10,7,'v');
INSERT INTO cc VALUES (11,1,'r');
CREATE TABLE bb (
pk INT,
col_date_key DATE,
PRIMARY KEY (pk)
);
INSERT INTO bb VALUES (10,'2002-02-21');
CREATE TABLE c (
pk INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk)
);
INSERT INTO c VALUES (1,NULL,'w');
INSERT INTO c VALUES (19,NULL,'f');
CREATE TABLE b (
pk INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk)
);
INSERT INTO b VALUES (1,7,'f');
SELECT col_int_key
FROM b granparent1
WHERE (col_int_key, col_int_key) IN (
SELECT parent1.pk, parent1.pk
FROM bb parent1 JOIN cc parent2
ON parent2.col_varchar_key = parent2.col_varchar_key
WHERE granparent1.col_varchar_key IN (
SELECT col_varchar_key
FROM c)
AND parent1.pk = granparent1.col_int_key
ORDER BY parent1.col_date_key
);
col_int_key
DROP TABLE IF EXISTS  t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1xt2;
CREATE TABLE t1 (
id_1 int(5) NOT NULL,
t varchar(4) DEFAULT NULL
);
CREATE TABLE t2 (
id_2 int(5) NOT NULL,
t varchar(4) DEFAULT NULL
);
CREATE TABLE t1xt2 (
id_1 int(5) NOT NULL,
id_2 int(5) NOT NULL
);
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa');
INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4);
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
id_1
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
id_1
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
id_1
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
id_1
1
2
3
4
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)));
id_1
1
2
3
4
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))));
id_1
1
2
3
4
insert INTO t1xt2 VALUES (1, 12);
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
id_1
1
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
id_1
1
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
id_1
1
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
id_1
2
3
4
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
id_1
2
3
4
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
id_1
2
3
4
insert INTO t1xt2 VALUES (2, 12);
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
id_1
1
2
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
id_1
1
2
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
id_1
1
2
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1));
id_1
3
4
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)));
id_1
3
4
SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))));
id_1
3
4
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1xt2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a int );
INSERT INTO t1 VALUES(0);
SELECT NULL IN (SELECT 1 FROM t1);
NULL IN (SELECT 1 FROM t1)
null
SELECT (NULL AND 1) IN (SELECT 1 FROM t1);
(null and 1) in (select 1 from t1)
null
SELECT (NULL, 1) IN (SELECT 1,1 FROM t1);
(NULL, 1) IN (SELECT 1,1 FROM t1)
null
SELECT (NULL, NULL) IN (SELECT 1,1 FROM t1);
(NULL, NULL) IN (SELECT 1,1 FROM t1)
null
SELECT (NULL OR 1) IN (SELECT 1 FROM t1);
(null or 1) in (select 1 from t1)
true
SELECT (NULL IS NULL) IN  (SELECT 1 FROM t1);
(null is null) in (select 1 from t1)
true
DELETE FROM t1;
SELECT NULL IN (SELECT 1 FROM t1);
NULL IN (SELECT 1 FROM t1)
false
SELECT (NULL AND 1) IN (SELECT 1 FROM t1);
(null and 1) in (select 1 from t1)
false
SELECT (NULL, 1) IN (SELECT 1,1 FROM t1);
(NULL, 1) IN (SELECT 1,1 FROM t1)
false
SELECT (NULL, NULL) IN (SELECT 1,1 FROM t1);
(NULL, NULL) IN (SELECT 1,1 FROM t1)
false
SELECT (NULL OR 1) IN (SELECT 1 FROM t1);
(null or 1) in (select 1 from t1)
false
SELECT (NULL IS NULL) IN  (SELECT 1 FROM t1);
(null is null) in (select 1 from t1)
false
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INTEGER);
INSERT INTO t1 VALUES (1), (2), (3);
SELECT 2 IN ( SELECT 5 UNION SELECT NULL ) FROM t1;
'union' will be supported in future version.
DROP TABLE IF EXISTS t1;