关联查询

自然连接： 关联的表有同名列，内部使用同名列做等值关联，除语法外效果与内连接等值关联一样

内连接：结果集是两个表满足关联条件的行

左外连接：结果集是左表所有行以及右表满足关联条件的行，右表不满足关联条件部分填充NULL

右外连接：结果集是右表所有行以及左表满足关联条件的行，左表不满足关联条件部分填充NULL

全外连接：结果集是左表和右表的所有行，对于不符合关联条件的部分填充NULL

半连接IN：只返回满足关联条件的左侧行

反半连接NOT IN：只返回左侧不能满足关联条件的行

表关联类型

层次查询

层次查询的场景：表中存储的数据存在层次结构，可以通过层次查询以层级的顺序展示数据。

层次结构，也叫树形结构，有根节点、分支节点、叶子节点。

层次查询的语法：SELECT  <expressions>,<hierarchy expression>

FROM table_name

START WITH  <start with condition>

CONNECT BY <connect by condition>

< hierarchy expression >::=

LEVEL| CONNECT_BY_ISCYCLE

| CONNECT_BY_ISLEAF

| PRIOR <value expression>

| CONNECT_BY_ROOT <value expression>

| SYS_CONNECT_BY_PATH

层次查询-示例

SELECT employee_id, last_name, manager_id

FROM employees

CONNECT BY PRIOR employee_id = manager_id;

一般表达式（CTE）

一般表达式是在SELECT语句中定义并在语句内参照的有名称的临时结果集。

WITH cte_name[<(col_name_list)>] 

AS <subquery>

[, cte_name2[<(col_name_list)>] 

AS <subquery2>,…]

<query clause>

名词解释：

cte_name: 一般表达式名称，一个SQL中存在多个一般表达式子句时不能有重复名称

col_name_list: 一般表达式的列名，多个列以逗号隔开

subquery: 构成临时结果集的子查询语句

query_clause: 主查询语句

一般表达式有非递归和递归两种。区别为是否在子查询部分参照一般表达式自身。

一般表达式（CTE2）

例1:递归的一般表达式

WITH RECURSIVE CTE(C1)AS

(

SELECT 1                                        

FROM dual

UNION ALL

SELECT C1 + 1

FROM RECURSIVE_CTE

WHERE C1<3

)

SELECT C1 FROM RECURSIVE _CTE:

例2:非递归的一般表达式

WITH NON RECURSIVE CTE(C1 )AS

(

SELECT i1

FROM t1

UNION ALL

SELECT i1

FROM t2

)

SELECT C1 FROM NON_RECURSIVE_CTE

CASE表达式

CASE表达式按照CASE语句中的顺序判断WHEN ... THEN语句。

如果比较结果为FALSE，则继续判断后续的WHEN...THEN语句，直到显示TRUE为止。

如果比较结果为TRUE，则返回结果，不再继续判断。

若WHEN语句的所有判断结果为FALSE，则返回ELSE语句的结果。

若省略ELSE语句，则结果返回NULL。

简单CASE表达式：

用equal运算( expr = comparison_expr )判断CASE expr与WHEN ... THEN语句的comparison_expr。语法如下：

CASE <expr> WHEN <comparision_expr> THEN <result>

                       [WHEN <comparision_expr> THEN <result> …]

                       [ELSE <result>]

END

搜索CASE表达式：

判断WHEN ... THEN语句的条件（condition）。语法如下：

CASE WHEN <condition> THEN <result>

          [WHEN <condition> THEN <result> …]

          [ELSE <result>]

END

UPSERT语句：

INSERT INTO table_name[(col_name,…)]

    VALUES(<value_expr>)[,…]

     |<query_expr>

    ON DUPLICATE KEY {DO NOTHING| [DO] UPDATE [SET] (column_name,…) = (value_expr,…) }

在表中创建新的row。如果有违unique限制条件，则更新原来的row。

一定条件下可以用来替代MERGE INTO语句：

MERGE INTO target_table USING source_table ON condition

WHEN MATCHED THEN UPDATE SET col_name=value_expr,… WHERE condition

                                         DELETE WHERE condition

WHEN NOT MATCHED THEN INSERT (col_name,…) VALUES (value_expr,…)

UPSERT语句与MERGE INTO语句的区别：

MERGE INTO根据ON后面的条件判断，符合ON条件的更新，不符合ON条件的插入；

UPSERT预设了条件是是否有主键冲突，如果不冲突，则插入，冲突则更新。

MERGE INTO的条件是由用户自定义，灵活度更高，所以只有一部分情况能够改写成UPSERT语句格式。

另外MERGE INTO允许对UPDATE后的数据进行条件判断，并删除符合条件的更新后的数据，UPSERT不支持。