2 -- UNION (also INTERSECT, EXCEPT)
5 -- Simple UNION constructs
7 SELECT 1 AS two UNION SELECT 2;
9 SELECT 1 AS one UNION SELECT 1;
11 SELECT 1 AS two UNION ALL SELECT 2;
13 SELECT 1 AS two UNION ALL SELECT 1;
15 SELECT 1 AS three UNION SELECT 2 UNION SELECT 3;
17 SELECT 1 AS two UNION SELECT 2 UNION SELECT 2;
19 SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2;
21 SELECT 1.1 AS two UNION SELECT 2.2;
25 SELECT 1.1 AS two UNION SELECT 2;
27 SELECT 1 AS two UNION SELECT 2.2;
29 SELECT 1 AS one UNION SELECT 1.0::float8;
31 SELECT 1.1 AS two UNION ALL SELECT 2;
33 SELECT 1.0::float8 AS two UNION ALL SELECT 1;
35 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3;
37 SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8;
39 SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2;
41 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2);
44 -- Try testing from tables...
47 SELECT f1 AS five FROM FLOAT8_TBL
49 SELECT f1 FROM FLOAT8_TBL;
51 SELECT f1 AS ten FROM FLOAT8_TBL
53 SELECT f1 FROM FLOAT8_TBL;
55 SELECT f1 AS nine FROM FLOAT8_TBL
57 SELECT f1 FROM INT4_TBL;
59 SELECT f1 AS ten FROM FLOAT8_TBL
61 SELECT f1 FROM INT4_TBL;
63 SELECT f1 AS five FROM FLOAT8_TBL
64 WHERE f1 BETWEEN -1e6 AND 1e6
66 SELECT f1 FROM INT4_TBL
67 WHERE f1 BETWEEN 0 AND 1000000;
69 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
71 SELECT f1 FROM CHAR_TBL;
73 SELECT f1 AS three FROM VARCHAR_TBL
75 SELECT CAST(f1 AS varchar) FROM CHAR_TBL;
77 SELECT f1 AS eight FROM VARCHAR_TBL
79 SELECT f1 FROM CHAR_TBL;
81 SELECT f1 AS five FROM TEXT_TBL
83 SELECT f1 FROM VARCHAR_TBL
85 SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL;
88 -- INTERSECT and EXCEPT
91 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl;
93 SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl;
95 SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
97 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl;
99 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl;
101 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
103 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
105 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
111 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
113 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl;
116 -- Operator precedence and (((((extra))))) parentheses
119 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
121 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
123 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
125 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
127 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl)));
129 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl;
132 -- Subqueries with ORDER BY & LIMIT clauses
135 -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
136 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
139 -- This should fail, because q2 isn't a name of an EXCEPT output column
140 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
142 -- But this should work:
143 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
146 -- New syntaxes (7.1) permit new tests
149 (((((select * from int8_tbl)))));