sql-tests/inputs/except-all.sql

0001 CREATE TEMPORARY VIEW tab1 AS SELECT * FROM VALUES
0002     (0), (1), (2), (2), (2), (2), (3), (null), (null) AS tab1(c1);
0003 CREATE TEMPORARY VIEW tab2 AS SELECT * FROM VALUES
0004     (1), (2), (2), (3), (5), (5), (null) AS tab2(c1);
0005 CREATE TEMPORARY VIEW tab3 AS SELECT * FROM VALUES
0006     (1, 2),
0007     (1, 2),
0008     (1, 3),
0009     (2, 3),
0010     (2, 2)
0011     AS tab3(k, v);
0012 CREATE TEMPORARY VIEW tab4 AS SELECT * FROM VALUES
0013     (1, 2),
0014     (2, 3),
0015     (2, 2),
0016     (2, 2),
0017     (2, 20)
0018     AS tab4(k, v);
0019
0020 -- Basic EXCEPT ALL
0021 SELECT * FROM tab1
0022 EXCEPT ALL
0023 SELECT * FROM tab2;
0024
0025 -- MINUS ALL (synonym for EXCEPT)
0026 SELECT * FROM tab1
0027 MINUS ALL
0028 SELECT * FROM tab2;
0029
0030 -- EXCEPT ALL same table in both branches
0031 SELECT * FROM tab1
0032 EXCEPT ALL
0033 SELECT * FROM tab2 WHERE c1 IS NOT NULL;
0034
0035 -- Empty left relation
0036 SELECT * FROM tab1 WHERE c1 > 5
0037 EXCEPT ALL
0038 SELECT * FROM tab2;
0039
0040 -- Empty right relation
0041 SELECT * FROM tab1
0042 EXCEPT ALL
0043 SELECT * FROM tab2 WHERE c1 > 6;
0044
0045 -- Type Coerced ExceptAll
0046 SELECT * FROM tab1
0047 EXCEPT ALL
0048 SELECT CAST(1 AS BIGINT);
0049
0050 -- Error as types of two side are not compatible
0051 SELECT * FROM tab1
0052 EXCEPT ALL
0053 SELECT array(1);
0054
0055 -- Basic
0056 SELECT * FROM tab3
0057 EXCEPT ALL
0058 SELECT * FROM tab4;
0059
0060 -- Basic
0061 SELECT * FROM tab4
0062 EXCEPT ALL
0063 SELECT * FROM tab3;
0064
0065 -- EXCEPT ALL + INTERSECT
0066 SELECT * FROM tab4
0067 EXCEPT ALL
0068 SELECT * FROM tab3
0069 INTERSECT DISTINCT
0070 SELECT * FROM tab4;
0071
0072 -- EXCEPT ALL + EXCEPT
0073 SELECT * FROM tab4
0074 EXCEPT ALL
0075 SELECT * FROM tab3
0076 EXCEPT DISTINCT
0077 SELECT * FROM tab4;
0078
0079 -- Chain of set operations
0080 SELECT * FROM tab3
0081 EXCEPT ALL
0082 SELECT * FROM tab4
0083 UNION ALL
0084 SELECT * FROM tab3
0085 EXCEPT DISTINCT
0086 SELECT * FROM tab4;
0087
0088 -- Mismatch on number of columns across both branches
0089 SELECT k FROM tab3
0090 EXCEPT ALL
0091 SELECT k, v FROM tab4;
0092
0093 -- Chain of set operations
0094 SELECT * FROM tab3
0095 EXCEPT ALL
0096 SELECT * FROM tab4
0097 UNION
0098 SELECT * FROM tab3
0099 EXCEPT DISTINCT
0100 SELECT * FROM tab4;
0101
0102 -- Using MINUS ALL
0103 SELECT * FROM tab3
0104 MINUS ALL
0105 SELECT * FROM tab4
0106 UNION
0107 SELECT * FROM tab3
0108 MINUS DISTINCT
0109 SELECT * FROM tab4;
0110
0111 -- Chain of set operations
0112 SELECT * FROM tab3
0113 EXCEPT ALL
0114 SELECT * FROM tab4
0115 EXCEPT DISTINCT
0116 SELECT * FROM tab3
0117 EXCEPT DISTINCT
0118 SELECT * FROM tab4;
0119
0120 -- Join under except all. Should produce empty resultset since both left and right sets
0121 -- are same.
0122 SELECT *
0123 FROM   (SELECT tab3.k,
0124                tab4.v
0125         FROM   tab3
0126                JOIN tab4
0127                  ON tab3.k = tab4.k)
0128 EXCEPT ALL
0129 SELECT *
0130 FROM   (SELECT tab3.k,
0131                tab4.v
0132         FROM   tab3
0133                JOIN tab4
0134                  ON tab3.k = tab4.k);
0135
0136 -- Join under except all (2)
0137 SELECT *
0138 FROM   (SELECT tab3.k,
0139                tab4.v
0140         FROM   tab3
0141                JOIN tab4
0142                  ON tab3.k = tab4.k)
0143 EXCEPT ALL
0144 SELECT *
0145 FROM   (SELECT tab4.v AS k,
0146                tab3.k AS v
0147         FROM   tab3
0148                JOIN tab4
0149                  ON tab3.k = tab4.k);
0150
0151 -- Group by under ExceptAll
0152 SELECT v FROM tab3 GROUP BY v
0153 EXCEPT ALL
0154 SELECT k FROM tab4 GROUP BY k;
0155
0156 -- Clean-up
0157 DROP VIEW IF EXISTS tab1;
0158 DROP VIEW IF EXISTS tab2;
0159 DROP VIEW IF EXISTS tab3;
0160 DROP VIEW IF EXISTS tab4;