sql-tests/inputs/except.sql

0001 -- Tests different scenarios of except operation
0002 create temporary view t1 as select * from values
0003   ("one", 1),
0004   ("two", 2),
0005   ("three", 3),
0006   ("one", NULL)
0007   as t1(k, v);
0008
0009 create temporary view t2 as select * from values
0010   ("one", 1),
0011   ("two", 22),
0012   ("one", 5),
0013   ("one", NULL),
0014   (NULL, 5)
0015   as t2(k, v);
0016
0017
0018 -- Except operation that will be replaced by left anti join
0019 SELECT * FROM t1 EXCEPT SELECT * FROM t2;
0020
0021
0022 -- Except operation that will be replaced by Filter: SPARK-22181
0023 SELECT * FROM t1 EXCEPT SELECT * FROM t1 where v <> 1 and v <> 2;
0024
0025
0026 -- Except operation that will be replaced by Filter: SPARK-22181
0027 SELECT * FROM t1 where v <> 1 and v <> 22 EXCEPT SELECT * FROM t1 where v <> 2 and v >= 3;
0028
0029
0030 -- Except operation that will be replaced by Filter: SPARK-22181
0031 SELECT t1.* FROM t1, t2 where t1.k = t2.k
0032 EXCEPT
0033 SELECT t1.* FROM t1, t2 where t1.k = t2.k and t1.k != 'one';
0034
0035
0036 -- Except operation that will be replaced by left anti join
0037 SELECT * FROM t2 where v >= 1 and v <> 22 EXCEPT SELECT * FROM t1;
0038
0039
0040 -- Except operation that will be replaced by left anti join
0041 SELECT (SELECT min(k) FROM t2 WHERE t2.k = t1.k) min_t2 FROM t1
0042 MINUS
0043 SELECT (SELECT min(k) FROM t2) abs_min_t2 FROM t1 WHERE  t1.k = 'one';
0044
0045
0046 -- Except operation that will be replaced by left anti join
0047 SELECT t1.k
0048 FROM   t1
0049 WHERE  t1.v <= (SELECT   max(t2.v)
0050                 FROM     t2
0051                 WHERE    t2.k = t1.k)
0052 MINUS
0053 SELECT t1.k
0054 FROM   t1
0055 WHERE  t1.v >= (SELECT   min(t2.v)
0056                 FROM     t2
0057                 WHERE    t2.k = t1.k);