select 1 union ( select 2 union select 3); 1 1 2 3 explain extended select 1 union ( select 2 union select 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`2` AS `2` from (/* select#2 */ select 2 AS `2` union /* select#3 */ select 3 AS `3`) `__4` select 1 union ( select 1 union select 1); 1 1 explain extended select 1 union ( select 1 union select 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` select 1 union all ( select 1 union select 1); 1 1 1 explain extended select 1 union all ( select 1 union select 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select 1 AS `1` union all /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` select 1 union ( select 1 union all select 1); 1 1 explain extended select 1 union ( select 1 union all select 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` select 1 union select 1 union all select 1; 1 1 1 explain extended select 1 union select 1 union all select 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select 1 AS `1` union /* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1` (select 1 as a) union (select 2) order by a; a 1 2 explain extended (select 1 as a) union (select 2) order by a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 (/* select#1 */ select 1 AS `a`) union (/* select#2 */ select 2 AS `2`) order by `a` /* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; a 1 2 explain extended /* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 /* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a` select 1 union ( select 1 union (select 1 union (select 1 union select 1))); 1 1 explain extended all select 1 union ( select 1 union (select 1 union (select 1 union select 1))); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 8 UNION ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 7 UNION ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 6 UNION ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1/0 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#8/0 */ select `__8`.`1` AS `1` from (/* select#2/1 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#7/1 */ select `__7`.`1` AS `1` from (/* select#3/2 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#6/2 */ select `__6`.`1` AS `1` from (/* select#4/3 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#5/3 */ select 1 AS `1`) `__6`) `__7`) `__8` # # MDEV-6341: INSERT ... SELECT UNION with parenthesis # create table t1 (a int, b int); insert into t1 (select 1,1 union select 2,2); select * from t1 order by 1; a b 1 1 2 2 delete from t1; insert into t1 select 1,1 union select 2,2; select * from t1 order by 1; a b 1 1 2 2 drop table t1; CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2; select * from t1 order by 1; a 1 2 drop table t1; CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2); select * from t1 order by 1; a 1 2 drop table t1; CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a); 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 1 AS `a`) latin1 latin1_swedish_ci drop view v1; CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2; 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 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci drop view v1; CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2); 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 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci drop view v1; # # MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...)) # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10); INSERT INTO t1 VALUES (20); INSERT INTO t1 VALUES (30); ((SELECT a FROM t1) UNION (SELECT a FROM t1)); a 10 20 30 (SELECT * FROM t1 UNION SELECT * FROM t1); a 10 20 30 ((SELECT a FROM t1) LIMIT 1); a 10 SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1; 1 1 2 3 DROP TABLE t1; # # test of several levels of ORDER BY / LIMIT # create table t1 (a int, b int); insert into t1 (a,b) values (1, 100), (2, 200), (3,30), (4,4); select a,b from t1 order by 1 limit 3; a b 1 100 2 200 3 30 (select a,b from t1 order by 1 limit 3) order by 2 limit 2; a b 3 30 1 100 (select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2; 10 1000 3 30 1 100 ((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1; a b 1 100 ((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1; a b 1 100 3 30 drop table t1; # # MDEV-16359: union with 3 selects in brackets # select 1 union select 1 union select 1; 1 1 (select 1 union select 1 union select 1); 1 1 ((select 1) union (select 1) union (select 1)); 1 1 # # MDEV-16357: union in brackets with tail # union with tail in brackets # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES(1),(2),(3),(4); CREATE TABLE t2 (a int); INSERT INTO t2 VALUES (4),(5),(6),(7); (SELECT a FROM t1 UNION SELECT a FROM t2) LIMIT 1; a 1 (SELECT a FROM t1 UNION SELECT a FROM t2) ORDER BY a DESC; a 7 6 5 4 3 2 1 (SELECT a FROM t1 UNION SELECT a FROM t2 LIMIT 1); a 1 DROP TABLE t1,t2; # # MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n # create table t1 (a int); insert into t1 values (10),(20),(30); select a from t1 order by a desc limit 1; a 30 explain extended select a from t1 order by a desc limit 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1 explain format=json select a from t1 order by a desc limit 1; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t1.a desc", "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100 } } } } ] } } (select a from t1 order by a desc) limit 1; a 30 explain extended (select a from t1 order by a desc) limit 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1) explain format=json (select a from t1 order by a desc) limit 1; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t1.a desc", "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100 } } } } ] } } (select a from t1 where a=20 union select a from t1) order by a desc limit 1; a 30 explain extended (select a from t1 where a=20 union select a from t1) order by a desc limit 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 explain format=json (select a from t1 where a=20 union select a from t1) order by a desc limit 1; EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100, "attached_condition": "t1.a = 20" } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100 } } ] } } ] } } } ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; a 30 explain extended ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 explain format=json ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100, "attached_condition": "t1.a = 20" } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100 } } ] } } ] } } } drop table t1; # # MDEV-19363: ((SELECT ...) ORDER BY col ) LIMIT n UNION ... # create table t1 (pk int); insert into t1 values (5),(4),(1),(2),(3); ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); pk 1 2 5 explain extended ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort 2 UNION t1 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` order by `test`.`t1`.`pk` limit 2) union (/* select#2 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where `test`.`t1`.`pk` > 4) explain format=json ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "nested_loop": [ { "read_sorted_file": { "filesort": { "sort_key": "t1.pk", "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100 } } } } ] } }, { "query_block": { "select_id": 2, "operation": "UNION", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 5, "filtered": 100, "attached_condition": "t1.pk > 4" } } ] } } ] } } } drop table t1; # # MDEV-18689: parenthesis around table names and derived tables # select * from ( mysql.db ); Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv create table t1 (a int); insert into t1 values (7), (2), (7); select * from (t1); a 7 2 7 select * from ((t1)); a 7 2 7 select * from (t1 t) where t.a > 5; a 7 7 select * from ((t1 t)) where t.a > 5; a 7 7 select * from ((select a, sum(a) from t1 group by a) t); a sum(a) 2 2 7 14 select * from (((select a, sum(a) from t1 group by a) t)); a sum(a) 2 2 7 14 update (t1 t) set t.a=t.a+1; select * from t1; a 8 3 8 drop table t1; # # MDEV-19956: query expressions in different contexts # create table t1 (a int); insert into t1 values (3), (7), (1), (2), (4); create table t2 (a int, b int); insert into t2 values (3,30), (7,70), (1,10), (2,20), (4,40); # 1. select # 1.1. simple select select * from t1; a 3 7 1 2 4 (select * from t1); a 3 7 1 2 4 ((select * from t1)); a 3 7 1 2 4 # 1.2. select with tail select * from t1 order by a; a 1 2 3 4 7 select a from t1 order by a; a 1 2 3 4 7 select a from t1 order by 1; a 1 2 3 4 7 select * from t1 order by t1.a; a 1 2 3 4 7 (select * from t1 order by t1.a); a 1 2 3 4 7 ((select * from t1 order by t1.a)); a 1 2 3 4 7 (select * from t1 order by t1.a limit 2); a 1 2 (select a from t1 where a=1) order by 1 desc; a 1 # 1.2. select with several tails (select * from t2 order by a limit 2) order by b desc; a b 2 20 1 10 (select * from t2 order by t2.a limit 2) order by b desc; a b 2 20 1 10 ((select * from t2 order by t2.a limit 2) order by b desc); a b 2 20 1 10 (((select * from t2 order by t2.a) limit 2) order by b desc); a b 2 20 1 10 # 2. union # 2.1 simple union select a from t1 union select a from t1; a 3 7 1 2 4 select a from t1 union all select a from t1; a 3 7 1 2 4 3 7 1 2 4 select a from t1 union select b from t2; a 3 7 1 2 4 30 70 10 20 40 (select a from t1) union (select a from t1); a 3 7 1 2 4 (select a from t1) union (select b from t2); a 3 7 1 2 4 30 70 10 20 40 select a from t1 where a=1 union select a from t1 where a=3; a 1 3 (select a from t1 where a=1) union select a from t1 where a=3; a 1 3 ((select a from t1 where a=1) union select a from t1 where a=3); a 1 3 ((select a from t1 where a<=3) union (select a from t1 where a=3)); a 3 1 2 select a from t1 where a=1 union (select a from t1 where a=3); a 1 3 (select a from t1 where a=1 union (select a from t1 where a=3)); a 1 3 ((select a from t1 where a=1 union (select a from t1 where a=3))); a 1 3 select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=7; a 1 3 7 ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=7 ); a 1 3 7 (select a from t1 where a=1 order by a) union select a from t1 where a=3; a 1 3 (select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; a 7 1 2 4 3 ((select a from t1 where a=1 order by a) union select a from t1 where a=3); a 1 3 (select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; a 7 1 2 4 3 ( ( select a from t1 where a!=3 order by a desc limit 3) union select a from t1 where a=3 ); a 7 4 2 3 ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7; a 1 2 7 ( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7 ); a 1 2 7 ( select a from t1 where a <=3 except ( select a from t1 where a >=3 union select a from t1 where a=7 ) ); a 1 2 ( ( select a from t1 where a <=3 ) except ( select a from t1 where a >=3 union select a from t1 where a=7 ) ); a 1 2 # 2.2. union with tail select a from t1 where a=1 union select a from t1 where a=3 order by a desc; a 3 1 (select a from t1 limit 2) union select a from t1 where a=3 order by a desc; a 7 3 select a from t1 where a=4 union (select a from t1 where a <=4 limit 2) order by a desc; a 4 3 1 select a from t1 where a=4 union (select a from t1 where a <=4 order by a limit 2) order by a desc; a 4 2 1 ( select a from t1 where a=4 union ( select a from t1 where a <=4 order by a limit 2 ) ) order by a desc; a 4 2 1 ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7 order by a desc; a 7 2 1 ( select a from t1 where a!=3 order by a desc ) union select a from t1 where a=3 order by a desc; a 7 4 3 2 1 (select a from t1 where a=1) union (select a from t1 where a=3) order by a desc; a 3 1 ( select a from t1 where a=1 union select a from t1 where a=3 ) order by a desc; a 3 1 ( ( select a from t1 where a=1 ) union ( select a from t1 where a=3 ) ) order by a desc; a 3 1 ( select a from t1 where a=1 union select a from t1 where a=3 ) order by 1 desc; a 3 1 ((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc; a 3 1 (((select a from t1 where a=1) union (select a from t1 where a=3))) order by 1 desc; a 3 1 ( (select a from t1 where a=1 ) union (select a from t1 where a=3) ) order by 1 desc; a 3 1 # 2.3. complex union select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 union select a from t1 where a=4; a 1 3 2 4 ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 ) union select a from t1 where a=4; a 1 3 2 4 (select a from t1 where a=1 union select a from t1 where a=3) union (select a from t1 where a=2 union select a from t1 where a=4); a 1 3 2 4 (select a from t1 where a=1 union (select a from t1 where a=3)) union ((select a from t1 where a=2) union select a from t1 where a=4); a 1 3 2 4 ( ( select a from t1 where a=1) union select a from t1 where a=3 ) union select a from t1 where a=2 union select a from t1 where a=4; a 1 3 2 4 ( ( ( select a from t1 where a=1) union select a from t1 where a=3 ) union select a from t1 where a=2 ) union select a from t1 where a=4; a 1 3 2 4 select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 union (select a from t1 where a=4); a 1 3 2 4 select a from t1 where a=1 union select a from t1 where a=3 union ( select a from t1 where a=2 union ( select a from t1 where a=4 ) ); a 1 3 2 4 select a from t1 where a=1 union ( select a from t1 where a=3 union ( select a from t1 where a=2 union ( select a from t1 where a=4 ) ) ); a 1 3 2 4 # 2.4. complex union with tail ( ( select a from t1 where a=1 union select a from t1 where a=3 ) order by a desc ) union ( ( select a from t1 where a=2 union select a from t1 where a=4 ) order by a desc ); a 3 1 4 2 ( ( select a from t1 where a=1 union select a from t1 where a=3 ) order by a desc ) union ( ( select a from t1 where a=2 union select a from t1 where a=4 ) order by a desc ) order by a; a 1 2 3 4 ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 order by a desc limit 2 ) union select a from t1 where a=4 order by a; a 2 3 4 ( select a from t1 where a=1 union select a from t1 where a=3 order by a desc ) union select a from t1 where a=2 order by a desc limit 2; a 3 2 ( ( select a from t1 where a >= 2 union select a from t1 where a=1 order by a desc limit 2 ) union select a from t1 where a=3 order by a limit 2 ) union select a from t1 where a=1; a 3 4 1 # 3. TVC # 3.1. simple TVC values (3), (7), (1); 3 3 7 1 (values (3), (7), (1)); 3 3 7 1 ((values (3), (7), (1))); 3 3 7 1 # 3.2. simple TVC with tail(s) values (3), (7), (1) order by 1; 3 1 3 7 (values (3), (7), (1)) order by 1; 3 1 3 7 ((values (3), (7), (1))) order by 1; 3 1 3 7 (((values (3), (7), (1))) order by 1); 3 1 3 7 (values (3), (7), (1) limit 2) order by 1 desc; 3 7 3 ((values (3), (7), (1)) order by 1 desc) limit 2; 3 7 3 (((values (3), (7), (1)) order by 1 desc) limit 2); 3 7 3 # 3.3. union of TVCs values (3), (7), (1) union values (3), (4), (2); 3 3 7 1 4 2 values (3), (7), (1) union all values (3), (4), (2); 3 3 7 1 3 4 2 values (3), (7), (1) union values (3), (4), (2); 3 3 7 1 4 2 values (3), (7), (1) except values (3), (4), (2); 3 7 1 (values (3), (7), (1)) union (values (3), (4), (2)); 3 3 7 1 4 2 (values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7); 3 3 7 1 4 2 5 (values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7)); 3 3 7 1 4 2 5 (values (3), (7), (1) union values (3), (4), (2)) union values (5), (7); 3 3 7 1 4 2 5 values (3), (7), (1) union (values (3), (4), (2) union values (5), (7)); 3 3 7 1 4 2 5 (values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7)))); 3 3 7 1 4 2 5 # 3.4. tailed union of TVCs values (3), (7), (1) union values (3), (4), (2) order by 1; 3 1 2 3 4 7 (values (3), (7), (1) union values (3), (4), (2)) order by 1; 3 1 2 3 4 7 (values (3), (7), (1) union values (3), (4), (2)) order by 1; 3 1 2 3 4 7 values (3), (7), (1) union (values (3), (4), (2)) order by 1; 3 1 2 3 4 7 (values (3), (7), (1) union values (3), (4), (2)) order by 1; 3 1 2 3 4 7 ((values (3), (7), (1)) union values (3), (4), (2)) order by 1; 3 1 2 3 4 7 # 3.5. union of tailed TVCs (values (3), (7), (1) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2); 3 1 3 4 ((values (3), (7), (1) order by 1) limit 2) union ((values (3), (4), (2) order by 1 desc) limit 2); 3 1 3 4 (((values (3), (7), (1)) order by 1) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2); 3 1 3 4 # 3.6. tailed union of tailed TVCs (values (3), (7), (1) order by 1 limit 2) union values (3), (4), (2) order by 1; 3 1 2 3 4 ((values (3), (7), (1)) order by 1 limit 2) union ((values (3), (4), (2) order by 1 desc) limit 2) order by 1; 3 1 3 4 # 3.7 [tailed] union of [tailed] select and [tailed] TVC (select a from t1 where a <=3 order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2); a 1 2 4 3 ((select a from t1 where a <=3) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2); a 1 2 4 3 (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2); a 1 2 4 3 ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ); a 1 2 4 3 (select a from t1 where a <=3 order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) order by a; a 1 2 3 4 ((select a from t1 where a <=3) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) order by a; a 1 2 3 4 (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) order by a; a 1 2 3 4 (((values (3), (4), (2)) order by 1 desc) limit 2); 3 4 3 ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ) order by a; a 1 2 3 4 (values (3), (4), (2) order by 1 desc limit 2) union (select a from t1 where a <=3 order by 1 limit 2); 3 4 3 1 2 (values (3), (4), (2) order by 1 desc limit 2) union ((select a from t1 where a <=3) order by 1 limit 2); 3 4 3 1 2 (((values (3), (4), (2)) order by 1 desc) limit 2) union (((select a from t1 where a <=3) order by 1) limit 2); 3 4 3 1 2 (((values (3), (4), (2)) order by 1 desc) limit 2) union (((select a from t1 where a <=3) order by a) limit 2) order by 1; 3 1 2 3 4 ( select a from t1 where a=1 union values (3), (4), (2) order by 1 desc ) union select a from t1 where a=2 order by a desc limit 3; a 4 3 2 4. CTE 4.1. simple select with simple CTE with t as (select * from t1 where a <=3) select * from t; a 3 1 2 with t as (select * from t1 where a <=3) (select * from t); a 3 1 2 with t as (select * from t1 where a <=3) ((select * from t)); a 3 1 2 with t as ((select * from t1 where a <=3)) select * from t; a 3 1 2 with t as (((select * from t1 where a <=3))) select * from t; a 3 1 2 4.2. tailed select with simple CTE with t as (select * from t1 where a <=3) select * from t order by a; a 1 2 3 with t as (select * from t1 where a <=3) (select * from t) order by a; a 1 2 3 with t as (select * from t1 where a <=3) (select * from t) order by a desc limit 2; a 3 2 4.3. [tailed] select with tailed CTE with t as (select * from t1 where a >=2 order by a limit 2) select * from t; a 2 3 with t as (((select * from t1 where a >=2) order by a desc) limit 2) select * from t; a 7 4 with t as (select * from t1 where a >=2 order by a desc limit 2) select * from t order by a; a 4 7 4.4. [tailed] union with CTE with t as (select * from t1 where a <=3) select a from t1 where a=1 union select a from t where a=3; a 1 3 with t as (select * from t1 where a <=3) (select a from t) union (select b from t2); a 3 1 2 30 70 10 20 40 with t as (select * from t1 where a <=3) (select a from t) union (select b as a from t2) order by a desc; a 70 40 30 20 10 3 2 1 4.5. [tailed] union with [tailed] union in CTE with t as (select * from t1 where a < 3 union select * from t1 where a > 3) select a from t1 where a=1 union select a from t where a=7; a 1 7 with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7; a 4 7 with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; a 7 4 with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; a 7 4 with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc); a 7 4 with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) ((select a from t1 where a=4 union select a from t where a=7) order by a desc); a 7 4 with t as ( select * from t1 where a < 3 union values (4), (7) order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; a 7 4 4.6. [tailed] union with [tailed] union of TVC in CTE with t(a) as ( values (2), (1) union (values (4), (7)) order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; a 7 4 with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc) order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc; a 7 1 with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc) order by 1 limit 3 ) select a from t where a=1 union values (7) order by a desc; a 7 1 with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc ) ) select a from t where a=1 union select 7 order by a desc; a 7 1 4.5. [tailed] union with two CTEs with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; a 7 1 with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) (select a from t where a=1 union select a from s where a=7 order by a desc); a 7 1 with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) (select a from t where a=1 union select a from s where a=7) order by a desc; a 7 1 with t as (select * from t1 where a < 3), s as (select * from t where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; a 1 # 5. single-row subquery in expression # 5.1. [tailed] simple select in expression select (a+1) + b as r from t2; r 34 78 12 23 45 select ((a+1) + b) as r from t2; r 34 78 12 23 45 select (b + (select 1)) as r from t2; r 31 71 11 21 41 select (select a from t1 where a <=3 order by a desc limit 1) as r from t2; r 3 3 3 3 3 select (select a from t1 where a <=3 order by a desc limit 1) as r from t2; r 3 3 3 3 3 select (select 100) as r from t2; r 100 100 100 100 100 select ((select 100)) as r from t2; r 100 100 100 100 100 select (select 100) + t2.b as r from t2; r 130 170 110 120 140 select ((select 100) + t2.b) as r from t2; r 130 170 110 120 140 # 5.2. [tailed] TVC in expression select (values (200)) as r from t2; r 200 200 200 200 200 select ((values (200))) as r from t2; r 200 200 200 200 200 select (values (200)) + t2.b as r from t2; r 230 270 210 220 240 select ((values (200)) + t2.b) as r from t2; r 230 270 210 220 240 select (values (200), (300) order by 1 desc limit 1) as r from t2; r 300 300 300 300 300 select ((values (200), (300)) order by 1 desc limit 1) as r from t2; r 300 300 300 300 300 select (select * from t1 limit 1) as r from t2; r 3 3 3 3 3 select (select * from t1 order by a limit 1) as r from t2; r 1 1 1 1 1 select ((select * from t1 order by a limit 1)) as r from t2; r 1 1 1 1 1 ((select ((select * from t1 order by a limit 1)) as r from t2)); r 1 1 1 1 1 select (select * from t1 order by a limit 1) + t2.b as r from t2; r 31 71 11 21 41 # 5.3. [tailed] union in expression select ( select a from t1 where a<3 union select a from t1 where a>4 order by a desc limit 1 ) as r from t1; r 7 7 7 7 7 select ( (select a from t1 where a<3) union (select a from t1 where a>4) order by a desc limit 1 ) as r from t1; r 7 7 7 7 7 select ( select a from t1 where a<3 union select a from t1 where a>4 order by a desc limit 1 ) + t1.a as r from t1; r 10 14 8 9 11 select t1.a + ( select a from t1 where a<3 union select a from t1 where a>4 order by a desc limit 1 ) as r from t1; r 10 14 8 9 11 select ( (select a from t1 where a<3 union select a from t1 where a>4 order by a desc limit 1 ) + t1.a) as r from t1; r 10 14 8 9 11 select ( ( (select a from t1 where a<3) union (select a from t1 where a>4) order by a desc limit 1 ) + t1.a ) as r from t1; r 10 14 8 9 11 # 5.4. [tailed] select with simple CTE in expression select ( with t as (select * from t1 where a <=3) select a from t limit 1) as r from t2; r 3 3 3 3 3 select ( with t as (select * from t1 where a <=3) select a from t limit 1) + t2.b as r from t2; r 33 73 13 23 43 select t2.b +( with t as (select * from t1 where a <=3) select a from t limit 1) as r from t2; r 33 73 13 23 43 select ((( with t as (select * from t1 where a <=3) select a from t limit 1) + t2.b)) as r from t2; r 33 73 13 23 43 select ( with t as (select * from t1 where a <=3) select a from t limit 1) + 100 as r from t2; r 103 103 103 103 103 select ( with t as (select * from t1 where a <=3) select a from t limit 1) + (select 100) as r from t2; r 103 103 103 103 103 select ( with t as (select * from t1 where a <=3) select a from t limit 1) + t2.b + (select 100) as r from t2; r 133 173 113 123 143 select ( with t as (select * from t1 where a <=3) select a from t limit 1 ) + (t2.b + (select 100)) as r from t2; r 133 173 113 123 143 select ( with t as (select * from t1 where a <=3) select a from t limit 1 ) + t2.b + (values (100)) as r from t2; r 133 173 113 123 143 # 5.5. [tailed] union with simple CTE in expression select ( with t as (select * from t1 where a <=3) select a from t union select b from t2 order by a desc limit 1) as r from t2; r 70 70 70 70 70 select ( with t as (select * from t1 where a <=3) (select a from t) union (select b from t2) order by a desc limit 1) as r from t2; r 70 70 70 70 70 select ( with t as (select * from t1 where a <=3) (select a from t) union (select b from t2) order by a desc limit 1) as r from t2; r 70 70 70 70 70 select ( ( with t as (select * from t1 where a <=3) (select a from t) union (select b from t2) order by a desc limit 1) + t2.a ) as r from t2; r 73 77 71 72 74 # 5.6. [tailed] union with CTE with union in expression select ( with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 limit 1) as r from t2; r 4 4 4 4 4 select ( with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 limit 1) + t2. b as r from t2; r 34 74 14 24 44 # 5.7. [tailed] union of TVCs with CTE with union in expression select ( with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 limit 1) order by 1 desc limit 3 ) select * from t limit 1 ) + t2.b as r from t2; r 34 74 14 24 44 select ( with t(a) as ( select 2 union select 1 union (values (4), (7) order by 1 limit 1) order by 1 limit 3 ) select * from t limit 1 ) + t2.b as r from t2; r 31 71 11 21 41 # 6. subquery # 6.1. TVC in IN subquery select a from t1 where a in (1,8,7); a 7 1 select a from t1 where a in (values (1), (8), (7)); a 7 1 # 6.2. simple select in IN subquery select a from t1 where a in (select a from t2 where a <= 3); a 3 1 2 select a from t1 where a in ((select a from t2 where a <= 3)); a 3 1 2 # 6.3. union in IN subquery select a from t1 where a in (select a from t1 where a<=2 union select a from t2 where b>40); a 7 1 2 select a from t1 where a in (select a from t1 where a<=2 union (select a from t2 where b>40)); a 7 1 2 select a from t1 where a in ((select a from t1 where a<=2) union select a from t2 where b>40); a 7 1 2 select a from t1 where a in ((select a from t1 where a<=2) union (select a from t2 where b>40)); a 7 1 2 # 6.4. select with CTE and union in IN subquery with t as (select a from t1 where a<=2) select a from t1 where a in ((select a from t) union (select a from t2 where b>40)); a 7 1 2 with t as ((select a from t1 where a<=2)) select a from t1 where a in ((select a from t) union (select a from t2 where b>40)); a 7 1 2 with t as ((select a from t1 where a<=2) order by a desc limit 1) select a from t1 where a in ((select a from t) union (select a from t2 where b>40)); a 7 2 # 6.5. NOT IN subquery select a from t1 where a not in (1,8,7); a 3 2 4 select a from t1 where a not in (values (1), (8), (7)); a 3 2 4 select a from t1 where a not in (select a from t2 where a <= 3); a 7 4 select a from t1 where a not in ((select a from t2 where a <= 3)); a 7 4 select a from t1 where a not in (select a from t1 where a<=2 union select a from t2 where b>40); a 3 4 select a from t1 where a not in (select a from t1 where a<=2 union (select a from t2 where b>40)); a 3 4 select a from t1 where a not in ((select a from t1 where a<=2) union select a from t2 where b>40); a 3 4 select a from t1 where a not in ((select a from t1 where a<=2) union (select a from t2 where b>40)); a 3 4 with t as ((select a from t1 where a<=2) order by a desc limit 1) select a from t1 where a not in ((select a from t) union (select a from t2 where b>40)); a 3 1 4 # 6.6. IN subquery in expression select 1 in (select a from t1) as r, b from t2 where b > 30; r b 1 70 1 40 select (1 in (select a from t1)) as r, b from t2 where b > 30; r b 1 70 1 40 select 1 in ((select a from t1)) as r, b from t2 where b > 30; r b 1 70 1 40 select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; r b 1 70 1 40 select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; r b 1 70 1 40 select b, if (a in (select a from t1 where a > 3),10,20) as r from t2; b r 30 20 70 10 10 20 20 20 40 10 select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2; b r 30 20 70 10 10 20 20 20 40 10 # 6.7. IN subquery in SF and SP create function f1(x int) returns int return (x in ((select a from t1 where a <= 4))); select b, f1(a) from t2 where b > 20; b f1(a) 30 1 70 0 40 1 drop function f1; create function f2(x int) returns int if x in ((select a from t1 where a <= 4)) then return 100; else return 200; end if | select b, f2(a) from t2 where b > 20; b f2(a) 30 100 70 200 40 100 drop function f2; # 6.8. EXISTS subquery select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30; r b 1 70 1 40 select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30; r b 1 70 1 40 with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from t2 where exists ((select * from s where s.a=t2.a)); a b 3 30 7 70 4 40 with t as ((select a from t1 where a<=2) order by a desc limit 1) select a from t2 where not exists ((select a from t where t.a=t2.a) except (select a from t where a>40)); a 3 7 1 4 # 6.9. EXISTS subquery with SF and SP create function f1(x int) returns int return exists (((select * from t1 where x=a and a <= 4))); select b, f1(a) from t2 where b > 20; b f1(a) 30 1 70 0 40 1 drop function f1; create function f2(x int) returns int if not exists (((select * from t1 where x=a and a <= 4))) then return 100; else return 200; end if | select b, f2(a) from t2 where b > 20; b f2(a) 30 200 70 100 40 200 drop function f2; # 6.10. subquery with ANY select a from t1 where a = any(select a from t2 where a <= 3); a 3 1 2 select a from t1 where a = any((select a from t2 where a <= 3)); a 3 1 2 select a from t1 where a = any (select a from t1 where a<=2 union select a from t2 where b>40); a 7 1 2 select a from t1 where a = any(select a from t1 where a<=2 union (select a from t2 where b>40)); a 7 1 2 select a from t1 where a = any((select a from t1 where a<=2) union select a from t2 where b>40); a 7 1 2 select a from t1 where a = any((select a from t1 where a<=2) union (select a from t2 where b>40)); a 7 1 2 # 7. create table as # 7.1. create table as simple select create table t as select * from t1 where a <=3; select * from t; a 3 1 2 drop table t; create table t select * from t1 where a <=3; select * from t; a 3 1 2 drop table t; create table t as (select * from t1 where a <=3); select * from t; a 3 1 2 drop table t; create table t (select * from t1 where a <=3); select * from t; a 3 1 2 drop table t; create table t as ((select * from t1 where a <=3)); select * from t; a 3 1 2 drop table t; create table t ((select * from t1 where a <=3)); select * from t; a 3 1 2 drop table t; create table t(a decimal(10,2)) as select * from t1 where a <=3; select * from t; a 3.00 1.00 2.00 drop table t; create table t(a decimal(10,2)) select * from t1 where a <=3; select * from t; a 3.00 1.00 2.00 drop table t; create table t(a decimal(10,2)) as (select * from t1 where a <=3); select * from t; a 3.00 1.00 2.00 drop table t; create table t(a decimal(10,2)) (select * from t1 where a <=3); select * from t; a 3.00 1.00 2.00 drop table t; create table t(a decimal(10,2)) as ((select * from t1 where a <=3)); select * from t; a 3.00 1.00 2.00 drop table t; create table t(a decimal(10,2)) ((select * from t1 where a <=3)); select * from t; a 3.00 1.00 2.00 drop table t; create table t(a decimal(10,2), b int) as ((select a, a as b from t1 where a <=3)); select * from t; a b 3.00 3 1.00 1 2.00 2 drop table t; create table t(a decimal(10,2), b int) ((select a, a as b from t1 where a <=3)); select * from t; a b 3.00 3 1.00 1 2.00 2 drop table t; # 7.2. create table as tailed select create table t as select * from t1 where a <=3 order by 1; select * from t; a 1 2 3 drop table t; create table t select * from t1 where a <=3 order by 1; select * from t; a 1 2 3 drop table t; create table t as select * from t1 where a <=3 order by 1 desc limit 2; select * from t; a 3 2 drop table t; create table t select * from t1 where a <=3 order by 1 desc limit 2; select * from t; a 3 2 drop table t; create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2; select * from t; a 3 2 drop table t; create table t ((select * from t1 where a <=3) order by 1 desc) limit 2; select * from t; a 3 2 drop table t; # 7.3. create table as select wihout from clause create table t as select 10; select * from t; 10 10 drop table t; create table t select 10; select * from t; 10 10 drop table t; # 7.4. create table as union of selects wihout from clause create table t as select 10 union select 70; select * from t; 10 10 70 drop table t; create table t select 10 union select 70; select * from t; 10 10 70 drop table t; # 7.5. create table as TVC create table t as values (7), (3), (8); select * from t; 7 7 3 8 drop table t; create table t values (7), (3), (8); select * from t; 7 7 3 8 drop table t; create table t as (values (7), (3), (8)); select * from t; 7 7 3 8 drop table t; create table t (values (7), (3), (8)); select * from t; 7 7 3 8 drop table t; create table t as ((values (7), (3), (8))); select * from t; 7 7 3 8 drop table t; create table t ((values (7), (3), (8))); select * from t; 7 7 3 8 drop table t; # 7.6. create table as select with CTE create table t as with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) select * from s; select * from t; a 3 2 drop table t; create table t with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) select * from s; select * from t; a 3 2 drop table t; create table t as with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s; select * from t; a 4 3 8 7 drop table t; create table t with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s; select * from t; a 4 3 8 7 drop table t; create table t as with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) select * from s; select * from t; a 3 2 drop table t; # 7.7. create table as union with CTE create table t as with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select a from t2 where b<40; select * from t; a 8 7 3 1 2 drop table t; create table t with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select a from t2 where b<40; select * from t; a 8 7 3 1 2 drop table t; create table t with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select a from t2 where b<40; select * from t; a 8 7 3 1 2 drop table t; create table t as with s as ( ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) ) select * from s where a>=7 union select a from t2 where b<40; select * from t; a 8 7 3 1 2 drop table t; create table t with s as ( ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) ) select * from s where a>=7 union select a from t2 where b<40; select * from t; a 8 7 3 1 2 drop table t; create table t as with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select a from s where a<4; select * from t; a 8 7 3 drop table t; create table t with s as ( (select * from t1 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select a from s where a<4; select * from t; a 8 7 3 drop table t; create table t as with s as ( select * from t1 where a <=4 or a=7 ) select * from s where a>=7 union select a from s where a<3; select * from t; a 7 1 2 drop table t; create table t with s as (select * from t1 where a <=4 or a=7) select * from s where a>=7 union select a from s where a<3; select * from t; a 7 1 2 drop table t; create table t (a int) with s as ( select * from t1 where a <=4 or a=7 ) select * from s where a>=7 union select a from s where a<3; select * from t; a 7 1 2 drop table t; create table t (a int) with s as (select * from t1 where a <=4 or a=7) select * from s where a>=7 union select a from s where a<3; select * from t; a 7 1 2 drop table t; create table t with s as ( select * from t1 where a <=4 or a=7 ) select * from s where a>=7 union select a from s where a<3 order by a desc limit 2; select * from t; a 7 2 drop table t; create table t ( with s as ( select * from t1 where a <=4 or a=7 ) select * from s where a>=7 union select a from s where a<3 order by a desc limit 2 ); select * from t; a 7 2 drop table t; # 8. insert create table t (c int, d int); # 8.1. insert simple select insert into t select * from t2 where a <=3; select * from t; c d 3 30 1 10 2 20 delete from t; insert into t(c) select t2.a from t2 where a <=3; select * from t; c d 3 NULL 1 NULL 2 NULL delete from t; insert into t (select * from t2 where a <=3); select * from t; c d 3 30 1 10 2 20 delete from t; insert into t(c) (select t2.a from t2 where a <=3); select * from t; c d 3 NULL 1 NULL 2 NULL delete from t; insert into t ((select * from t2 where a <=3)); select * from t; c d 3 30 1 10 2 20 delete from t; insert into t(c) ((select t2.a from t2 where a <=3)); select * from t; c d 3 NULL 1 NULL 2 NULL delete from t; drop table t; create table t(c decimal(10,2)); insert into t select * from t1 where a <=3; select * from t; c 3.00 1.00 2.00 delete from t; insert into t(c) select * from t1 where a <=3; select * from t; c 3.00 1.00 2.00 delete from t; insert into t (select * from t1 where a <=3); select * from t; c 3.00 1.00 2.00 delete from t; insert into t(c) (select * from t1 where a <=3); select * from t; c 3.00 1.00 2.00 delete from t; insert into t ((select * from t1 where a <=3)); select * from t; c 3.00 1.00 2.00 delete from t; insert into t(c) ((select * from t1 where a <=3)); select * from t; c 3.00 1.00 2.00 delete from t; drop table t; create table t(a decimal(10,2), b int); insert into t ((select * from t2 where a <=3)); select * from t; a b 3.00 30 1.00 10 2.00 20 delete from t; insert into t(a) ((select a from t2 where a <=3)); select * from t; a b 3.00 NULL 1.00 NULL 2.00 NULL delete from t; drop table t; create table t(c int, d int); # 8.2. insert tailed select insert into t select * from t2 where a <=3 order by 1; select * from t; c d 1 10 2 20 3 30 delete from t; insert into t(c) select a from t2 where a <=3 order by 1; select * from t; c d 1 NULL 2 NULL 3 NULL delete from t; insert into t select * from t2 where a <=3 order by 1 desc limit 2; select * from t; c d 3 30 2 20 delete from t; insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2; select * from t; c d 3 NULL 2 NULL delete from t; insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2; select * from t; c d 3 30 2 20 delete from t; insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2; select * from t; c d 3 NULL 2 NULL delete from t; # 8.3. insert select without from clause insert into t select 10, 20; select * from t; c d 10 20 delete from t; insert into t(c) select 10; select * from t; c d 10 NULL delete from t; # 8.4. insert union of selects without from clause insert into t select 10,20 union select 70,80; select * from t; c d 10 20 70 80 delete from t; insert into t(c) select 10 union select 70; select * from t; c d 10 NULL 70 NULL delete from t; # 8.5. insert TVC insert into t values (7,70), (3,30), (8,80); select * from t; c d 7 70 3 30 8 80 delete from t; insert into t(c) values (7), (3), (8); select * from t; c d 7 NULL 3 NULL 8 NULL delete from t; insert into t (values (7,70), (3,30), (8,80)); select * from t; c d 7 70 3 30 8 80 delete from t; insert into t(c) (values (7), (3), (8)); select * from t; c d 7 NULL 3 NULL 8 NULL delete from t; insert into t ((values (7,70), (3,30), (8,80))); select * from t; c d 7 70 3 30 8 80 delete from t; insert into t(c) ((values (7), (3), (8))); select * from t; c d 7 NULL 3 NULL 8 NULL delete from t; # 8.7. insert simple select with CTE insert into t with s(a,b) as (select * from t2 where a <=3 order by 1 desc limit 2) select * from s; select * from t; c d 3 30 2 20 delete from t; insert into t(c) with s(a) as (select a from t2 where a <=3 order by 1 desc limit 2) select * from s; select * from t; c d 3 NULL 2 NULL delete from t; insert into t with s as ( (select * from t2 where a <=4 order by 1 desc limit 2) union values (3,30), (8,80), (7,70) ) select * from s; select * from t; c d 4 40 3 30 8 80 7 70 delete from t; insert into t(c) with s as ( (select a from t2 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s; select * from t; c d 4 NULL 3 NULL 8 NULL 7 NULL delete from t; # 8.8. insert into union with CTE insert into t(c) with s as ( (select a from t2 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select a from t2 where b<40; select * from t; c d 8 NULL 7 NULL 3 NULL 1 NULL 2 NULL delete from t; insert into t with s as ( (select * from t2 where a <=4 order by 1 desc limit 2) union values (3,30), (8,80), (7,70) ) select * from s where a>=7 union select * from s where a<4; select * from t; c d 8 80 7 70 3 30 delete from t; insert into t(c) with s as ( (select a from t2 where a <=4 order by 1 desc limit 2) union values (3), (8), (7) ) select * from s where a>=7 union select * from s where a<4; select * from t; c d 8 NULL 7 NULL 3 NULL delete from t; insert into t with s as ( select * from t2 where a <=4 or a=7 ) select * from s where a>=7 union select * from s where a<3; select * from t; c d 7 70 1 10 2 20 delete from t; insert into t(c) with s as ( select a from t2 where a <=4 or a=7 ) select * from s where a>=7 union select * from s where a<3; select * from t; c d 7 NULL 1 NULL 2 NULL delete from t; drop table t; # 9. derived table # 9.1. derived table as [tailed] simple select select * from (select * from t1) as dt; a 3 7 1 2 4 select * from ((select * from t1)) as dt; a 3 7 1 2 4 select * from (((select * from t1))) as dt; a 3 7 1 2 4 select * from (select * from t1 order by a) as dt; a 3 7 1 2 4 select * from (select a from t1 order by a) as dt; a 3 7 1 2 4 select * from (select a from t1 order by 1) as dt; a 3 7 1 2 4 select * from (select a from t1 order by t1.a) as dt; a 3 7 1 2 4 select * from ((select * from t1 order by t1.a limit 2)) as dt; a 1 2 select * from ((select * from t2 order by a limit 2) order by b desc) dt; a b 1 10 2 20 select * from ((select a from t1 where a=1) order by 1 desc) dt; a 1 # 9.2. derived table as select with two tails select * from ((select * from t2 order by t2.a limit 2) order by b desc) dt; a b 1 10 2 20 select * from ((select * from t2 order by t2.a limit 2) order by b desc) as dt; a b 1 10 2 20 select * from (((select * from t2 order by t2.a limit 2) order by b desc )) as dt; a b 1 10 2 20 select * from (((select * from t2 order by t2.a) limit 2) order by b desc) dt; a b 1 10 2 20 select * from ((select * from t2 order by a limit 2) order by b desc) dt; a b 1 10 2 20 select * from ((select a from t1 where a=1) order by 1 desc) as dt; a 1 select * from ((select * from t2 order by t2.a limit 2) order by b desc) as dt; a b 1 10 2 20 # 9.3. derived table as union select * from (select a from t1 union select a from t1) as dt; a 3 7 1 2 4 select * from (select a from t1 union all select a from t1) as dt; a 3 7 1 2 4 3 7 1 2 4 select * from (select a from t1 union select b from t2) as dt; a 3 7 1 2 4 30 70 10 20 40 select * from ((select a from t1) union (select a from t1)) as dt; a 3 7 1 2 4 select * from ((select a from t1) union (select b from t2)) as dt; a 3 7 1 2 4 30 70 10 20 40 select * from (select a from t1 where a=1 union select a from t1 where a=3) dt; a 1 3 select * from ((select a from t1 where a=1) union select a from t1 where a=3) dt; a 1 3 select * from (((select a from t1 where a=1) union select a from t1 where a=3)) dt; a 1 3 select * from (((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt; a 3 1 2 select * from (select a from t1 where a=1 union (select a from t1 where a=3)) as dt; a 1 3 select * from ((select a from t1 where a=1 union (select a from t1 where a=3))) as dt; a 1 3 select * from (((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt; a 1 3 select * from ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=7 ) as dt; a 1 3 7 select * from ( (select a from t1 where a=1 order by a) union select a from t1 where a=3 ) as dt; a 1 3 select * from ( (select a from t1 where a!=3 order by a desc) union select a from t1 where a=3 ) as dt; a 7 1 2 4 3 select * from ( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7 ) as dt; a 1 2 7 select * from ( ( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7 ) ) as dt; a 1 2 7 select * from ( select a from t1 where a=1 union select a from t1 where a=3 order by a desc) as dt; a 3 1 select *from ( (select a from t1 limit 2) union select a from t1 where a=3 order by a desc) as dt; a 7 3 select * from ( select a from t1 where a=4 union (select a from t1 where a <=4 limit 2) order by a desc ) as dt; a 4 3 1 select * from ( ( select a from t1 where a=4 union ( select a from t1 where a <=4 order by a ) ) order by a desc limit 2 ) as dt; a 4 3 select * from ( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7 order by a desc ) as dt; a 7 2 1 select * from ( ( select a from t1 where a!=3 order by a desc ) union select a from t1 where a=3 order by a desc ) as dt; a 7 4 3 2 1 select * from ( (select a from t1 where a=1) union (select a from t1 where a=3) order by a desc ) as dt; a 3 1 select * from ( ( select a from t1 where a=1 union select a from t1 where a=3 ) order by a desc ) as dt; a 3 1 select * from ( ( ( select a from t1 where a=1 ) union ( select a from t1 where a=3 ) ) order by a desc ) as dt; a 3 1 select * from ( ( select a from t1 where a=1 union select a from t1 where a=3 ) order by 1 desc ) as dt; a 3 1 select * from ( ( (select a from t1 where a=1 union select a from t1 where a=3) ) order by 1 desc ) as dt; a 3 1 select * from ((((select a from t1 where a=1) union (select a from t1 where a=3))) order by 1 desc ) as dt; a 3 1 select * from ( ( (select a from t1 where a=1 ) union (select a from t1 where a=3) ) order by 1 desc ) as dt; a 3 1 select * from ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 union select a from t1 where a=4 ) as dt; a 1 3 2 4 select * from ( ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 ) union select a from t1 where a=4 ) as dt; a 1 3 2 4 select * from ( (select a from t1 where a=1 union select a from t1 where a=3) union (select a from t1 where a=2 union select a from t1 where a=4) ) as dt; a 1 3 2 4 select * from ( (select a from t1 where a=1 union (select a from t1 where a=3)) union ((select a from t1 where a=2) union select a from t1 where a=4) ) as dt; a 1 3 2 4 select * from ( ( ( select a from t1 where a=1) union select a from t1 where a=3 ) union select a from t1 where a=2 union select a from t1 where a=4 ) as dt; a 1 3 2 4 select * from ( ( ( ( select a from t1 where a=1) union select a from t1 where a=3 ) union select a from t1 where a=2 ) union select a from t1 where a=4 ) as dt; a 1 3 2 4 select * from ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 union (select a from t1 where a=4) ) as dt; a 1 3 2 4 select * from ( select a from t1 where a=1 union select a from t1 where a=3 union ( select a from t1 where a=2 union ( select a from t1 where a=4 ) ) ) as dt; a 1 3 2 4 select * from ( select a from t1 where a=1 union ( select a from t1 where a=3 union ( select a from t1 where a=2 union ( select a from t1 where a=4 ) ) ) ) as dt; a 1 3 2 4 select * from ( ( ( select a from t1 where a=1 union select a from t1 where a=3 ) order by a desc limit 2 ) union ( ( select a from t1 where a=2 union select a from t1 where a=4 ) order by a desc limit 1 ) ) as dt; a 3 1 4 select * from ( ( ( select a from t1 where a=1 union select a from t1 where a=3 ) order by a desc limit 2 ) union ( ( select a from t1 where a=2 union select a from t1 where a=4 ) order by a desc limit 2 ) order by a) as dt; a 1 2 3 4 select * from ( ( select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=2 order by a desc limit 2 ) union select a from t1 where a=4 order by a limit 3 ) as dt; a 2 3 4 select * from ( ( select a from t1 where a=1 union select a from t1 where a=3 order by a desc limit 2) union select a from t1 where a=2 order by a desc limit 2 ) as dt; a 3 2 select * from ( ( ( select a from t1 where a >= 2 union select a from t1 where a=1 order by a desc limit 2 ) union select a from t1 where a=3 order by a limit 2 ) union select a from t1 where a=1 ) as dt; a 3 4 1 # 9.3. derived table as [tailed] TVC select * from ( values (3), (7), (1) ) as dt; 3 3 7 1 select * from ( (values (3), (7), (1)) ) as dt; 3 3 7 1 select * from (((values (3), (7), (1)))) as dt; 3 3 7 1 select * from ( values (3), (7), (1) order by 1 limit 2 ) as dt; 3 1 3 select * from ( (values (3), (7), (1)) order by 1 limit 2 ) as dt; 3 1 3 select * from ( ((values (3), (7), (1))) order by 1 limit 2 ) as dt; 3 1 3 select * from ( (((values (3), (7), (1))) order by 1 limit 2) ) as dt; 3 1 3 select * from ( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt; 3 3 7 select * from ( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt; 3 7 3 select * from ( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt; 3 7 3 # 9.3. derived table as union of TVCs select * from ( values (3), (7), (1) union values (3), (4), (2) ) dt; 3 3 7 1 4 2 select * from ( values (3), (7), (1) union all values (3), (4), (2) ) as dt; 3 3 7 1 3 4 2 select * from ( values (3), (7), (1) union values (3), (4), (2) ) as dt; 3 3 7 1 4 2 select * from ( values (3), (7), (1) except values (3), (4), (2) ) as dt; 3 7 1 select * from ( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt; 3 3 7 1 4 2 select * from ( (values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7) ) dt; 3 3 7 1 4 2 5 select * from ( (values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7)) ) as dt; 3 3 7 1 4 2 5 select * from ( (values (3), (7), (1) union values (3), (4), (2)) union values (5), (7) ) as dt; 3 3 7 1 4 2 5 select * from ( values (3), (7), (1) union (values (3), (4), (2) union values (5), (7)) ) as dt; 3 3 7 1 4 2 5 select * from ( (values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7)))) ) dt; 3 3 7 1 4 2 5 select * from ( values (3), (7), (1) union values (3), (4), (2) order by 1 ) as dt; 3 1 2 3 4 7 select * from ( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; 3 1 2 3 4 7 select * from ( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; 3 1 2 3 4 7 select * from ( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt; 3 1 2 3 4 7 select * from ( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; 3 1 2 3 4 7 select * from ( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt; 3 1 2 3 4 7 select * from ( (values (3), (7), (1) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) ) as dt; 3 1 3 4 select * from ( ((values (3), (7), (1) order by 1) limit 2) union ((values (3), (4), (2) order by 1 desc) limit 2) ) as dt; 3 1 3 4 select * from ( (((values (3), (7), (1)) order by 1) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; 3 1 3 4 select * from ( (values (3), (7), (1) order by 1 limit 2) union values (3), (4), (2) order by 1 limit 3 ) as dt; 3 1 2 3 select * from ( ((values (3), (7), (1)) order by 1 limit 2) union ((values (3), (4), (2) order by 1 desc) limit 2) order by 1 limit 3 ) as dt; 3 1 3 4 select * from ( (select a from t1 where a <=3 order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) ) dt; a 1 2 4 3 select * from ( ((select a from t1 where a <=3) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) ) as dt; a 1 2 4 3 select * from ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; a 1 2 4 3 select * from ( ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ) ) dt; a 1 2 4 3 select * from ( (select a from t1 where a <=3 order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) order by a ) as dt; a 1 2 3 4 select * from ( ((select a from t1 where a <=3) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2) order by a ) as dt; a 1 2 3 4 select * from ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) order by a ) as dt; a 1 2 3 4 select * from ( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; 3 4 3 select * from ( ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ) order by a ) as dt; a 1 2 3 4 select * from ( (values (3), (4), (2) order by 1 desc limit 2) union (select a from t1 where a <=3 order by 1 limit 2) ) as dt; 3 4 3 1 2 select * from ( (values (3), (4), (2) order by 1 desc limit 2) union ((select a from t1 where a <=3) order by 1 limit 2) ) as dt; 3 4 3 1 2 select * from ( (((values (3), (4), (2)) order by 1 desc) limit 2) union (((select a from t1 where a <=3) order by 1) limit 2) ) as dt; 3 4 3 1 2 select * from ( (((values (3), (4), (2)) order by 1 desc) limit 2) union (((select a from t1 where a <=3) order by a) limit 2) order by 1 ) as dt; 3 1 2 3 4 select * from ( ( select a from t1 where a=1 union values (3), (4), (2) order by 1 desc ) union select a from t1 where a=2 order by a desc limit 3 ) as dt; a 4 3 2 # 9.4. derived table as [tailed] simple select with CTE select * from ( with t as (select * from t1 where a <=3) select * from t ) as dt; a 3 1 2 select * from ( with t as (select * from t1 where a <=3) (select * from t) ) as dt; a 3 1 2 select * from ( with t as (select * from t1 where a <=3) ((select * from t)) ) as dt; a 3 1 2 select * from ( with t as ((select * from t1 where a <=3)) select * from t ) as dt; a 3 1 2 select * from ( with t as (((select * from t1 where a <=3))) select * from t ) as dt; a 3 1 2 select * from ( with t as (select * from t1 where a <=3) select * from t order by a ) as dt; a 3 1 2 select * from ( with t as (select * from t1 where a <=3) (select * from t) order by a ) as dt; a 3 1 2 select * from ( with t as (select * from t1 where a <=3) (select * from t) order by a desc limit 2 ) as dt; a 3 2 select * from ( with t as (select * from t1 where a >=2 order by a limit 2) select * from t ) as dt; a 2 3 select * from ( with t as (((select * from t1 where a >=2) order by a desc) limit 2) select * from t ) as dt; a 7 4 select * from ( with t as (select * from t1 where a >=2 order by a desc limit 2) select * from t order by a ) as dt; a 7 4 # 9.5. derived table as tailed union with CTE select * from ( with t as (select * from t1 where a <=3) select a from t1 where a=1 union select a from t where a=3 ) as dt; a 1 3 select * from ( with t as (select * from t1 where a <=3) (select a from t) union (select b from t2) ) as dt; a 3 1 2 30 70 10 20 40 select * from ( with t as (select * from t1 where a <=3) (select a from t) union (select b as a from t2) order by a desc ) as dt; a 70 40 30 20 10 3 2 1 select * from ( with t as (select * from t1 where a < 3 union select * from t1 where a > 3) select a from t1 where a=1 union select a from t where a=7 ) as dt; a 1 7 select * from ( with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 ) as dt; a 4 7 select * from ( with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc ) as dt; a 7 4 select * from ( with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc ) dt; a 7 4 select * from ( with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc) ) as dt; a 7 4 select * from ( with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) ((select a from t1 where a=4 union select a from t where a=7) order by a desc) ) as dt; a 7 4 select * from ( with t as ( select * from t1 where a < 3 union values (4), (7) order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc ) dt; a 7 4 select * from ( with t(a) as ( values (2), (1) union (values (4), (7)) order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc ) as dt; a 7 4 select * from ( with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc) order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc ) as dt; a 7 1 select * from ( with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc) order by 1 limit 3 ) select a from t where a=1 union values (7) order by a desc ) as dt; a 7 1 select * from ( with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc ) ) select a from t where a=1 union select 7 order by a desc ) as dt; a 7 1 select * from ( with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc ) dt; a 7 1 select * from ( with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) (select a from t where a=1 union select a from s where a=7 order by a desc) ) dt; a 7 1 select * from ( with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) (select a from t where a=1 union select a from s where a=7) order by a desc ) dt; a 7 1 10. view 10.1. view as simple select create view v1 as select * from t1; 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` from `t1` latin1 latin1_swedish_ci select * from v1; a 3 7 1 2 4 drop view v1; create view v1 as select 2*a as c from t1; 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 2 * `t1`.`a` AS `c` from `t1` latin1 latin1_swedish_ci select * from v1; c 6 14 2 4 8 drop view v1; create view v1(c) as select 2*a from t1; 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 2 * `t1`.`a` AS `c` from `t1` latin1 latin1_swedish_ci select * from v1; c 6 14 2 4 8 drop view v1; create view v1 as ((select * from t1)); 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` from `t1`) latin1 latin1_swedish_ci select * from v1; a 3 7 1 2 4 drop view v1; 10.2. view as tailed simple select create view v1 as select * from t1 order by a; 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` from `t1` order by `t1`.`a` latin1 latin1_swedish_ci select * from v1; a 1 2 3 4 7 drop view v1; create view v1 as (select * from t2 order by a limit 2) order by b desc; 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 `__3`.`a` AS `a`,`__3`.`b` AS `b` from (select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` order by `t2`.`a` limit 2) `__3` order by `__3`.`b` desc latin1 latin1_swedish_ci select * from v1; a b 2 20 1 10 drop view v1; 10.3. view as union create view v1 as select a from t1 union select b from t2; 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` from `t1` union select `t2`.`b` AS `b` from `t2` latin1 latin1_swedish_ci select * from v1; a 3 7 1 2 4 30 70 10 20 40 drop view v1; create view v1 as (select a from t1) union (select b from t2); 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` from `t1`) union (select `t2`.`b` AS `b` from `t2`) latin1 latin1_swedish_ci select * from v1; a 3 7 1 2 4 30 70 10 20 40 drop view v1; create view v1 as (select a from t1 where a=1) union select a from t1 where a=3; 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` from `t1` where `t1`.`a` = 1) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 latin1 latin1_swedish_ci select * from v1; a 1 3 drop view v1; create view v1 as ((select a from t1 where a<=3) union (select a from t1 where a=3)); 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` from `t1` where `t1`.`a` <= 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3) latin1 latin1_swedish_ci select * from v1; a 3 1 2 drop view v1; create view v1 as select a from t1 where a=1 union select a from t1 where a=3 union select a from t1 where a=7; 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` from `t1` where `t1`.`a` = 1 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 1 3 7 drop view v1; create view v1 as ( ( select a from t1 where a!=3 order by a desc limit 3) union select a from t1 where a=3 ); 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` from `t1` where `t1`.`a` <> 3 order by `t1`.`a` desc limit 3) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 latin1 latin1_swedish_ci select * from v1; a 7 4 2 3 drop view v1; create view v1 as ( select a from t1 where a <=3 except select a from t1 where a >=3 ) union select a from t1 where a=7; 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 `__5`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3 except select `t1`.`a` AS `a` from `t1` where `t1`.`a` >= 3) `__5` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 1 2 7 drop view v1; create view v1 as (select a from t1 limit 2) union select a from t1 where a=3 order by a desc; 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` from `t1` limit 2) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 3 drop view v1; create view v1 as select a from t1 where a=1 union ( select a from t1 where a=3 union ( select a from t1 where a=2 union ( select a from t1 where a=4 ) ) ); 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` from `t1` where `t1`.`a` = 1 union select `__7`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 union select `__6`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 2 union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4)) `__6`) `__7` latin1 latin1_swedish_ci select * from v1; a 1 3 2 4 drop view v1; create view v1 as ( ( select a from t1 where a >= 2 union select a from t1 where a=1 order by a desc limit 2 ) union select a from t1 where a=3 order by a limit 2 ) union select a from t1 where a=1; 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 `__7`.`a` AS `a` from (select `__5`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` >= 2 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 order by `a` desc limit 2) `__5` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 order by `a` limit 2) `__7` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 latin1 latin1_swedish_ci select * from v1; a 3 4 1 drop view v1; 10.4. view as [tailed] TVC create view v1 as values (3), (7), (1); 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 values (3),(7),(1) latin1 latin1_swedish_ci select * from v1; 3 3 7 1 drop view v1; create view v1 as (((values (3), (7), (1))) order by 1); 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 (values (3),(7),(1) order by 1) latin1 latin1_swedish_ci select * from v1; 3 1 3 7 drop view v1; 10.5. view as [tailed] union of TVCs create view v1 as values (3), (7), (1) union values (3), (4), (2); 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 values (3),(7),(1) union values (3),(4),(2) latin1 latin1_swedish_ci select * from v1; 3 3 7 1 4 2 drop view v1; create view v1 as (values (3), (7), (1) union values (3), (4), (2)) order by 1; 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 values (3),(7),(1) union values (3),(4),(2) order by 1 latin1 latin1_swedish_ci select * from v1; 3 1 2 3 4 7 drop view v1; create view v1 as (values (3), (7), (1) order by 1 limit 2) union (values (3), (4), (2) order by 1 desc limit 2); 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 (values (3),(7),(1) order by 1 limit 2) union (values (3),(4),(2) order by 1 desc limit 2) latin1 latin1_swedish_ci select * from v1; 3 1 3 4 drop view v1; create view v1 as (values (3), (7), (1) order by 1 limit 2) union values (3), (4), (2) order by 1; 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 (values (3),(7),(1) order by 1 limit 2) union values (3),(4),(2) order by 1 latin1 latin1_swedish_ci select * from v1; 3 1 2 3 4 drop view v1; 10.6. view as [tailed] union of [tailed] select and tailed TVC create view v1 as ( (((select a from t1 where a <=3) order by a) limit 2) union (((values (3), (4), (2)) order by 1 desc) limit 2) ) order by a; 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` from `t1` where `t1`.`a` <= 3 order by `t1`.`a` limit 2) union (values (3),(4),(2) order by 1 desc limit 2) order by `a` latin1 latin1_swedish_ci select * from v1; a 1 2 3 4 drop view v1; create view v1 as ( select a from t1 where a=1 union values (3), (4), (2) order by 1 desc ) union select a from t1 where a=2 order by a desc limit 3; 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 `__5`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union values (3),(4),(2) order by 1 desc) `__5` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 2 order by `a` desc limit 3 latin1 latin1_swedish_ci select * from v1; a 4 3 2 drop view v1; 10.7. view as select with CTE create view v1 as with t as (select * from t1 where a <=3) select * from t; 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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 3 1 2 drop view v1; create view v1 as with t as ( select * from t1 where a < 3 union select * from t1 where a > 3 order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7; 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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 4 7 drop view v1; 10.8. view as union with CTE create view v1 as with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc); 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 with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 4 drop view v1; create view v1 as with t as ( (select * from t1 where a < 3) union (select * from t1 where a > 3) order by a desc limit 3 ) (select a from t where a=4 union select a from t where a=7 order by a desc); 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 with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 4 drop view v1; create view v1 as with t(a) as (values (2), (1)) select a from t; 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 with t(`a`) as (values (2),(1))select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 2 1 drop view v1; create view v1 as with t(a) as ( values (2), (1) union (values (4), (7)) order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; 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 with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 4 drop view v1; create view v1 as with t(a) as ( (values (2), (1)) union (values (4), (7) order by 1 desc) order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc; 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 with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 1 drop view v1; create view v1 as with t as (select * from t1 where a < 3), s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; 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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 1 drop view v1; create view v1 as with t as (select * from t1 where a < 3), s as (select * from t where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; 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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 1 drop view v1; drop table t1,t2; # End of 10.4 tests