mirror of
https://github.com/MariaDB/server.git
synced 2025-11-03 20:36:16 +01:00
4517 lines
83 KiB
Text
4517 lines
83 KiB
Text
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 <derived2> 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 <union2,3> ALL NULL NULL NULL NULL NULL NULL
|
|
NULL UNION RESULT <union1,4> 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 <derived2> 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 <union2,3> ALL NULL NULL NULL NULL NULL NULL
|
|
NULL UNION RESULT <union1,4> 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 <derived2> 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 <union2,3> 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 <derived2> 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 <union1,4> 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 <union1,2,3> 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 <union1,2> 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 <union1,2> 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 <derived2> ALL NULL NULL NULL NULL 2 100.00
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
7 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
6 UNION <derived4> 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 <union4,5> ALL NULL NULL NULL NULL NULL NULL
|
|
NULL UNION RESULT <union3,6> ALL NULL NULL NULL NULL NULL NULL
|
|
NULL UNION RESULT <union2,7> ALL NULL NULL NULL NULL NULL NULL
|
|
NULL UNION RESULT <union1,8> 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,
|
|
"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,
|
|
"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 <union1,2> 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": "<union1,2>",
|
|
"access_type": "ALL",
|
|
"query_specifications": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 20"
|
|
}
|
|
}
|
|
},
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"operation": "UNION",
|
|
"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 <union1,2> 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": "<union1,2>",
|
|
"access_type": "ALL",
|
|
"query_specifications": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 20"
|
|
}
|
|
}
|
|
},
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"operation": "UNION",
|
|
"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 <union1,2> 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": "<union1,2>",
|
|
"access_type": "ALL",
|
|
"query_specifications": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"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",
|
|
"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
|
|
% test Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y
|
|
% test\_% Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y
|
|
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
|