drop table if exists ii;
drop table if exists oo;

create table ii (
  id int primary key,
  c1 int not null,
  c2 int not null,
  c3 int not null,
  c4 int not null,
  c5 int not null,
  c6 int not null,
  c7 int not null,
  c8 int not null,
  c9 int not null,
  c10 int not null,
  n1 int,
  n2 int,
  n3 int,
  n4 int,
  n5 int,
  n6 int,
  n7 int,
  n8 int,
  n9 int,
  n10 int
);

create table oo (
  id int primary key,
  c1 int not null,
  c2 int not null,
  c3 int not null,
  c4 int not null,
  c5 int not null,
  c6 int not null,
  c7 int not null,
  c8 int not null,
  c9 int not null,
  c10 int not null,
  n1 int,
  n2 int,
  n3 int,
  n4 int,
  n5 int,
  n6 int,
  n7 int,
  n8 int,
  n9 int,
  n10 int
);

-- Query 1 ANTI JOIN
EXPLAIN ANALYZE SELECT * FROM oo WHERE (c1,c2,c3) NOT IN(SELECT c1,c2,c3 FROM ii);

-- Query 2 ANTI JOIN
EXPLAIN ANALYZE SELECT * FROM oo WHERE (c1,c2) NOT IN(SELECT n1,n2 FROM ii WHERE n1 > 200 AND n2 < 300);

-- Query 3 Sub Plan
EXPLAIN ANALYZE SELECT * FROM oo WHERE (c1,c2) NOT IN(SELECT n1,n2 FROM ii WHERE n1 > 200 OR n2 < 300);

-- Query 4 ANTI JOIN
EXPLAIN ANALYZE SELECT * FROM oo WHERE (c1,c2) NOT IN(SELECT n1,n2 FROM ii WHERE n1>0 AND n2>0 AND n3>0 AND n4>0 AND n5>0 AND n6>0 AND n7>0 AND n8>0 AND n9>0 AND n10>0);

-- Query 5 ANTI JOIN
EXPLAIN ANALYZE SELECT * FROM oo
WHERE (n1,n2) NOT IN(SELECT n1,n2 FROM ii WHERE n1>0 AND n2>0 AND n3>0 AND n4>0 AND n5>0 AND n6>0 AND n7>0 AND n8>0 AND n9>0 AND n10>0)
  AND n1>0 AND n2>0 AND n3>0 AND n4>0 AND n5>0 AND n6>0 AND n7>0 AND n8>0 AND n9>0 AND n10>0

-- Query 6 Sub Plan
EXPLAIN ANALYZE SELECT * FROM oo WHERE n1 NOT IN(SELECT n1 FROM ii);
