3.2.3.5. Oceanbase-Mysql

3.2.3.5.1. 简单查询

简单查询是指从数据库的一个或多个选择列表或视图中检索一个或多个列数据，列的数量以及它们的数据类型和长度由选择列表的元素确定。而选择列表指的是 SELECT 关键字之后和 FROM 子句之前的表达式列表。

如果两个或多个表有一些共同的列名，则必须用表名限定列名。您可以使用列别名 col_alias 来标记紧接在选择列表之前的表达式。别名可以在 ORDER BY 子句中使用，但不能在查询中的其他子句中使用。

简单查询的 SELECT 语法如下：

simple_select:
SELECT [ DISTINCT | UNIQUE | ALL] select_expr_list
FROM from_list
[WHERE condition]
[GROUP BY group_expression_list
    [{ROLLUP | GROUPING SETS} group_expression_list]
    [HAVING condition]
 ]
[ORDER BY order_expression_list]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
[row_limiting_clause ]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]

from_list:
    table_reference [, table_reference...]

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause
    | table_name@dblink_name

simple_table:
    table_factor [partition_option] [[table_alias_name]
    | (select_stmt)  table_alias_name
    | (table_reference_list)

joined_table:
    table_reference [INNER] JOIN simple_table [join_condition]
    | table_reference outer_join_type JOIN simple_table join_condition

partition_option:
    PARTITION (partition_name_list)

partition_name_list:
    partition_name [, partition_name...]

outer_join_type:
    {LEFT | RIGHT | FULL} [OUTER]

join_condition:
    ON expression

condition:
    expression

group_expression_list:
    group_expression [, group_expression...]

group_expression:
    expression [ASC | DESC]

order_expression_list:
    order_expression [, order_expression...]

order_expression:
    expression [ASC | DESC]

row_limiting_clause:
    [ OFFSET offset { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
        { ROW | ROWS } { ONLY | WITH TIES } ]

pivot_clause：
    PIVOT
    (aggregate_function ( expr ) [[AS] alias ]
    [, aggregate_function ( expr ) [[AS] alias ]... ]
    pivot_for_clause
    pivot_in_clause
    )

pivot_for_clause：
    FOR { column }

pivot_in_clause
    IN
    ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
    [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
    )

unpivot_clause ：
    UNPIVOT
    ( { column }
    pivot_for_clause
    unpivot_in_clause
    )

unpivot_in_clause：
    IN
    ( { column | ( column [, column... ]) }
    )

CREATE TABLE unisql_simple_query_employee (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT,
salary NUMERIC
);
INSERT INTO unisql_simple_query_employee VALUES(1, 'DEL', 'FA BEN', 1, 1500);
INSERT INTO unisql_simple_query_employee VALUES(2, 'AXEL', 'BELL', 1, 1000);
INSERT INTO unisql_simple_query_employee VALUES(3, 'CRIS',  'RACHAR', 1, 1000);
INSERT INTO unisql_simple_query_employee VALUES(4, 'BlAIR',  'WALDORF', 1, 800);

-- 转换前Oracle SQL：
-- 查询表中部分列
SELECT first_name, last_name, salary FROM unisql_simple_query_employee;
-- 查询表中所有列
SELECT * FROM unisql_simple_query_employee;
-- 对表中的列进行数学运算
SELECT salary+100 FROM unisql_simple_query_employee;
-- 给表中的列取别名
SELECT salary*12 AS year_salary FROM unisql_simple_query_employee;
-- 将表中的数据去重
SELECT DISTINCT manager_id FROM unisql_simple_query_employee;
-- 使用 CASE WHEN 语句查询表中的数据
SELECT salary, CASE WHEN salary >= 1200 then 'high' WHEN salary >= 1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee;

-- 转换后Oceanbase-MySQL：
SELECT `first_name`,`last_name`,`salary` FROM `unisql_simple_query_employee`;
SELECT * FROM `unisql_simple_query_employee`;
SELECT `salary`+100 FROM `unisql_simple_query_employee`;
SELECT `salary`*12 AS `year_salary` FROM `unisql_simple_query_employee`;
SELECT DISTINCT `manager_id` FROM `unisql_simple_query_employee`;
SELECT `salary`,CASE WHEN `salary`>=1200 THEN 'high' WHEN `salary`>=1000 THEN 'middle' ELSE 'low' END AS `type` FROM `unisql_simple_query_employee`;

3.2.3.5.1.1. 分区表查询

-- 创建表
CREATE TABLE unisql_partition (
unisql_id NUMBER,
unisql_name VARCHAR2(50),
unisql_salary NUMBER(10,2),
unisql_hire_date DATE,
CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date)
)
PARTITION BY RANGE (unisql_hire_date) (
PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
-- 插入数据
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (1, 'John', 5000, TO_DATE('2023-01-01','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (2, 'Mary', 6000, TO_DATE( '2023-02-15','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (3, 'Tom', 7000, TO_DATE( '2023-03-20','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (4, 'Alice', 8000, TO_DATE('2023-04-10','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (5, 'Bob', 9000, TO_DATE('2023-05-01','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (6, 'Bill', 1000, TO_DATE('2024-05-02','YYYY-MM-DD'));

-- 转换前Oracle SQL：
SELECT unisql_id,unisql_name,unisql_salary, unisql_hire_date FROM unisql_partition PARTITION (unisql_partition_p1);
UNISQL_ID|UNISQL_NAME|UNISQL_SALARY|UNISQL_HIRE_DATE       |
---------+-----------+-------------+-----------------------+
        1|John       |         5000|2023-01-01 00:00:00.000|
        2|Mary       |         6000|2023-02-15 00:00:00.000|
        3|Tom        |         7000|2023-03-20 00:00:00.000|
        4|Alice      |         8000|2023-04-10 00:00:00.000|
        5|Bob        |         9000|2023-05-01 00:00:00.000|

-- 转换后oceanbase-MySQL：
SELECT `unisql_id`,`unisql_name`,`unisql_salary`,`unisql_hire_date` FROM `unisql_partition` PARTITION(`unisql_partition_p1`);
unisql_id|unisql_name|unisql_salary|unisql_hire_date       |
---------+-----------+-------------+-----------------------+
        1|John       |      5000.00|2023-01-01 00:00:00.000|
        2|Mary       |      6000.00|2023-02-15 00:00:00.000|
        3|Tom        |      7000.00|2023-03-20 00:00:00.000|
        4|Alice      |      8000.00|2023-04-10 00:00:00.000|
        5|Bob        |      9000.00|2023-05-01 00:00:00.000|

3.2.3.5.1.2. pivot行转列

-- 创建表
CREATE TABLE unisql_pivot(id int ,
    name varchar(64),
    score NUMBER ,
    subject varchar2(64));
-- 插入数据
INSERT INTO unisql_pivot VALUES(1,'张三',70,'CHINESE');
INSERT INTO unisql_pivot VALUES(1,'张三',90,'MATH');
INSERT INTO unisql_pivot VALUES(1,'张三',95,'ENGLISH');
INSERT INTO unisql_pivot VALUES(2,'李四',75,'CHINESE');
INSERT INTO unisql_pivot VALUES(2,'李四',85,'MATH');
INSERT INTO unisql_pivot VALUES(2,'李四',90,'ENGLISH');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'CHINESE');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'MATH');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'ENGLISH');

-- 转换前Oracle SQL：
SELECT
    id,
    name,
    chinese_score,
    match_score,
    english_score
FROM
    unisql_pivot pivot(
    min(score) FOR subject IN('CHINESE' AS chinese_score,
    'MATH' AS match_score,
    'ENGLISH' AS english_score)
    )
WHERE
    id IN (1, 2, 3)
    ORDER BY id;
ID|NAME|CHINESE_SCORE|MATCH_SCORE|ENGLISH_SCORE|
--+----+-------------+-----------+-------------+
1|张三  |           70|         90|           95|
2|李四  |           75|         85|           90|
3|王五  |           90|         90|           90|

-- 转换后oceanbase-MySQL：
SELECT
    `id`,
    `name`,
    `chinese_score`,
    `match_score`,
    `english_score`
FROM
    (SELECT `id`,`name`,
    min(CASE WHEN `subject`='CHINESE' THEN `score` END) AS `chinese_score`,
    min(CASE WHEN `subject`='MATH' THEN `score` END) AS `match_score`,
    min(CASE WHEN `subject`='ENGLISH' THEN `score` END) AS `english_score` FROM `unisql_pivot` WHERE `id` IN (1,2,3) GROUP BY `id`,`name` ORDER BY `id`)
    AS `uni_sub`;
id|name|chinese_score|match_score|english_score|
--+----+-------------+-----------+-------------+
1|张三  |           70|         90|           95|
2|李四  |           75|         85|           90|
3|王五  |           90|         90|           90|

3.2.3.5.1.3. unpivot列转行

注意

统一SQL当前对unpivot转换使用限制如下：
1. 只支持在单表查询语句中使用，参考如下：
  SELECT id,name,score AS sc,subject AS su FROM unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english)) WHERE id IN (1, 2, 3,4,5) ORDER BY id;
2. 只支持单字段的unpivot,如上(score for subject)
3. 统一SQL会将unpivot in中的字段转换为大写，如上(chinese-->CHINESE, math-->MATH, english-->ENGLISH)
4. 不支持join，with, 子查询，group by, having，rownum
5. 不支持unpivot和unpivot for字段上使用函数，表达式
6. 不支持unpivot in语句中的字段使用AS
7. 分页查询存在offset时必须有fetch
8. 注意oracle和tdmysql字符集，排序规则等底层实现存在差异，排序字段需要保证是唯一序，否则可能导致两个库执行结果获取结果顺序不一致。

参考示例

-- 前置准备SQL：
CREATE TABLE unisql_unpivot(id INT,name VARCHAR(64),chinese NUMBER,math INT,english INT);
INSERT INTO unisql_unpivot VALUES(1,'张三',70,90,95);
INSERT INTO unisql_unpivot VALUES(2,'李四',75,85,90);
INSERT INTO unisql_unpivot VALUES(3,'张三',90,90,90);
drop table unisql_unpivot;


-- 转换前Oracle SQL：
SELECT id,name,score,subject FROM unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english)) WHERE id IN (1, 2, 3) ORDER BY id;
ID|NAME|SCORE|SUBJECT|
--+----+-----+-------+
1|张三  |   70|CHINESE|
1|张三  |   90|MATH   |
1|张三  |   95|ENGLISH|
2|李四  |   75|CHINESE|
2|李四  |   85|MATH   |
2|李四  |   90|ENGLISH|
3|张三  |   90|CHINESE|
3|张三  |   90|MATH   |
3|张三  |   90|ENGLISH|

-- 转换后oceanbase-MySQL
SELECT `id`,`name`,`chinese` AS `score`,'CHINESE' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`math` AS `score`,'MATH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`english` AS `score`,'ENGLISH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) ORDER BY `id`
id|name|score|subject|
--+----+-----+-------+
1|张三  |   70|CHINESE|
1|张三  |   95|ENGLISH|
1|张三  |   90|MATH   |
2|李四  |   85|MATH   |
2|李四  |   75|CHINESE|
2|李四  |   90|ENGLISH|
3|张三  |   90|MATH   |
3|张三  |   90|CHINESE|
3|张三  |   90|ENGLISH|


 -- 转换前Oracle SQL：
SELECT id,name,score,subject FROM unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english)) WHERE id IN (1, 2, 3) ORDER BY id,name,score,subject OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;
ID|NAME|SCORE|SUBJECT|
--+----+-----+-------+
1|张三  |   90|MATH   |
1|张三  |   95|ENGLISH|

-- 转换后oceanbase-MySQL
SELECT `id`,`name`,`chinese` AS `score`,'CHINESE' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`math` AS `score`,'MATH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) UNION ALL SELECT `id`,`name`,`english` AS `score`,'ENGLISH' AS `subject` FROM `unisql_unpivot` WHERE `id` IN (1,2,3) ORDER BY `id`,`name`,`score`,`subject` LIMIT 1,2
id|name|score|subject|
--+----+-----+-------+
1|张三  |   90|MATH   |
1|张三  |   95|ENGLISH|

3.2.3.5.2. 复合查询

使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询，即复合查询。所有集合运算符都具有相同的优先级。

3.2.3.5.2.1. UNION 唯一并集

-- 创建表
CREATE TABLE unisql_collect_1(
    id INT,
    name VARCHAR(32),
    chinese NUMBER,
    math NUMBER,
    english NUMBER);

CREATE TABLE unisql_collect_2(
    id INT,
    name VARCHAR(32),
    chinese NUMBER,
    math NUMBER,
    english NUMBER);

-- 插入数据
INSERT INTO unisql_collect_1 VALUES(1,'张三',70,90,95);
INSERT INTO unisql_collect_1 VALUES(2,'李四',75,85,90);
INSERT INTO unisql_collect_1 VALUES(3,'王五',90,90,90);
INSERT INTO unisql_collect_1 VALUES(4,'秦六',65,65,60);

INSERT INTO unisql_collect_2 VALUES(1,'张三',70,90,95);
INSERT INTO unisql_collect_2 VALUES(2,'李四',75,85,90);
INSERT INTO unisql_collect_2 VALUES(4,'Bob',78,88,65);
INSERT INTO unisql_collect_2 VALUES(5,'Li',90,93,93);

-- 转换前Oracle SQL：
SELECT * FROM unisql_collect_1
    UNION
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三  |     70|  90|     95|
2|李四  |     75|  85|     90|
3|王五  |     90|  90|     90|
4|Bob |     78|  88|     65|
4|秦六  |     65|  65|     60|
5|Li  |     90|  93|     93|

-- 转换后oceanbase-MySQL：
SELECT * FROM `unisql_collect_1` UNION SELECT * FROM `unisql_collect_2`;
id|name|chinese|math|english|
--+----+-------+----+-------+
1|张三  |     70|  90|     95|
2|李四  |     75|  85|     90|
3|王五  |     90|  90|     90|
4|秦六  |     65|  65|     60|
4|Bob |     78|  88|     65|
5|Li  |     90|  93|     93|

3.2.3.5.2.2. UNION ALL 并集

-- 转换前Oracle SQL：
SELECT * FROM unisql_collect_1
 UNION ALL
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三  |     70|  90|     95|
2|李四  |     75|  85|     90|
3|王五  |     90|  90|     90|
4|秦六  |     65|  65|     60|
1|张三  |     70|  90|     95|
2|李四  |     75|  85|     90|
4|Bob |     78|  88|     65|
5|Li  |     90|  93|     93|

-- 转换后oceanbase-MySQL：
SELECT * FROM `unisql_collect_1` UNION ALL SELECT * FROM `unisql_collect_2`
id|name|chinese|math|english|
--+----+-------+----+-------+
1|张三  |     70|  90|     95|
2|李四  |     75|  85|     90|
3|王五  |     90|  90|     90|
4|秦六  |     65|  65|     60|
1|张三  |     70|  90|     95|
2|李四  |     75|  85|     90|
4|Bob |     78|  88|     65|
5|Li  |     90|  93|     93|

3.2.3.5.3. 联接查询

联接（Join）是将两个或多个表、视图的结合在一起的查询。

3.2.3.5.3.1. 等值联接

等值联接是包含等值运算符的联接。在进行等值联接时将指定列满足等值条件的行进行组合输出。

示例

-- 创建 unisql_orders 表
CREATE TABLE unisql_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
);

-- 创建 unisql_customers 表
CREATE TABLE unisql_customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
city VARCHAR2(100)
);

-- 插入示例数据
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000);

INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500);

INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1001, 'John Smith', 'New York');

INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1002, 'Jane Doe', 'Los Angeles');

-- 转换前Oracle SQL：
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE             |CUSTOMER_NAME|CITY       |AMOUNT|
--------+-----------------------+-------------+-----------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York   |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles|  1500|

-- 转换后oceanbase-MySQL：
SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount`
FROM `unisql_orders` AS `o`
INNER JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id`
order_id|order_date             |customer_name|city       |amount|
--------+-----------------------+-------------+-----------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York   |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles|  1500|

3.2.3.5.3.2. 自联接

自联接是表与其自身的联接。该表在 FROM 子句中出现两次，后跟表别名，这些别名限定联接条件中的列名。

示例

-- 创建 employees 表
CREATE TABLE employees (
employee_id NUMBER,
employee_name VARCHAR2(100),
manager_id NUMBER
);

-- 插入示例数据
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (1001, 'John Smith', 1003);

INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (1002, 'Jane Doe', 1003);

INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (1003, 'Mike Johnson', NULL);

-- 转换前Oracle SQL：
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e, employees m WHERE e.manager_id = m.employee_id;
EMPLOYEE     MANAGER
-----------------------
John Smith   Mike Johnson
Jane Doe     Mike Johnson

-- 转换后oceanbase-MySQL：
SELECT `e`.`employee_name` AS `employee`,`m`.`employee_name` AS `manager`
FROM `employees` AS `e`, `employees` AS `m` WHERE `e`.`manager_id`=`m`.`employee_id`
employee  |manager     |
----------+------------+
John Smith|Mike Johnson|
Jane Doe  |Mike Johnson|

3.2.3.5.3.3. 笛卡尔积

如果联接查询中的两个表没有联接条件，返回其笛卡尔乘积，使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积会生成许多行，但是大多是无用数据。例如，两个都有 100 行的表的笛卡尔积有 10,000 行，所以除非特别需要笛卡尔乘积，建议查询中始终包括一个联接条件以避免返回笛卡尔乘积。

示例

-- 创建 colors 表
CREATE TABLE unisql_colors (
color_id NUMBER,
color_name VARCHAR2(100)
);

-- 创建 sizes 表
CREATE TABLE unisql_sizes (
size_id NUMBER,
size_name VARCHAR2(100)
);

-- 插入示例数据
INSERT INTO unisql_colors (color_id, color_name)
VALUES (1, 'Red');

INSERT INTO unisql_colors (color_id, color_name)
VALUES (2, 'Blue');

INSERT INTO unisql_sizes (size_id, size_name)
VALUES (1, 'Small');

INSERT INTO unisql_sizes (size_id, size_name)
VALUES (2, 'Medium');

-- 转换前Oracle SQL：
SELECT c.color_id, c.color_name, s.size_id, s.size_name
FROM unisql_colors c, unisql_sizes s;
COLOR_ID|COLOR_NAME|SIZE_ID|SIZE_NAME|
--------+----------+-------+---------+
    1|Red       |      1|Small    |
    1|Red       |      2|Medium   |
    2|Blue      |      1|Small    |
    2|Blue      |      2|Medium   |

-- 转换后oceanbase-MySQL：
SELECT `c`.`color_id`,`c`.`color_name`,`s`.`size_id`,`s`.`size_name`
FROM `unisql_colors` AS `c`, `unisql_sizes` AS `s`
color_id|color_name|size_id|size_name|
--------+----------+-------+---------+
    1|Red       |      1|Small    |
    2|Blue      |      1|Small    |
    1|Red       |      2|Medium   |
    2|Blue      |      2|Medium   |

3.2.3.5.3.4. INNER JOIN 内联接查询

内联接基于联接条件将两张表（例如 A 和 B）的列组合在一起，产生新的结果存放在表中。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足联接条件的组合。当满足联接条件时，表 A 和 B 中匹配的行会按列组合（并排组合）成结果集中的一行。联接产生的结果集等于首先对两张表做笛卡尔积，将表 A 中的每一行和表 B 中的每一行组合，然后返回满足联接条件的记录。

示例

-- 创建 unisql_orders 表
CREATE TABLE unisql_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
);

-- 创建 unisql_customers 表
CREATE TABLE unisql_customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
city VARCHAR2(100)
);

-- 插入示例数据
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000);

INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500);

INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (3, TO_DATE('2022-04-03', 'YYYY-MM-DD'), 1003, 1600);

INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1001, 'John Smith', 'New York');

INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1002, 'Jane Doe', 'Los Angeles');

INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1004, 'Grace Miller', 'San Francisco');

-- 转换前Oracle SQL：
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
INNER JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE             |CUSTOMER_NAME|CITY       |AMOUNT|
--------+-----------------------+-------------+-----------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York   |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles|  1500|

-- 转换后oceanbase-MySQL：
SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount`
FROM `unisql_orders` AS `o`
INNER JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id`
order_id|order_date             |customer_name|city       |amount|
--------+-----------------------+-------------+-----------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York   |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles|  1500|

3.2.3.5.3.5. LEFT JOIN 左外联接查询

左联接（LEFT [OUTER] JOIN）中左表行未在右表匹配到行时，在右表自动填充 NULL。

示例

-- 转换前Oracle SQL：
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
LEFT JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE             |CUSTOMER_NAME|CITY       |AMOUNT|
--------+-----------------------+-------------+-----------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York   |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles|  1500|
    3|2022-04-03 00:00:00.000|             |           |  1600|

-- 转换后oceanbase-MySQL：
SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount`
FROM `unisql_orders` AS `o`
LEFT JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id`;
order_id|order_date             |customer_name|city       |amount|
--------+-----------------------+-------------+-----------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York   |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles|  1500|
    3|2022-04-03 00:00:00.000|             |           |  1600|

3.2.3.5.3.6. RIGHT JOIN 右外联接查询

右联接（RIGHT [OUTER] JOIN）中右表行未在左表匹配到行时，在左表自动填充 NULL。

示例

-- 转换前Oracle SQL：
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
RIGHT JOIN unisql_customers c ON o.customer_id = c.customer_id;
order_id|order_date             |customer_name|city         |amount|
--------+-----------------------+-------------+-------------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York     |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles  |  1500|
        |                       |Grace Miller |San Francisco|      |

-- 转换后oceanbase-MySQL：
SELECT `o`.`order_id`,`o`.`order_date`,`c`.`customer_name`,`c`.`city`,`o`.`amount`
FROM `unisql_orders` AS `o`
RIGHT JOIN `unisql_customers` AS `c` ON `o`.`customer_id`=`c`.`customer_id`;
order_id|order_date             |customer_name|city         |amount|
--------+-----------------------+-------------+-------------+------+
    1|2022-01-01 00:00:00.000|John Smith   |New York     |  1000|
    2|2022-02-01 00:00:00.000|Jane Doe     |Los Angeles  |  1500|
        |                       |Grace Miller |San Francisco|      |

3.2.3.5.3.7. 外联接符号（+）

对于左联接，在 WHERE 子句的联接条件中对右表的所有列应用外联接符号（+）；对于右联接，在 WHERE 子句的联接条件中对左表的所有列应用外联接符号（+）。

示例

-- (+)左外连接
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
    INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
    INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
    INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');

CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
    INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');

-- 转换前Oracle SQL：
SELECT
        UNISQL_TABLE1.*,
        UNISQL_TABLE2.*
FROM
        UNISQL_TABLE1,UNISQL_TABLE2
WHERE
        UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+);
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB|  |  |  |
3|C |CC|  |  |  |

-- 转换后oceanbase-MySQL：
SELECT
    `UNISQL_TABLE1`.*,
    `UNISQL_TABLE2`.*
FROM
    `UNISQL_TABLE2`
RIGHT JOIN
    `UNISQL_TABLE1` ON `UNISQL_TABLE1`.`c1`=`UNISQL_TABLE2`.`c1` WHERE 1=1
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB|  |  |  |
3|C |CC|  |  |  |

-- (+)左外连接多个条件
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');

CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');

-- 转换前Oracle SQL：
SELECT
        UNISQL_TABLE1.*,
        UNISQL_TABLE2.*
FROM
        UNISQL_TABLE1,
        UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+)
AND UNISQL_TABLE1.c2 = UNISQL_TABLE2.c2(+);
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
2|B |BB|  |  |  |
1|A |AA|  |  |  |
3|C |CC|  |  |  |

-- 转换后oceanbase-MySQL：
select
    `UNISQL_TABLE1` .*,
    `UNISQL_TABLE2` .*
from
    `UNISQL_TABLE2`
right join `UNISQL_TABLE1` on
    `UNISQL_TABLE1`.`c1` = `UNISQL_TABLE2`.`c1`
    and `UNISQL_TABLE1`.`c2` = `UNISQL_TABLE2`.`c2`
where
    1 = 1 and 1 = 1;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB|  |  |  |
3|C |CC|  |  |  |

-- (+)右外连接
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');

CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');

-- 转换前Oracle SQL：
SELECT
        *
FROM
        UNISQL_TABLE1,UNISQL_TABLE2
WHERE
        UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |

-- 转换后oceanbase-MySQL：
select
    *
from
    `UNISQL_TABLE1`
right join `UNISQL_TABLE2` on
    `UNISQL_TABLE1`.`c1` = `UNISQL_TABLE2`.`c1`
where
    1 = 1
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |

-- (+)右外连接多个条件
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');

CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');

-- 转换前Oracle SQL：
SELECT
        *
FROM
    UNISQL_TABLE1,UNISQL_TABLE2
WHERE
        UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1
        AND UNISQL_TABLE1.c2(+) = UNISQL_TABLE2.c2;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
|  |  | 1|a |b |



-- (+)外连接在子查询中

CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');

CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');

CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D');

-- 转换前Oracle SQL：
SELECT
        *
FROM
(
SELECT
        t1.c1,
        t2.c2,
        t3.c3
FROM
        UNISQL_TABLE1 t1,
        UNISQL_TABLE2 t2,
        UNISQL_TABLE3 t3
WHERE
        t1.c1 = t2.c1(+)
        AND t1.c1 = t3.c1(+)
);
C1|C2|C3|
--+--+--+
1|a |D |
2|  |  |
3|  |  |

-- 转换后oceanbase-MySQL：
select
    *
from
    (
    select
        `t1`.`c1`,
        `t2`.`c2`,
        `t3`.`c3`
    from
        `UNISQL_TABLE3` as `t3`
    right join (`UNISQL_TABLE2` as `t2`
    right join `UNISQL_TABLE1` as `t1` on
        `t1`.`c1` = `t2`.`c1`) on
        `t1`.`c1` = `t3`.`c1`
    where
        1 = 1
        and 1 = 1) as `uni_sub`;

c1|c2|c3|
--+--+--+
1|a |D |
2|  |  |
3|  |  |


-- (+)外连接加笛卡尔积
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');

CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');

CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2  VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D');
-- 转换前Oracle SQL：
SELECT
        t1.*,
        t2.*,
        t3.*
FROM
        UNISQL_TABLE1 t1,
        UNISQL_TABLE2 t2,
        UNISQL_TABLE3 t3
WHERE
t1.c1(+) = t2.c1
AND t1.c1 = t3.c1;
C1|C2|C3|C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+--+--+--+
1|A |AA| 1|a |b | 1|C |D |

-- 转换后oceanbase-MySQL：
select
    `t1` .*,
    `t2` .*,
    `t3` .*
from
    (`UNISQL_TABLE1` as `t1`
right join `UNISQL_TABLE2` as `t2` on
    `t1`.`c1` = `t2`.`c1`)
join `UNISQL_TABLE3` as `t3`
where
    1 = 1
    and `t1`.`c1` = `t3`.`c1`;
C1|C2|C3|C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+--+--+--+
1|A |AA| 1|a |b | 1|C |D |

3.2.3.5.4. WITH CLAUSE

如果查询语句中有多个相同的子查询，可以把相同的子查询放在 WITH CLAUSE 中作为公共表达式，在主体查询中直接引用即可。

语法

with_clause_select:
with_clause simple_select

with_clause:
    WITH query_table_name [opt_column_alias_name_list] AS ( select_clause )

select_clause:
    simple_select | select_clause_set

opt_column_alias_name_list:
    (column_name_list)

column_name_list:
    column_name [,column_name...]

示例

CREATE TABLE unisql_tbl1(col1 INT,col2 INT,col3 INT);

INSERT INTO unisql_tbl1 VALUES(1,1,1);
INSERT INTO unisql_tbl1 VALUES(2,2,2);
INSERT INTO unisql_tbl1 VALUES(3,3,3);

/*直接使用表 unisql_tbl1 的列名作为 u_tbl1 的列名*/
-- 转换前Oracle SQL：
WITH u_tbl1 AS(SELECT * FROM unisql_tbl1) SELECT * FROM  u_tbl1;
COL1|COL2|COL3|
----+----+----+
1|   1|   1|
2|   2|   2|
3|   3|   3|

-- 转换后oceanbase-MySQL：
SELECT * FROM (SELECT * FROM `unisql_tbl1`) AS `u_tbl1`;
col1|col2|col3|
----+----+----+
1|   1|   1|
2|   2|   2|
3|   3|   3|

/*直接给 w_tbl1 命名列名*/
-- 转换前Oracle SQL：
WITH w_tbl1(c1,c2,c3) AS(SELECT * FROM unisql_tbl1) SELECT * FROM  w_tbl1;
C1|C2|C3|
--+--+--+
1| 1| 1|
2| 2| 2|
3| 3| 3|

-- 转换后oceanbase-MySQL：
SELECT * FROM (SELECT * FROM `unisql_tbl1`) AS `w_tbl1`
col1|col2|col3|
----+----+----+
1|   1|   1|
2|   2|   2|
3|   3|   3|

3.2.3.5.5. 子查询

子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句，可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图。可以在嵌入式视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。

子查询可以分为相关子查询和非相关子查询。相关子查询指该子查询的执行依赖了外部查询的变量，这种子查询通常会执行多次。非相关子查询指该子查询的执行不依赖外部查询的变量，这种子查询一般只需要计算一次。对于非相关子查询与部分相关子查询，可以通过改写进行子查询消除，实现嵌套子查询的展开。

语法

SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ]
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ]
| ( subquery ) [ order_by_clause ]

示例

CREATE TABLE unisql_table_a(PK INT, name VARCHAR(25));
INSERT INTO unisql_table_a VALUES(1,'福克斯');
INSERT INTO unisql_table_a VALUES(2,'警察');
INSERT INTO unisql_table_a VALUES(3,'的士');
INSERT INTO unisql_table_a VALUES(4,'林肯');
INSERT INTO unisql_table_a VALUES(5,'亚利桑那州');
INSERT INTO unisql_table_a VALUES(6,'华盛顿');
INSERT INTO unisql_table_a VALUES(7,'戴尔');
INSERT INTO unisql_table_a VALUES(10,'朗讯');

CREATE TABLE unisql_table_b(PK INT, name VARCHAR(25));
INSERT INTO unisql_table_b VALUES(1,'福克斯');
INSERT INTO unisql_table_b VALUES(2,'警察');
INSERT INTO unisql_table_b VALUES(3,'的士');
INSERT INTO unisql_table_b VALUES(6,'华盛顿');
INSERT INTO unisql_table_b VALUES(7,'戴尔');
INSERT INTO unisql_table_b VALUES(8,'微软');
INSERT INTO unisql_table_b VALUES(9,'苹果');
INSERT INTO unisql_table_b VALUES(11,'苏格兰威士忌');

-- 没有依赖关系的子查询
-- 转换前Oracle SQL：
SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2);
PK|NAME|
--+----+
1|福克斯 |
2|警察  |
3|的士  |
6|华盛顿 |
7|戴尔  |

-- 转换后oceanbase-MySQL
select
    *
from
    `unisql_table_a` as `T1`
where
    `T1`.`PK` in (
    select
        *
    from
        (
        select
            `T2`.`PK`
        from
            `unisql_table_b` as `T2`) as `unisub`);
PK|name|
--+----+
1|福克斯 |
2|警察  |
3|的士  |
6|华盛顿 |
7|戴尔  |

3.2.3.5.6. DUAL 表查询

DUAL 表有一个列 DUMMY，定义为 VARCHAR2(1)，并包含一个值为 X 的行。在使用 SELECT 语句计算常量表达式时，可以从 DUAL 表中选择数据。

-- 转换前Oracle SQL：
SELECT
    1,
    2 + 1,
    NULL,
    ''
FROM
    dual;

1|2+1|NULL|''|
-+---+----+--+
1|  3|    |  |

-- 转换后oceanbase-MySQL
SELECT 1,2+1,NULL,'' FROM dual;
1|2+1|NULL||
-+---+----++
1|  3|    ||

3.2.3.5.7. AS作为别名

通常AS在数据库中作为保留关键字，一般不建议作为字段的别名，但是有时候开发人员在编写SQL语句时，存在使用AS作为别名的情况。 统一SQL支持AS作为别名时到目标库的转换。

参考示例：

-- 转换前Oracle SQL：
SELECT
    0 AS,
    1 as,
    2 "as",
    3 a,
    4 AS b,
    count(*) AS
FROM
    dual;
0AS|1AS|as|A|B|COUNT(*)AS|
---+---+--+-+-+----------+
0|  1| 2|3|4|         1|

-- 转换后oceanbase-MySQL
select
    0 as `AS`,
    1 as `as`,
    2 as `as`,
    3 as `a`,
    4 as `b`,
    count(1) as `AS`
from
    dual;
AS|as|as|a|b|AS|
--+--+--+-+-+--+
0| 1| 2|3|4| 1|