select查询之四：连接查询
其中外连接又分全外连接、左外连接与右外连接。
1、左外连接：
查询所有员工的信息并且列出员工的部门名称
SQL> select e.last_name,d.department_id,d.department_name
2 from employees e
3 left outer join departments d
4 on e.department_id = d.department_id;
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Urman 100 Finance
Sciarra 100 Finance
Chen 100 Finance
Faviet 100 Finance
Greenberg 100 Finance
Gietz 110 Accounting
Higgins 110 Accounting
Grant
107 rows selected.
2、右外连接：
在HR用户，查询没有任何职员的部门
SQL> select e.employee_id,d.department_name,d.department_id
2 from employees e
3 right outer join departments d
4 on e.department_id = d.department_id;
EMPLOYEE_ID DEPARTMENT_NAME DEPARTMENT_ID
----------- ------------------------------ -------------
Benefits 160
Manufacturing 170
Construction 180
Contracting 190
Operations 200
IT Support 210
NOC 220
IT Helpdesk 230
Government Sales 240
Retail Sales 250
Recruiting 260
......
3、全外链接：
SQL> select e.last_name,d.department_id,d.department_name
2 from employees e
3 full outer join departments d
4 on e.department_id = d.department_id;
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Ozer 80 Sales
Bloom 80 Sales
Fox 80 Sales
Smith 80 Sales
Bates 80 Sales
Kumar 80 Sales
Abel 80 Sales
Hutton 80 Sales
Taylor 80 Sales
Livingston 80 Sales
Grant
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
210 IT Support
160 Benefits
150 Shareholder Services
250 Retail Sales
140 Control And Credit
260 Recruiting
200 Operations
120 Treasury
270 Payroll
130 Corporate Tax
180 Construction
123 rows selected.
4、With字句（右外连接）：
SQL> with
2 t1 as
3 (select 1 as id,'a' as name from dual
4 union all
5 select 2,'b' from dual
6 union all
7 select 3,'c' from dual),
8 t2 as
9 (select 2 as id,'bb' as name from dual
10 union all
11 select 3,'cc' from dual
12 union all
13 select 4,'dd' from dual)
14 select t1.id,t1.name,t2.id,t2.name
15 from t1 right outer join t2
16 on t1.id = t2.id;
ID N ID NA
---------- - ---------- --
2 b 2 bb
3 c 3 cc
4 dd
5、自连接：查询每个领导都管理哪些员工
LAST_NAME LAST_NAME MANAGER_ID
------------------------- ------------------------- ----------
King Cambrault 100
King De Haan 100
King Errazuriz 100
King Fripp 100
King Hartstein 100
King Kaufling 100
King Kochhar 100
King Mourgos 100
King Partners 100
King Raphaely 100
King Russell 100
LAST_NAME LAST_NAME MANAGER_ID
------------------------- ------------------------- ----------
King Vollman 100
King Weiss 100
King Zlotkey 100
Kochhar Baer 101
Kochhar Greenberg 101
Kochhar Higgins 101
Kochhar Mavris 101
Kochhar Whalen 101
.......
Hartstein Fay 201
Higgins Gietz 205
106 rows selected.
6、不等链接：
在SCOTT用户，查询EMP，SALGRADE这两张表，显示每个人的级别。显示字段：人员id，姓名，级别。
SQL> select e.empno,e.ename,g.grade
2 from emp e
3 join salgrade g
4 on e.sal between g.losal and g.hisal;
EMPNO ENAME GRADE
---------- ---------- ----------
7369 SMITH 1
7900 JAMES 1
7876 ADAMS 1
7521 WARD 2
7654 MARTIN 2
7934 MILLER 2
7844 TURNER 3
7499 ALLEN 3
14 rows selected.
7、复杂内连接：
SQL> select empno,ename,dname
2 from emp e,dept d
3 where e.deptno=d.deptno
4 and d.deptno=30;
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7654 MARTIN SALES
7698 BLAKE SALES
7844 TURNER SALES
7900 JAMES SALES
6 rows selected.
8、交叉连接：
2 from students,courses;
STUDENT_ID NAME COURSE_ID COURSE_NAME
---------- --------------- ---------- ------------------------------
45211 Mar 1002 Earth and Land
45212 Maro 1002 Earth and Land
45221 July 1002 Earth and Land
45222 Marry 1002 Earth and Land
45223 Mare 1002 Earth and Land
45214 Jone 1002 Earth and Land
45215 Kaha 1002 Earth and Land
45216 Kaka 1002 Earth and Land
45211 Mar 1003 Earth Song
45212 Maro 1003 Earth Song
45221 July 1003 Earth Song
STUDENT_ID NAME COURSE_ID COURSE_NAME
---------- --------------- ---------- ------------------------------
45216 Kaka 1102 With the Earth
56 rows selected.
左外连接，缺右表信息，右外连接，缺左表信息，
全连接就是将左右外连接联合起来。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2125909/ ，如需转载，请注明出处，否则将追究法律责任。
