oracle sql join

最新推荐文章于 2023-03-17 17:26:22 发布

bazingaz

最新推荐文章于 2023-03-17 17:26:22 发布

阅读量1.9k

点赞数

分类专栏： ORACLE 文章标签： join sql oracle manager table sql server

本文链接：https://blog.csdn.net/bazingaz/article/details/6947873

版权

ORACLE 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00           
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00 
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

8 rows selected

SQL> select * from dept;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

内连接(inner join)查询会将 A 表的每一行和 B 表的每一行进行比较, 并找出满足连接谓词的组合. 当连接谓词被满足, A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行. 连接产生的结果集, 可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合, 然后返回满足连接谓词的记录。SQL 定义了两种不同语法方式去表示"连接". 首先是"显示连接符号", 它显式地使用关键字 JOIN, 其次是"隐式连接符号", 它使用所谓的"隐式连接符号". 隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分, 并用逗号隔开.这样就构成了一个"交叉连接", WHERE 语句可能放置一些过滤谓词(过滤条件). 那些过滤谓词在功能上等价于显式连接符号.

SQL> select *
  2  from emp
  3  inner join dept
  4  on emp.deptno=dept.deptno;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     30 SALES          CHICAGO
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     20 RESEARCH       DALLAS
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     30 SALES          CHICAGO
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     30 SALES          CHICAGO
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     10 ACCOUNTING     NEW YORK
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     20 RESEARCH       DALLAS

6 rows selected

SQL> select *
  2  from emp,dept
  3  where emp.deptno = dept.deptno;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     30 SALES          CHICAGO
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     20 RESEARCH       DALLAS
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     30 SALES          CHICAGO
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     30 SALES          CHICAGO
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     10 ACCOUNTING     NEW YORK
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     20 RESEARCH       DALLAS

6 rows selected

相等连接 (equi-join, 或 equijoin), 是比较连接(θ连接)的一种特例, 它的连接谓词只用了相等比较. 使用其他比较操作符(如 <)的不是相等连接

select * from emp inner join dept on emp.deptno=dept.deptno
SQL> select * from emp inner join dept using (deptno);

DEPTNO EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DNAME          LOC
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- -------------
    30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00 SALES          CHICAGO
    20  7566 JONES      MANAGER    7839 1981-4-2      2975.00           RESEARCH       DALLAS
    30  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00 SALES          CHICAGO
    30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00           SALES          CHICAGO
    10  7782 CLARK      MANAGER    7839 1981-6-9      2450.00           ACCOUNTING     NEW YORK
    20  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00           RESEARCH       DALLAS

6 rows selected

USING 结构并不仅仅是语法糖, 上面查询的结果和使用显式谓词得到的查询得到的结果是不同的. 特别地, 在 USING 部分列出的列(column)将
以只出现一次, 且名称无表名修饰.
自然连接(natural join )比相等连接的进一步特例化. 两表做自然连接时, 两表中的所有名称相同的列都将被比较, 这是隐式的. 自然连接得
到的结果表中, 两表中名称相同的列只出现一次.

SQL> select * from emp natural join dept;

DEPTNO EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DNAME          LOC
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- -------------
    30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00 SALES          CHICAGO
    20  7566 JONES      MANAGER    7839 1981-4-2      2975.00           RESEARCH       DALLAS
    30  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00 SALES          CHICAGO
    30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00           SALES          CHICAGO
    10  7782 CLARK      MANAGER    7839 1981-6-9      2450.00           ACCOUNTING     NEW YORK
    20  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00           RESEARCH       DALLAS

6 rows selected

交叉连接(cross join), 又称笛卡尔连接(cartesian join)或叉乘(Product), 它是所有类型的内连接的基础. 把表视为行记录的集合, 交叉连
接即返回这两个集合的笛卡尔积.

SQL> select * from emp cross join dept;
SQL> select * from emp,dept;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
 7369 SMITH      CLERK      7902 1980-12-17     800.00                      10 ACCOUNTING     NEW YORK
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00            10 ACCOUNTING     NEW YORK
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     10 ACCOUNTING     NEW YORK
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     10 ACCOUNTING     NEW YORK
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     10 ACCOUNTING     NEW YORK
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     10 ACCOUNTING     NEW YORK
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     10 ACCOUNTING     NEW YORK
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     10 ACCOUNTING     NEW YORK
 7369 SMITH      CLERK      7902 1980-12-17     800.00                      20 RESEARCH       DALLAS
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00            20 RESEARCH       DALLAS
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     20 RESEARCH       DALLAS
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     20 RESEARCH       DALLAS
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     20 RESEARCH       DALLAS
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     20 RESEARCH       DALLAS
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     20 RESEARCH       DALLAS
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     20 RESEARCH       DALLAS
 7369 SMITH      CLERK      7902 1980-12-17     800.00                      30 SALES          CHICAGO
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00            30 SALES          CHICAGO
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     30 SALES          CHICAGO
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     30 SALES          CHICAGO

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     30 SALES          CHICAGO
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     30 SALES          CHICAGO
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     30 SALES          CHICAGO
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     30 SALES          CHICAGO
 7369 SMITH      CLERK      7902 1980-12-17     800.00                      40 OPERATIONS     BOSTON
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00            40 OPERATIONS     BOSTON
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     40 OPERATIONS     BOSTON
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     40 OPERATIONS     BOSTON
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     40 OPERATIONS     BOSTON
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     40 OPERATIONS     BOSTON
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     40 OPERATIONS     BOSTON
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     40 OPERATIONS     BOSTON

32 rows selected

外连接(outer join)并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 -- 甚至这条记录没有匹配的记录
也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.在标准的 SQL 语言中, 外连接没有
隐式的连接符号.这意味着左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为
NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果.

SQL> select * from emp left outer join dept on emp.deptno=dept.deptno;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
 7369 SMITH      CLERK      7902 1980-12-17     800.00                                        
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00                              
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     30 SALES          CHICAGO
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     20 RESEARCH       DALLAS
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     30 SALES          CHICAGO
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     30 SALES          CHICAGO
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     10 ACCOUNTING     NEW YORK
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     20 RESEARCH       DALLAS

8 rows selected

右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已. 如果 A 表右连接 B 表, 那么"右表" B 中的每一行
在连接表中至少会出现一次. 如果 B 表的记录在"左表" A 中未找到匹配行, 连接表中来源于 A 的列的值设为 NULL.

SQL> select * from emp 

right outer join dept on emp.deptno=dept.deptno;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10     10 ACCOUNTING     NEW YORK
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20     20 RESEARCH       DALLAS
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20     20 RESEARCH       DALLAS
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30     30 SALES          CHICAGO
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30     30 SALES          CHICAGO
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30     30 SALES          CHICAGO
                                                                            40 OPERATIONS     BOSTON

7 rows selected

全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.

SQL> select * from emp full outer join dept on emp.deptno=dept.deptno;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ------ -------------- -------------
      7369 SMITH      CLERK           7902 1980-12-17         800                                             
      7499 ALLEN      SALESMAN        7698 1981-2-20         1600        300                                  
      7521 WARD       SALESMAN        7698 1981-2-22         1250        500         30     30 SALES          CHICAGO
      7566 JONES      MANAGER         7839 1981-4-2          2975                    20     20 RESEARCH       DALLAS
      7654 MARTIN     SALESMAN        7698 1981-9-28         1250       1400         30     30 SALES          CHICAGO
      7698 BLAKE      MANAGER         7839 1981-5-1          2850                    30     30 SALES          CHICAGO
      7782 CLARK      MANAGER         7839 1981-6-9          2450                    10     10 ACCOUNTING     NEW YORK
      7788 SCOTT      ANALYST         7566 1987-4-19         3000                    20     20 RESEARCH       DALLAS
                                                                                            40 OPERATIONS     BOSTON

9 rows selected

连接算法是SQL SERVER实体处理中策略，主要包含以下三种：
1.循环嵌套连接，如果一个连接的输入相当小，而另一个连接的输入相当的大时，而且在连接列建有索引，此时访问两个表时，循环嵌套连接
是最快的。在简单的情况下，先扫描整个表或者索引，首先访问输入小的表的连接字段的索引，然后得到一个值A，再访问输入大的表的索引，
找到和A匹配的数据，返回该条记录，以此对输入较小的表的记录进行循环操作，如果输入较小的表输入为10条记录，则总的要循环10次。如果
连接列没有建立索引，则数据库优化器会选择建立暂时性索引，连接完成后再清除掉索引。

SELECT A.*,B.* FROM A INNER JOIN B ON A.ID = B.PK;

READ(TABLE,i,FIELD)为取表TABLE第i条记录字段FIELD的值，假设上述查询ID和PK都为INT类型; SCAN(TABLE,FIELD, VALUE)为扫描TABLE表，
按照字段FIELD的索引做扫描，检测是否存在VALUE值；

int i = 1;

int temp = 0;

for(i; i < 11;)

{

temp = READ(A,i,ID);

SCAN(B,PK, temp);

}

2.合并连接，如果合并连接的输入都非常大，而且按照连接列排序，要访问这两个表，合并连接就是最快的选择。在进行连接时，如连接字段
没有建立索引，则首先要根绝连接字段对两个表进行排序。如有索引则直接扫描索引。总之先得到两个表的排序记录，然后以此对比两个表对
应的记录两个字段的值是否相等。例如：
SELECT A.*,B.* FROM A INNER JOIN B ON A.ID = B.PK;
其中A标有x条记录，B表有y条记录。 READ(TABLE,i,FIELD)为取表TABLE第i条记录字段FIELD的值。则上述查询可以理解为：
首先对表A,B按照字段ID进行排序，然后取排序后的两个表的第一条记录RA1和RB1，然后判断RA1中的ID何RB1中的PK的大小关系，则执行伪代码
如下：

int i = 1,int j = 1;

FOR(i; i < x +1, j< y + 1; )

{

IF(READ(A,i,ID) = READ(A,i,ID) ){i ++ ; j++;}

IF(READ(A,i,ID) < READ(A,i,ID) ) {i ++;}

IF(READ(A,i,ID) > READ(A,i,ID) ){j++;}

}
3.哈希连接，哈希连接通常可以有效的处理大型，无序，无索引的输入。一般情况看下，查询优化器会优先考虑合并连接和嵌套循环连接方式
。但是如果表没有建立合理的索引，或者前两种无法有效的处理时，就会采用哈希连接方式。做哈希连接时，首先会在内存中，依据连接字段
的哈希值建立两个输入源的HASH表，对其中一个进行分组HASH，为查询 SELECT TABLE_A.*, TABLE_B.* FROM TABLE INNER JOIN TABLE_B ON
TABLE_A.B = TABLE_B.Y的执行图，首先依据字段B在内存中建立TABLE_A的HASH表MEMORY_A，依据字段Y建立TABLE_B在内存中HASH表MEMORY_B，
然后依据内存中HASH表进行比较操作，操作顺序和合并连接类似，两表以此循环，知道一表结束

bazingaz

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle sql join

SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 790
复制链接

扫一扫