create table t1 (a int, b int) engine=MyISAM; create table t2 (c int, d int) engine=MyISAM; insert into t1 values (1,1),(2,2),(3,3),(2,2); insert into t2 values (2,2),(2,2),(5,5); select * from t1 intersect all select * from t2; a b 2 2 2 2 (select a,b from t1) intersect all (select c,d from t2); a b 2 2 2 2 select * from ((select a,b from t1) intersect all (select c,d from t2)) t; a b 2 2 2 2 select * from ((select a from t1) intersect all (select c from t2)) t; a 2 2 drop tables t1,t2; create table t1 (a int, b int) engine=MyISAM; create table t2 (c int, d int) engine=MyISAM; create table t3 (e int, f int) engine=MyISAM; insert into t1 values (1,1),(2,2),(3,3),(2,2); insert into t2 values (2,2),(3,3),(4,4),(2,2); insert into t3 values (1,1),(2,2),(5,5),(2,2); (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); a b 2 2 2 2 EXPLAIN (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 2 INTERSECT t2 ALL NULL NULL NULL NULL 4 3 INTERSECT t3 ALL NULL NULL NULL NULL 4 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL EXPLAIN extended (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00 3 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 4 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 3 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00 4 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `a` EXPLAIN format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 4, "cost": "COST_REPLACED", "filtered": 100 } } ] } }, { "query_block": { "select_id": 2, "operation": "INTERSECT", "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 4, "cost": "COST_REPLACED", "filtered": 100 } } ] } }, { "query_block": { "select_id": 3, "operation": "INTERSECT", "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "rows": 4, "cost": "COST_REPLACED", "filtered": 100 } } ] } } ] } } } ANALYZE format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 2, "query_specifications": [ { "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } }, { "query_block": { "select_id": 2, "operation": "INTERSECT", "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } }, { "query_block": { "select_id": 3, "operation": "INTERSECT", "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } } ] } } } ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 2, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 2, "query_specifications": [ { "query_block": { "select_id": 2, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } }, { "query_block": { "select_id": 3, "operation": "INTERSECT", "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } }, { "query_block": { "select_id": 4, "operation": "INTERSECT", "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 4, "r_rows": 4, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } } ] } } } } } ] } } select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; a b 2 2 2 2 prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);"; execute stmt; a b 2 2 2 2 execute stmt; a b 2 2 2 2 prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a"; execute stmt; a b 2 2 2 2 execute stmt; a b 2 2 2 2 insert into t1 values (2,2),(3,3); insert into t2 values (2,2),(2,2),(2,2); (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); a b 2 2 2 2 (select a,b from t1) intersect (select c,d from t2) intersect all (select e,f from t3); a b 2 2 insert into t3 values (2,2); (select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3); a b 2 2 (select a,b from t1) intersect all (select c,e from t2,t3); a b 2 2 2 2 2 2 EXPLAIN (select a,b from t1) intersect all (select c,e from t2,t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 2 INTERSECT t3 ALL NULL NULL NULL NULL 5 2 INTERSECT t2 ALL NULL NULL NULL NULL 7 Using join buffer (flat, BNL join) NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL EXPLAIN extended (select a,b from t1) intersect all (select c,e from t2,t3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 2 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 7 100.00 Using join buffer (flat, BNL join) NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`) EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY ALL NULL NULL NULL NULL 6 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 3 INTERSECT t2 ALL NULL NULL NULL NULL 7 100.00 Using join buffer (flat, BNL join) NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a` EXPLAIN format=json (select a,b from t1) intersect all (select c,e from t2,t3); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "rows": 6, "cost": "COST_REPLACED", "filtered": 100 } } ] } }, { "query_block": { "select_id": 2, "operation": "INTERSECT", "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "rows": 5, "cost": "COST_REPLACED", "filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "loops": 5, "rows": 7, "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL" } } ] } } ] } } } ANALYZE format=json (select a,b from t1) intersect all (select c,e from t2,t3); ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 3, "query_specifications": [ { "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 6, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } }, { "query_block": { "select_id": 2, "operation": "INTERSECT", "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 5, "r_rows": 5, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "loops": 5, "r_loops": 1, "rows": 7, "r_rows": 7, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL", "r_loops": 5, "r_filtered": 100, "r_unpack_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_effective_rows": 7 } } ] } } ] } } } ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 3, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 3, "query_specifications": [ { "query_block": { "select_id": 2, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 6, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } } ] } }, { "query_block": { "select_id": 3, "operation": "INTERSECT", "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 5, "r_rows": 5, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "t2", "access_type": "ALL", "loops": 5, "r_loops": 1, "rows": 7, "r_rows": 7, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL", "r_loops": 5, "r_filtered": 100, "r_unpack_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_effective_rows": 7 } } ] } } ] } } } } } ] } } select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; a b 2 2 2 2 2 2 prepare stmt from "(select a,b from t1) intersect all (select c,e from t2,t3);"; execute stmt; a b 2 2 2 2 2 2 execute stmt; a b 2 2 2 2 2 2 prepare stmt from "select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a"; execute stmt; a b 2 2 2 2 2 2 execute stmt; a b 2 2 2 2 2 2 drop tables t1,t2,t3; select 1 as a from dual intersect all select 1 from dual; a 1 (select 1 from dual) intersect all (select 1 from dual); 1 1 (select 1 from dual into @v) intersect all (select 1 from dual); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @v) intersect all (select 1 from dual)' at line 1 select 1 from dual ORDER BY 1 intersect all select 1 from dual; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'intersect all select 1 from dual' at line 1 select 1 as a from dual union all select 1 from dual; a 1 1 create table t1 (a int, b blob, a1 int, b1 blob); create table t2 (c int, d blob, c1 int, d1 blob); insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt"),(2, "fgh", 2, "dffggtt"); insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"),(2, "fgh", 2, "dffggtt"); (select a,b,b1 from t1) intersect all (select c,d,d1 from t2); a b b1 2 fgh dffggtt 2 fgh dffggtt drop tables t1,t2; create table t1 (a int, b blob) engine=MyISAM; create table t2 (c int, d blob) engine=MyISAM; create table t3 (e int, f blob) engine=MyISAM; insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3); insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2); insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5); (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); a b 2 2 2 2 select * from ((select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3)) a; a b 2 2 prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);"; execute stmt; a b 2 2 2 2 execute stmt; a b 2 2 2 2 prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a"; execute stmt; a b 2 2 2 2 execute stmt; a b 2 2 2 2 create table t4 (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL, `b` blob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop tables t4; (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); a b 4 4 2 2 2 2 (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2); a b 4 4 2 2 drop tables t1,t2,t3; create table t1 (a int, b int); create table t2 (c int, d int); create table t3 (e int, f int); insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3); insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2); insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5); (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); a b 4 4 2 2 2 2 (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2); a b 4 4 2 2 drop tables t1,t2,t3; # # INTERSECT precedence # create table t1 (a int, b blob) engine=MyISAM; create table t2 (c int, d blob) engine=MyISAM; create table t3 (e int, f blob) engine=MyISAM; insert into t1 values (5,5),(6,6); insert into t2 values (2,2),(3,3); insert into t3 values (1,1),(3,3); (select a,b from t1) union all (select c,d from t2) intersect (select e,f from t3) union all (select 4,4); a b 5 5 6 6 3 3 4 4 (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); a b 5 5 6 6 3 3 4 4 explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 5 UNION ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all /* select#5 */ select `__5`.`c` AS `c`,`__5`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__5` union all (/* select#4 */ select 4 AS `4`,4 AS `4`) insert into t2 values (3,3); insert into t3 values (3,3); (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); e f 3 3 3 3 5 5 6 6 4 4 explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 INTERSECT t2 ALL NULL NULL NULL NULL 3 100.00 3 UNION t1 ALL NULL NULL NULL NULL 2 100.00 4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union all (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all (/* select#4 */ select 4 AS `4`,4 AS `4`) (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`); a b 5 5 6 6 3 3 4 4 prepare stmt from "(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4)"; execute stmt; a b 5 5 6 6 3 3 3 3 4 4 execute stmt; a b 5 5 6 6 3 3 3 3 4 4 create view v1 as (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); select b,a,b+1 from v1; b a b+1 5 5 6 6 6 7 3 3 4 3 3 4 4 4 5 select b,a,b+1 from v1 where a > 3; b a b+1 5 5 6 6 6 7 4 4 5 create procedure p1() select * from v1; call p1(); a b 5 5 6 6 3 3 3 3 4 4 call p1(); a b 5 5 6 6 3 3 3 3 4 4 drop procedure p1; create procedure p1() (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4); call p1(); a b 5 5 6 6 3 3 3 3 4 4 call p1(); a b 5 5 6 6 3 3 3 3 4 4 drop procedure p1; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`) union all select `__6`.`c` AS `c`,`__6`.`d` AS `d` from ((select `t2`.`c` AS `c`,`t2`.`d` AS `d` from `t2`) intersect all (select `t3`.`e` AS `e`,`t3`.`f` AS `f` from `t3`)) `__6` union all (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci drop view v1; drop tables t1,t2,t3; CREATE TABLE t (i INT); INSERT INTO t VALUES (1),(2); SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT ALL SELECT 3 ); i select i from t where exists ((select 6 as r from dual having t.i <> 6) intersect all (select 3 from dual having t.i <> 3)); i drop table t; CREATE TABLE t1 (a varchar(32)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'), ('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'), ('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'), ('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'), ('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'), ('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'), ('Vaduz'); CREATE TABLE t2 (b varchar(32)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'), ('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'), ('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'), ('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'), ('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'), ('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'), ('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'), ('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'); CREATE TABLE t3 (c varchar(32)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'), ('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'), ('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'), ('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'); select count(*) from ( SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b INTERSECT SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b ) a; count(*) 14848 select count(*) from ( SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b INTERSECT ALL SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b ) a; count(*) 14848 insert into t1 values ('Xiamen'); insert into t2 values ('Xiamen'),('Xiamen'); insert into t3 values ('Xiamen'); select count(*) from ( SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b INTERSECT ALL SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b ) a; count(*) 16430 drop table t1,t2,t3; CREATE TABLE t1 (a varchar(32) not null) ENGINE=MyISAM; INSERT INTO t1 VALUES ('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'), ('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'), ('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'), ('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'), ('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'), ('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'), ('Vaduz'),('Detroit'),('Detroit'); CREATE TABLE t2 (b varchar(32) not null) ENGINE=MyISAM; INSERT INTO t2 VALUES ('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'), ('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'), ('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'), ('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'), ('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'), ('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'), ('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'), ('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'),('Detroit'),('Detroit'); CREATE TABLE t3 (c varchar(32) not null) ENGINE=MyISAM; INSERT INTO t3 VALUES ('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'), ('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'), ('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'), ('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'), ('Detroit'); select count(*) from ( SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b INTERSECT SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b ) a; count(*) 15547 drop table t1,t2,t3; create table t12(c1 int); insert into t12 values(1); insert into t12 values(2); create table t13(c1 int); insert into t13 values(1); insert into t13 values(3); create table t234(c1 int); insert into t234 values(2); insert into t234 values(3); insert into t234 values(4); select * from t13 union select * from t234 intersect all select * from t12; c1 1 3 2 drop table t12,t13,t234; create table t1 (a int); insert into t1 values (3), (1), (7), (3), (2), (7), (4); create table t2 (a int); insert into t2 values (4), (5), (9), (1), (8), (9), (2), (2); create table t3 (a int); insert into t3 values (8), (1), (8), (2), (3), (7), (2); select * from t1 where a > 4 union all select * from t2 where a < 5 intersect all select * from t3 where a < 5; a 7 7 2 1 2 explain extended select * from t1 where a > 4 union all select * from t2 where a < 5 intersect all select * from t3 where a < 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where 4 UNION ALL NULL NULL NULL NULL 7 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 8 100.00 Using where 3 INTERSECT t3 ALL NULL NULL NULL NULL 7 100.00 Using where NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 union all /* select#4 */ select `__4`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect all /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4` drop table t1,t2,t3; # # MDEV-25158 Segfault on INTERSECT ALL with UNION in Oracle mode # create table t3 (x int); create table u3 (x int); create table i3 (x int); explain SELECT * from t3 union select * from u3 intersect all select * from i3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 0 Const row not found 4 UNION ALL NULL NULL NULL NULL 2 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 3 INTERSECT NULL NULL NULL NULL NULL NULL NULL no matching row in const table NULL INTERSECT RESULT ALL NULL NULL NULL NULL NULL NULL UNION RESULT ALL NULL NULL NULL NULL NULL set sql_mode= 'oracle'; explain SELECT * from t3 union select * from u3 intersect all select * from i3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 0 Const row not found 2 UNION u3 system NULL NULL NULL NULL 0 Const row not found 3 INTERSECT i3 system NULL NULL NULL NULL 0 Const row not found NULL UNIT RESULT ALL NULL NULL NULL NULL NULL select * from t3 union select * from u3 intersect select * from i3; x SELECT * from t3 union select * from u3 intersect all select * from i3; x insert into t3 values (0); insert into i3 values (0); Select * from t3 union select * from u3 intersect select * from i3; x 0 SELECT * FROM t3 UNION SELECT * FROM u3 INTERSECT ALL SELECT * FROM i3; x 0 drop tables t3, u3, i3; # First line of these results is column names, not the result # (pay attention to "affected rows") values (1, 2) union all values (1, 2); 1 2 1 2 1 2 affected rows: 2 values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3); 1 2 1 2 4 3 4 3 affected rows: 3 values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3) union all values (1, 2); 1 2 1 2 4 3 4 3 1 2 affected rows: 4 values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3) union all values (1, 2) union values (1, 2); 1 2 1 2 4 3 affected rows: 2 create table t1 (a int, b int); create table t2 like t1; insert t1 values (1, 2), (1, 2), (1, 2), (2, 3), (2, 3), (3, 4), (3, 4); insert t2 values (1, 2), (1, 2), (2, 3), (2, 3), (2, 3), (2, 3), (4, 5); select * from t1 intersect select * from t2; a b 1 2 2 3 select * from t1 intersect all select * from t2; a b 1 2 2 3 1 2 2 3 # Default: first INTERSECT ALL, then UNION # Oracle: first UNION, then INTERSECT ALL select * from t1 union values (1, 2) intersect all select * from t2; a b 1 2 2 3 select * from t1 union (values (1, 2) intersect all select * from t2); a b 1 2 2 3 3 4 (select * from t1 union values (1, 2)) intersect all select * from t2; a b 1 2 2 3 select * from t1 intersect all select * from t2 union values (1, 2); a b 1 2 2 3 1 2 2 3 select * from t1 intersect all (select * from t2 union values (1, 2)); a b 1 2 2 3 (select * from t1 intersect all select * from t2) union values (1, 2); a b 1 2 2 3 explain select * from t1 intersect all select * from t2 union values (1, 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 7 2 INTERSECT t2 ALL NULL NULL NULL NULL 7 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNIT RESULT ALL NULL NULL NULL NULL NULL drop tables t1, t2; set sql_mode= default;