連線查詢，有自連線，內連線，外連線與交叉連線，交叉連線也叫笛卡爾積查詢。
其中外連線又分全外連線、左外連線與右外連線。

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、交叉連線：

SQL> select student_id,name,course_id,course_name
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.

其中，對於左外連線與右外連線，個人理解就是：
左外連線，缺右表資訊，右外連線，缺左表資訊，
全連線就是將左右外連線聯合起來。

select查詢之四：連線查詢

相關文章