/*
一、多表查詢
簡言之，根據特定的連接配接條件從不同的表中擷取所需的資料
笛卡爾集的産生條件：
省略連接配接條件
連接配接條件無效
第一個表中的所有行與第二個表中的所有行相連接配接
二、多表查詢文法：*/
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
/*
但要注意where 不要省了，省略where 即為笛卡爾集，而且where 條件要有效，
兩張表間有一個相同的字段，才好進行有效的多表查詢
查詢時列名前，加表名或表别名前辍（如果字段在兩個表中是唯一的可以不加）
為了簡化SQL書寫，可為表名定義别名，格式：from 表名别名
如：from emp e,dept d
建議使用表的别名及表字首，使用表别名可以簡化查詢，而使用表字首則可以提高查詢性能
例：查詢每個員工的工号，姓名，工資，部門名和工作地點*/
select empno,ename,sal,dname,loc from emp,dept
where emp.deptno=dept.deptno;
三、多表連接配接類型：
從資料顯示方式來講有：内連接配接和外連接配接。
内連接配接：隻傳回滿足連接配接條件的資料。
外連接配接：除了傳回滿足連接配接條的行以外，還傳回左（右）表中，不滿足條件的行，
稱為左（右）連接配接
内連接配接*/
where emp.deptno=dept.deptno; --(Oracle 8i 及以前的寫法)
--内連接配接的另一種寫法：
select empno,ename,job,sal,dept.deptno,dname,loc
from emp join dept on (emp.deptno=dept.deptno); --
(SQL 99的寫法)
外連接配接：
兩個表的查詢中，使用外連接配接可以查詢另一個表或者兩個中不滿足連接配接條件的資料。
外連接配接的符号是（＋），（＋）要放在字段名後。（＋）對面的那個表，會全部顯示。
外連接配接文法*/
SELECT table1.column, table2.column --右外連接配接
FROM table1, table2
WHERE table1.column(+) = table2.column;
SELECT table1.column, table2.column --左外連接配接
WHERE table1.column = table2.column(+);
--例：
from emp,dept
where emp.deptno(+)=dept.deptno; -- (Oracle 8i 及以前的寫法)
--另一種寫法（右連接配接）： -- (SQL 99的寫法)
from emp right join dept on (emp.deptno=dept.deptno);
--左連接配接： (SQL 99的寫法)
from emp left join dept on (emp.deptno=dept.deptno);
--全連接配接（滿連接配接） (SQL 99的寫法)
select empno,ename,job,sal,d.deptno,dname,loc
from emp e full join dept d on (e.deptno=d.deptno);
自然連接配接 (SQL 99的寫法)
以兩個表具有相同的字段的所有列為基礎，可采用自然連接配接（natural join)
它将選擇兩個表中那些在所有比對的列中值相等的行。
如果列具有相同的名稱，但資料類型能夠不同，則會報錯*/
select empno,ename,job,sal,deptno,dname,loc
from emp natural join dept;
自連接配接：
将自身表的一個鏡像當另一個表來對待。*/
select ... from emp e,emp d ...
--下面列出SQL 99的文法供參考
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
--在下面的文法中
table1.column --指明從中檢索資料的表和列
CROSS JOIN --傳回兩個表的笛卡爾集
NATURAL JOIN --根據相同的列名連接配接兩個表
JOIN table
USING column_name --根據列名執行等值連接配接
JOIN table ON
table1.column_name --根據ON 子句中的條件執行等值連接配接
= table2.column_name
LEFT/RIGHT/FULL OUTER
使用using子句建立連接配接
如果幾個列具有相同的名稱，但是資料類型不比對，那麼可以使用using子句來修改natural join
子句以指定要用于等值連接配接的列
在多個列比對時，使用using 子句隻比對一個列
在引用列中不要使用表名或别名
natural join 和using 子句是互不相容的 */
--例：
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
--下面的語句無效，因為where 子句中限定了location_id
WHERE d.location_id = 1400;
ORA-25154: column part of USING clause cannot have qualifier
--注意：
-- 兩個表中名稱相同的列在使用時不能有任何限定符，這一限制同樣适用于natural join
四、示範： */
--笛卡爾集
SQL> select empno,ename,dname from emp,dept;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7654 MARTIN ACCOUNTING
7698 BLAKE ACCOUNTING
7782 CLARK ACCOUNTING
7788 SCOTT ACCOUNTING
7839 KING ACCOUNTING
7844 TURNER ACCOUNTING
7876 ADAMS ACCOUNTING
--中間結果省略
56 rows selected.
--使用cross join 實作交叉連接配接，即笛卡爾集
SQL> select empno,ename,dname from emp
2 cross join dept;
--等值連接配接(Oracle 寫法)
SQL> select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;
7369 SMITH RESEARCH
7499 ALLEN SALES
7876 ADAMS RESEARCH
--部分結果省略
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
14 rows selected.
--等值連接配接(SQL 99 寫法)
SQL> select e.empno,e.ename,d.dname from emp e
2 inner join dept d
3 on e.deptno = d.deptno;
--部分結果省略
--注意：表别名不支援as 用法
SQL> select e.empno,e.ename,d.dname from emp as e
select e.empno,e.ename,d.dname from emp as e
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
--等值連接配接并增加條件
SQL> select e.empno,e.ename,d.dname from emp e,
2 dept d
3 where d.deptno = e.deptno
4 and e.ename = 'SCOTT';
7788 SCOTT RESEARCH
--非等值連接配接
--查詢雇員的姓名、薪水、級别且部門為的記錄
SQL> select ename,sal,grade
2 from emp,salgrade
3 where sal between losal and hisal
4 and emp.deptno = 20;
ENAME SAL GRADE
---------- ---------- ----------
SCOTT 3000 4
FORD 3000 4
JONES 2975 4
ADAMS 1100 1
SMITH 800 1
--使用SQL 99寫法實作上述功能
SQL> select e.ename,e.sal,s.grade
2 from emp e
3 join salgrade s
4 on e.sal between losal and hisal
5 and e.deptno = 20;
SMITH 800 1
--右外連接配接
--注意：右外連接配接時，加号在等号的左邊
--可以看到，左表emp中的列有為空值的
SQL> select e.ename,e.deptno,d.dname
2 from emp e,dept d
3 where e.deptno(+) = d.deptno;
ENAME DEPTNO DNAME
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 10 ACCOUNTING
JONES 20 RESEARCH
FORD 20 RESEARCH
ADAMS 20 RESEARCH
SMITH 20 RESEARCH
SCOTT 20 RESEARCH
WARD 30 SALES
TURNER 30 SALES
ALLEN 30 SALES
JAMES 30 SALES
BLAKE 30 SALES
MARTIN 30 SALES
OPERATIONS
15 rows selected.
--使用SQL 99寫法實作右外連接配接
SQL> select e.ename,e.deptno,d.dname
2 from emp e
3 right join dept d
4 on e.deptno = d.deptno ;
--左外連接配接
--注意：左外連接配接時，加号在等号的右邊
SQL> select d.dname,e.ename,e.deptno
2 from dept d,emp e
3 where d.deptno = e.deptno(+)
4 order by d.deptno;
DNAME ENAME DEPTNO
-------------- ---------- ----------
ACCOUNTING CLARK 10
ACCOUNTING KING 10
ACCOUNTING MILLER 10
RESEARCH JONES 20
RESEARCH FORD 20
RESEARCH ADAMS 20
RESEARCH SMITH 20
RESEARCH SCOTT 20
SALES WARD 30
SALES TURNER 30
SALES ALLEN 30
SALES JAMES 30
SALES BLAKE 30
SALES MARTIN 30
OPERATIONS
--使用SQL 99寫法實作左外連接配接
2 from dept d
3 left join emp e
4 on d.deptno = e.deptno
5 order by d.deptno;
15 rows selected.
--自連接配接
SQL> select e.ename || ' works for ' || m.ename
2 from emp e,emp m
3 where e.empno = m.mgr;
E.ENAME||'WORKSFOR'||M.ENAME
-------------------------------
FORD works for SMITH
BLAKE works for ALLEN
BLAKE works for WARD
KING works for JONES
BLAKE works for MARTIN
KING works for BLAKE
KING works for CLARK
JONES works for SCOTT
BLAKE works for TURNER
SCOTT works for ADAMS
BLAKE works for JAMES
JONES works for FORD
CLARK works for MILLER
13 rows selected.
--自然連接配接
SQL> select empno,ename,job,deptno,dname,loc
2 from emp
3 natural join dept;
EMPNO ENAME JOB DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7369 SMITH CLERK 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 30 SALES CHICAGO
7521 WARD SALESMAN 30 SALES CHICAGO
7566 JONES MANAGER 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 30 SALES CHICAGO
7698 BLAKE MANAGER 30 SALES CHICAGO
7782 CLARK MANAGER 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 20 RESEARCH DALLAS
7839 KING PRESIDENT 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 30 SALES CHICAGO
7876 ADAMS CLERK 20 RESEARCH DALLAS
7900 JAMES CLERK 30 SALES CHICAGO
7902 FORD ANALYST 20 RESEARCH DALLAS
7934 MILLER CLERK 10 ACCOUNTING NEW YORK
--使用using 子句建立連接配接
SQL> select e.empno,e.ename,d.dname,d.loc
3 join dept d
4 using (deptno)
5 where deptno in (20,40);
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7566 JONES RESEARCH DALLAS
7788 SCOTT RESEARCH DALLAS
7876 ADAMS RESEARCH DALLAS
7902 FORD RESEARCH DALLAS
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)