一、组合相关的行
相对查询单表中的数据来说，平时更常见的需求是要在多个表中返回数据。比如，显示部门10的员工编码、姓名及所在部门名称和工作地址。

select a.empno,a.deptno,b.dname
from emp a inner join dept b
on(a.deptno=b.deptno)
where a.deptno=10;
EMPNO DEPTNO DNAME
----- ------ --------------
7782 10 ACCOUNTING
7839 10 ACCOUNTING
7934 10 ACCOUNTING

另外有下面写法：

select a.empno,a.deptno,b.dname
from emp a,dept b
where a.deptno=b.deptno
EMPNO DEPTNO DNAME
----- ------ --------------
7369 20 RESEARCH
7499 30 SALES
7521 30 SALES
7566 20 RESEARCH
7654 30 SALES
7698 30 SALES
7782 10 ACCOUNTING
7788 20 RESEARCH
7839 10 ACCOUNTING
7844 30 SALES
7876 20 RESEARCH
7900 30 SALES
7902 20 RESEARCH
7934 10 ACCOUNTING

14 rows selected


其中，JOIN的写法是SQL-92的标准，当有多个表关联时，JOIN方式的写法能更清楚地看清各表之间的关系，因此，建议大家写查询语句时优先使用JOIN的写法。

二、从执行计划看IN、EXISTS 和 INNER JOIN效率
下面先 创建一个表 emp2.

drop index IDX_ENAME;
DROP TABLE emp2 PURGE ;
CREATE TABLE emp2 AS
SELECT ename,job,sal,comm FROM emp WHERE job ='CLERK';

要求返回与表emp2(empno,job,sal)中数据相匹配的emp(empno,ename,job,sal,deptno)
信息。
有IN、EXISTS、INNER JOIN三种写法。为了加强理解，请大家看一下三种写法及其PLAN(此处用的是Oracle 11g)。

in写法
SQL> explain plan for select empno,ename,job,sal,deptno from emp where (ename,job,sal) in(select ename,job,sal from emp2);

Explained


SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039873364
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 67 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 15 | 780 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 4 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ENAME"="ENAME" AND "JOB"="JOB" AND "SAL"="SAL")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

19 rows selected


exists写法
SQL> EXPLAIN PLAN FOR SELECT empno,ename,job,sal,deptno FROM emp a
2 WHERE EXISTS (SELECT NULL
3 FROM emp2 b
4 WHERE b.ename = a.ename AND b.job = a.job
5 AND b.sal = a.sal) ;

Explained


SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039873364
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 67 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 15 | 780 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 4 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND
"B"."SAL"="A"."SAL")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

20 rows selected


因为子查询的JOIN列(emp2.ename,emp2.job,ernp2.sal)没有重复行，所以这个查询可以直接改为INNER JOIN。
SQL> EXPLAIN PLAN FOR SELECT a.empno,a.ename,a.job,a.sal,a.deptno from emp a
2 INNER JOIN emp2 b ON (b.ename = a.ename AND b.job = a.job AND b.sal =a.sal);

Explained


SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 166525280
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 268 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 268 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP2 | 4 | 60 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 15 | 780 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND
"B"."SAL"="A"."SAL")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

20 rows selected


或许与大家想象的不一样，以上三个PLAN中JOIN写法利用了HASH JOIN(哈希连接），其他两种运用的都是HASH JOIN SEMI(哈希半连接），说明在这个语句中的IN与EXISTS效率是一样的。所以，在不知哪种写法高效时应查看PLAN,而不是去记固定的结论。

三、INNER JOIN、LEFT JOIN、RIGHT JOIN 、FULL JOIN、自关联解析
有很多人对这几种连接方式，特别是LEFT JOIN与RIGHT JOIN分不清，下面通过案例来解析一下。
首先建立两个测试用表。

DROP TABLE L PURGE ; DROP TABLE R PURGE;
--左表
CREATE TABLE L AS
SELECT 'left_1' AS str , '1' AS v FROM dual UNION ALL
SELECT 'left_2' AS str , '2' AS v FROM dual UNION ALL
SELECT 'left_3' AS str , '3' AS v FROM dual UNION ALL
SELECT 'left_4' AS str , '4' AS v FROM dual;
--右表
CREATE TABLE R AS
SELECT 'right_3' AS str , '3' AS v,1 as status FROM dual UNION ALL
SELECT 'right_4' AS str , '4' AS v,0 as status FROM dual UNION ALL
SELECT 'right_5' AS str , '5' AS v,0 as status FROM dual UNION ALL
SELECT 'right_6' AS str , '6' AS v,0 as status FROM dual;

1、INNER JOIN 的 特点
该方式返回两表相匹配的数据，左表的"1、2"以及右表的"5、6"都没有显示。
JOIN写法

SQL>
SQL> select l.str as left_str, r.str as right_str
2 from l
3 inner join r
4 on (l.v = r.v)
5 order by 1, 2;

LEFT_STR RIGHT_STR
-------- ---------
left_3 right_3
left_4 right_4

2、LEFTJOIN的特点
该方式的左表为主表，左表返回所有的数据，右表中只返回与左表匹配的数据，"5、6"都没有显示。
join写法：

select l.str as left_str, r.str as right_str
from l
left join r
on (l.v = r.v)
order by 1, 2;
LEFT_STR RIGHT_STR
-------- ---------
left_1
left_2
left_3 right_3
left_4 right_4

加（+）写法

select l.str as left_str, r.str as right_str
from l, r
where l.v = r.v(+)
order by 1, 2;

3、RIGHT JOIN的特点
该方式的右表为主表，左表中只返回与右表匹配的数据"3、4",而"1、2"都没有显示，右表返回所有的数据。
join写法

select l.str as left_str, r.str as right_str
from l
right join r
on (l.v = r.v)
order by 1, 2;
LEFT_STR RIGHT_STR
-------- ---------
left_3 right_3
left_4 right_4
right_5
right_6

加（+）写法

select l.str as left_str, r.str as right_str
from l, r
where l.v(+) = r.v
order by 1, 2;

4、FULL JOIN的特点
该方式的左右表均返回所有的数据，但只有相匹配的数据显示在同一行，非匹配的行只显示一个表的数据。
JOIN写法

select l.str as left_str, r.str as right_str
from l
full join r
on (l.v = r.v)
order by 1, 2;
LEFT_STR RIGHT_STR
-------- ---------
left_1
left_2
left_3 right_3
left_4 right_4
right_5
right_6

6 rows selected

FULL JOIN 无(+ )的写法。

6、自关联
表emp中有一个字段mgr,其中是主管的编码（对应于emp.empno),如：
(EMPNO:7698,ENAME:BLAKE)-->(MGR:7839)-->(EMPNO:7839,ENAME:KING),说明BLAKE的主管就是KING
如何根据这个信息返回主管的姓名呢？
这里用到的就是自关联。也就是两次查询表emp,分别取不同的别名，这样就可以当作是两个表，后面的任务就是将这两个表和JOIN连接起来就可以。
为了方便理解，这里用汉字作为别名，并把相关列一起返回。

SELECT 员工.empno AS 职工编码,
员工.ename AS 职工姓名,
员工.job AS 工作,
员工.mgr AS 员工表_主管编码,
主管.empno AS 主管表＿主管编码,
主管.ename AS 主管姓名
FROM emp 员工
LEFT JOIN emp 主管
ON (员工.mgr = 主管.empno)
ORDER BY 1;
职工编码 职工姓名 工作 员工表_主管编码 主管表＿主管编码 主管姓名
----- ---------- --------- -------- -------- ----------
1001 test
7369 SMITH CLERK 7902 7902 FORD
7499 ALLEN SALESMAN 7698 7698 BLAKE
7521 WARD SALESMAN 7698 7698 BLAKE
7566 JONES MANAGER 7839 7839 KING
7654 MARTIN SALESMAN 7698 7698 BLAKE
7698 BLAKE MANAGER 7839 7839 KING
7782 CLARK MANAGER 7839 7839 KING
7788 SCOTT ANALYST 7566 7566 JONES
7839 KING PRESIDENT
7844 TURNER SALESMAN 7698 7698 BLAKE
7876 ADAMS CLERK 7788 7788 SCOTT
7900 JAMES CLERK 7698 7698 BLAKE
7902 FORD ANALYST 7566 7566 JONES
7934 MILLER CLERK 7782 7782 CLARK

15 rows selected


总结
这一章主要介绍两块，之所以拿出来这两块说是因为：

IN、EXISTS 和 INNER JOIN这三者或则说前两者的效率，博主在日常工作和面试过程中，经常遇到大家斩钉截铁的说in效率远远低于EXISTS 和 INNER JOIN，这类人大都是自己没有亲测，从网上搜了相关信息就记下来了，有些时候，网上的内容并不代表绝对正确，就像网上很多文章说scala的入参不能超过22个参数一样~
其次，表的INNER JOIN、LEFT JOIN、RIGHT JOIN 、FULL JOIN、自关联这5种关联和简写方式，在工作中也很容易出错，所以在写这篇文章时候，博主自己也做个总结~
————————————————

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs
同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack