0001
0002
0003
0004
0005
0006
0007
0008
0009 CREATE OR REPLACE TEMPORARY VIEW INT4_TBL AS SELECT * FROM
0010 (VALUES (0), (123456), (-123456), (2147483647), (-2147483647))
0011 AS v(f1);
0012 CREATE OR REPLACE TEMPORARY VIEW INT8_TBL AS SELECT * FROM
0013 (VALUES
0014 (123, 456),
0015 (123, 4567890123456789),
0016 (4567890123456789, 123),
0017 (4567890123456789, 4567890123456789),
0018 (4567890123456789, -4567890123456789))
0019 AS v(q1, q2);
0020 CREATE OR REPLACE TEMPORARY VIEW FLOAT8_TBL AS SELECT * FROM
0021 (VALUES (0.0), (-34.84), (-1004.30),
0022 (CAST('-1.2345678901234e+200' AS DOUBLE)), (CAST('-1.2345678901234e-200' AS DOUBLE)))
0023 AS v(f1);
0024
0025
0026
0027 SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
0028
0029 SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
0030
0031 SELECT 1 AS two UNION ALL SELECT 2;
0032
0033 SELECT 1 AS two UNION ALL SELECT 1;
0034
0035 SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
0036
0037 SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
0038
0039 SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
0040
0041 SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
0042
0043
0044
0045 SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
0046
0047 SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
0048
0049 SELECT 1 AS one UNION SELECT double(1.0) ORDER BY 1;
0050
0051 SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
0052
0053 SELECT double(1.0) AS two UNION ALL SELECT 1 ORDER BY 1;
0054
0055 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
0056
0057 SELECT double(1.1) AS two UNION SELECT 2 UNION SELECT double(2.0) ORDER BY 1;
0058
0059 SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
0060
0061 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
0062
0063
0064
0065
0066
0067 SELECT f1 AS five FROM FLOAT8_TBL
0068 UNION
0069 SELECT f1 FROM FLOAT8_TBL
0070 ORDER BY 1;
0071
0072 SELECT f1 AS ten FROM FLOAT8_TBL
0073 UNION ALL
0074 SELECT f1 FROM FLOAT8_TBL;
0075
0076 SELECT f1 AS nine FROM FLOAT8_TBL
0077 UNION
0078 SELECT f1 FROM INT4_TBL
0079 ORDER BY 1;
0080
0081 SELECT f1 AS ten FROM FLOAT8_TBL
0082 UNION ALL
0083 SELECT f1 FROM INT4_TBL;
0084
0085 SELECT f1 AS five FROM FLOAT8_TBL
0086 WHERE f1 BETWEEN -1e6 AND 1e6
0087 UNION
0088 SELECT f1 FROM INT4_TBL
0089 WHERE f1 BETWEEN 0 AND 1000000
0090 ORDER BY 1;
0091
0092
0093
0094
0095
0096
0097
0098
0099
0100
0101
0102
0103
0104
0105
0106
0107
0108
0109
0110
0111
0112
0113
0114
0115
0116
0117
0118 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
0119
0120 SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
0121
0122 SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
0123
0124 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
0125
0126 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
0127
0128 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
0129
0130 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
0131
0132 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
0133
0134
0135
0136
0137
0138 (SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
0139 (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
0140 (SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
0141 (SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
0142
0143
0144
0145
0146
0147
0148
0149
0150 select count(*) from
0151 ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
0152
0153
0154
0155 select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
0156
0157
0158
0159
0160
0161
0162 select count(*) from
0163 ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
0164
0165
0166
0167 select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
0168
0169
0170
0171
0172
0173
0174
0175 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1;
0176
0177 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
0178
0179
0180
0181
0182
0183 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl ORDER BY 1;
0184
0185 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1;
0186
0187 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl ORDER BY 1))) UNION ALL SELECT q2 FROM int8_tbl;
0188
0189 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
0190
0191 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
0192
0193 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
0194
0195
0196
0197
0198
0199
0200 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
0201 ORDER BY q2,q1;
0202
0203
0204 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
0205
0206
0207 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
0208
0209
0210
0211
0212
0213 (((((select * from int8_tbl)))));
0214
0215
0216
0217
0218
0219
0220
0221
0222
0223
0224
0225
0226
0227
0228
0229
0230
0231
0232
0233
0234
0235 select * from range(1,5) union select * from range(1,3);
0236 select * from range(1,6) union all select * from range(1,4);
0237 select * from range(1,6) intersect select * from range(1,4);
0238 select * from range(1,6) intersect all select * from range(1,4);
0239 select * from range(1,6) except select * from range(1,4);
0240 select * from range(1,6) except all select * from range(1,4);
0241
0242
0243
0244
0245
0246
0247
0248
0249
0250
0251 select * from range(1,6) union select * from range(1,4);
0252 select * from range(1,6) union all select * from range(1,4);
0253 select * from range(1,6) intersect select * from range(1,4);
0254 select * from range(1,6) intersect all select * from range(1,4);
0255 select * from range(1,6) except select * from range(1,4);
0256 select * from range(1,6) except all select * from range(1,4);
0257
0258
0259
0260
0261
0262
0263
0264
0265
0266
0267
0268
0269
0270
0271
0272
0273 SELECT cast('3.4' as decimal(38, 18)) UNION SELECT 'foo';
0274
0275
0276
0277
0278
0279
0280
0281
0282
0283
0284
0285
0286
0287
0288
0289
0290
0291
0292
0293
0294
0295
0296
0297
0298
0299
0300
0301
0302
0303
0304
0305
0306
0307
0308
0309
0310
0311
0312
0313
0314
0315
0316
0317
0318
0319
0320
0321
0322
0323
0324
0325
0326
0327
0328
0329
0330
0331
0332
0333
0334
0335
0336
0337
0338
0339
0340
0341
0342
0343
0344
0345
0346
0347
0348
0349
0350
0351
0352
0353
0354
0355
0356
0357
0358
0359
0360
0361
0362
0363
0364
0365
0366
0367
0368
0369
0370
0371
0372 SELECT * FROM
0373 (SELECT 1 AS t, 2 AS x
0374 UNION
0375 SELECT 2 AS t, 4 AS x) ss
0376 WHERE x < 4
0377 ORDER BY x;
0378
0379
0380
0381
0382
0383
0384
0385
0386
0387 ;
0388
0389 SELECT * FROM
0390 (SELECT 1 AS t, id as x from range(1,11)
0391 UNION
0392 SELECT 2 AS t, 4 AS x) ss
0393 WHERE x < 4
0394 ORDER BY x;
0395
0396
0397
0398
0399
0400
0401
0402
0403
0404 SELECT * FROM
0405 (SELECT 1 AS t, int((random()*3)) AS x
0406 UNION
0407 SELECT 2 AS t, 4 AS x) ss
0408 WHERE x > 3
0409 ORDER BY x;
0410
0411
0412
0413
0414
0415
0416
0417
0418
0419
0420 select distinct q1 from
0421 (select distinct * from int8_tbl i81
0422 union all
0423 select distinct * from int8_tbl i82) ss
0424 where q2 = q2;
0425
0426
0427
0428
0429
0430
0431
0432
0433 select distinct q1 from
0434 (select distinct * from int8_tbl i81
0435 union all
0436 select distinct * from int8_tbl i82) ss
0437 where -q1 = q2;
0438
0439
0440
0441
0442
0443
0444
0445
0446
0447
0448
0449
0450
0451
0452
0453
0454
0455
0456
0457
0458
0459
0460
0461
0462
0463
0464
0465
0466
0467
0468 select * from
0469 (select *, 0 as x from int8_tbl a
0470 union all
0471 select *, 1 as x from int8_tbl b) ss
0472 where (x = 0) or (q1 >= q2 and q1 <= q2);