什么是多表查詢(xún)？ 多表查詢(xún)就是從多個(gè)表中獲取數(shù)據(jù)。 1、笛卡爾集 笛卡爾集是集合中的一種。假設(shè)A和B都是集合，A和B的笛卡爾集用 A B來(lái)表示。即A B所形成的集合叫做笛卡爾集。 下表中，部門(mén)表員工表即為部門(mén)表和員工表的笛卡爾集。（可以看出，部門(mén)表員工表中有三條數(shù)據(jù)并不正確。） Oracle專(zhuān)題7之多表查詢(xún) a、演示笛卡爾集 創(chuàng)建部門(mén)表、和員工表并添加數(shù)據(jù)： SQL> create table 部門(mén)表(deptno number primary key,dname varchar2(10)); 表已創(chuàng)建。 SQL> create table 員工表(empno number primary key, ename varchar2(10), deptno number, foreign key(deptno) references 部門(mén)表(deptno)); 表已創(chuàng)建。 SQL> insert into 部門(mén)表(deptno, dname) values(10,'銷(xiāo)售部'); 已創(chuàng)建 1 行。 SQL> insert into 部門(mén)表(deptno, dname) values(20,'人事部'); 已創(chuàng)建 1 行。 SQL> insert into 員工表(empno,ename,deptno) values(1,'張三',10); 已創(chuàng)建 1 行。 SQL> insert into 員工表(empno,ename,deptno) values(2,'李四',20); 已創(chuàng)建 1 行。 SQL> insert into 員工表(empno,ename,deptno) values(3,'王五',10); 已創(chuàng)建 1 行。 部門(mén)表和員工表的集合： SQL> select empno, ename, 員工表.deptno, 部門(mén)表.deptno, dname from 部門(mén)表， 員工表; EMPNO ENAME DEPTNO DEPTNO DNAME 1 張三 10 10 銷(xiāo)售部 2 李四 20 10 銷(xiāo)售部 3 王五 10 10 銷(xiāo)售部 1 張三 10 20 人事部 2 李四 20 20 人事部 3 王五 10 20 人事部 已選擇6行。 b、sql語(yǔ)句多表查詢(xún)顯示滿(mǎn)足條件的表的集合 SQL> select empno, ename, 員工表.empno, 部門(mén)表.deptno, dname from 部門(mén)表, 員工表 where 部門(mén)表.deptno = 員工表.deptno; EMPNO ENAME EMPNO DEPTNO DNAME 1 張三 1 10 銷(xiāo)售部 2 李四 2 20 人事部 3 王五 3 10 銷(xiāo)售部 為了避免笛卡爾集，可以在where子句中加入有效的連接條件。 連接條件至少有n-1個(gè)，n代表表的個(gè)數(shù)。 在表中有相同列時(shí)，在列名之前加上表名前綴。 2、等值與非等值連接 等值連接：是指使用等值比較符（=）指定連接條件的查詢(xún)。舉例：查詢(xún)員工信息，要求顯示：?jiǎn)T工號(hào)、姓名、職位、部門(mén)名稱(chēng)。 使用表名前綴在多個(gè)表中區(qū)分相同的列： SQL> select empno, ename, job, dname from emp, dept where emp.deptno = dept.deptno; EMPNO ENAME JOB DNAME 7934 MILLER CLERK ACCOUNTING 7782 CLARK MANAGER ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7566 JONES MANAGER RESEARCH 7951 EASON ANALYST RESEARCH 7369 G_EASON CLERK RESEARCH 7902 FORD ANALYST RESEARCH 7876 ADAMS CLERK RESEARCH 7788 SCOTT ANALYST RESEARCH 7499 ALLEN SALESMAN SALES 7844 TURNER SALESMAN SALES 7900 JAMES CLERK SALES 7521 WARD SALESMAN SALES 7698 BLAKE MANAGER SALES 7654 MARTIN SALESMAN SALES 已選擇15行。 使用表名前綴可以提高執(zhí)行效率： SQL> select emp.empno, emp.ename, emp.job, dept.dname from emp, dept where emp.deptno = dept.deptno; EMPNO ENAME JOB DNAME 7934 MILLER CLERK ACCOUNTING 7782 CLARK MANAGER ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7566 JONES MANAGER RESEARCH 7951 EASON ANALYST RESEARCH 7369 G_EASON CLERK RESEARCH 7902 FORD ANALYST RESEARCH 7876 ADAMS CLERK RESEARCH 7788 SCOTT ANALYST RESEARCH 7499 ALLEN SALESMAN SALES 7844 TURNER SALESMAN SALES 7900 JAMES CLERK SALES 7521 WARD SALESMAN SALES 7698 BLAKE MANAGER SALES 7654 MARTIN SALESMAN SALES 已選擇15行。 使用表的別名，可以簡(jiǎn)化連接查詢(xún)并提高查詢(xún)性能： SQL> select e.empno, e.ename, e.job, d.dname from emp e, dept d where e.deptno = d.deptno; EMPNO ENAME JOB DNAME 7934 MILLER CLERK ACCOUNTING 7782 CLARK MANAGER ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7566 JONES MANAGER RESEARCH 7951 EASON ANALYST RESEARCH 7369 G_EASON CLERK RESEARCH 7902 FORD ANALYST RESEARCH 7876 ADAMS CLERK RESEARCH 7788 SCOTT ANALYST RESEARCH 7499 ALLEN SALESMAN SALES 7844 TURNER SALESMAN SALES 7900 JAMES CLERK SALES 7521 WARD SALESMAN SALES 7698 BLAKE MANAGER SALES 7654 MARTIN SALESMAN SALES 已選擇15行。 多個(gè)連接條件時(shí)使用AND操作符：在連接查詢(xún)中，當(dāng)有多個(gè)連接條件時(shí)，使用AND指定其他條件。 SQL> select e.empno, e.ename, e.job, d.dname from emp e, dept d where e.deptno = d.deptno and e.deptno = 10; EMPNO ENAME JOB DNAME 7782 CLARK MANAGER ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7934 MILLER CLERK ACCOUNTING 不等值連接：不等值連接是指使用除等值比較符之外的其他比較操作符執(zhí)行連接查詢(xún)。舉例：顯示所有員工的員工號(hào)、姓名、工資以及其工資的等級(jí)。 SQL> select * from salgrade; GRADE LOSAL HISAL 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 SQL> select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between losal and hisal; EMPNO ENAME SAL GRADE 7369 G_EASON 800 1 7900 JAMES 950 1 7876 ADAMS 1100 1 7521 WARD 1250 2 7654 MARTIN 1250 2 7934 MILLER 1300 2 7844 TURNER 1500 3 7499 ALLEN 1600 3 7782 CLARK 2450 4 7698 BLAKE 2850 4 7566 JONES 2975 4 7951 EASON 3000 4 7902 FORD 3000 4 7788 SCOTT 3000 4 7839 KING 5000 5 已選擇15行。 3、Oracle外連接 外連接是標(biāo)準(zhǔn)連接的擴(kuò)展，它不僅會(huì)返回滿(mǎn)足連接條件的所有記錄，而且還會(huì)返回不滿(mǎn)足連接條件的部分記錄。 舉例：按照部門(mén)統(tǒng)計(jì)員工的人數(shù)，要求顯示：部門(mén)號(hào)，部門(mén)名稱(chēng)，和人數(shù)。 普通的表連接：（發(fā)現(xiàn)沒(méi)有顯示DEPTNO為40的記錄，原因是該部門(mén)沒(méi)有員工，但是本例中需要顯示該記錄。） SQL> select d.deptno, d.dname, count(e.empno) from dept d, emp e where d.deptno = e.deptno group by d.deptno, d.dname; DEPTNO DNAME COUNT(E.EMPNO) 10 ACCOUNTING 3 20 RESEARCH 6 30 SALES 6 外連接是使用（+）操作符來(lái)完成的。 右（外）連接語(yǔ)法格式：SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column;（返回table2表中的所有記錄，返回table1表中所有滿(mǎn)足條件的記錄） 左（外）連接語(yǔ)法格式：SELECT table1.column, table2.column FROM table1, table2 WHERE table1, table2 WHERE table1.column = table2.column(+);（返回table1表中的所有記錄，返回table2表中所有滿(mǎn)足條件的記錄） SQL> select d.deptno, d.dname, count(e.empno) from dept d, emp e where d.deptno = e.deptno(+) group by d.deptno, d.dname; DEPTNO DNAME COUNT(E.EMPNO) 10 ACCOUNTING 3 40 OPERATIONS 0 20 RESEARCH 6 30 SALES 6 SQL> 4、Oracle自連接 自連接的實(shí)質(zhì)是：將同一張表看成是多張表。 舉例：查詢(xún)所有員工的姓名以及其直屬上級(jí)的姓名。 SQL> select e.ename 員工名, m.ename 直屬上級(jí) from emp e, emp m where e.mgr = m.empno; 員工名 直屬上級(jí) FORD JONES SCOTT JONES EASON JONES JAMES BLAKE TURNER BLAKE MARTIN BLAKE WARD BLAKE ALLEN BLAKE MILLER CLARK ADAMS SCOTT CLARK KING BLAKE KING JONES KING G_EASON FORD 已選擇14行。 SQL> 5、SQL1999連接 為了簡(jiǎn)化連接查詢(xún)，使得連接查詢(xún)更加直觀、更容易編寫(xiě)。SQL：1999標(biāo)準(zhǔn)為連接查詢(xún)提供新語(yǔ)法，如下所示：SELECT table1.column_name, table2.column_name 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)];。 a、交叉連接cross join（了解） 返回笛卡爾集的操作：select d.dname, e.ename, d.deptno, e.deptno from dept d, emp e; crosss join連接也用于生成兩張表的笛卡爾集（叉集）。即：select d.dname, e.ename, d.deptno, e.deptno from dept d cross join emp e; SQL> select d.dname, e.ename, d.deptno, e.deptno from dept d cross join emp e; DNAME ENAME DEPTNO DEPTNO ACCOUNTING EASON 10 20 ACCOUNTING G_EASON 10 20 ACCOUNTING ALLEN 10 30 ACCOUNTING WARD 10 30 ACCOUNTING JONES 10 20 ACCOUNTING MARTIN 10 30 ACCOUNTING BLAKE 10 30 ACCOUNTING CLARK 10 10 ACCOUNTING SCOTT 10 20 ACCOUNTING KING 10 10 已選擇60行。 b、自然連接NATURAL JOIN 自然連接是一種特殊的等價(jià)連接，它將表中具有相同名稱(chēng)的列自動(dòng)進(jìn)行記錄匹配。自然連接不必指定任何同等連接條件。語(yǔ)法格式為：SELECT table1.column_name, table2.column_name FROM table1 NATURAL JOIN table2; 舉例：查詢(xún)員工名、工資以及所在部門(mén)名稱(chēng) SQL> select e.ename, e.sal, d.dname from dept d natural join emp e; ENAME SAL DNAME MILLER 1300 ACCOUNTING CLARK 2450 ACCOUNTING KING 5000 ACCOUNTING JONES 2975 RESEARCH EASON 3000 RESEARCH G_EASON 800 RESEARCH FORD 3000 RESEARCH ADAMS 1100 RESEARCH SCOTT 3000 RESEARCH ALLEN 1600 SALES TURNER 1500 SALES JAMES 950 SALES WARD 1250 SALES BLAKE 2850 SALES MARTIN 1250 SALES 已選擇15行。 c、內(nèi)連接 返回兩個(gè)表中相匹配的數(shù)據(jù)（只返回滿(mǎn)足條件的數(shù)據(jù)）。等值連接、非等值連接、自然連接都數(shù)據(jù)內(nèi)連接。 使用USING子句建立相等連接。例如：SELECT e.ename, e.sal, d.dname from dept d join emp e using(deptno); SQL> SELECT e.ename, e.sal, d.dname from dept d join emp e using(deptno); ENAME SAL DNAME MILLER 1300 ACCOUNTING CLARK 2450 ACCOUNTING KING 5000 ACCOUNTING JONES 2975 RESEARCH EASON 3000 RESEARCH G_EASON 800 RESEARCH FORD 3000 RESEARCH ADAMS 1100 RESEARCH SCOTT 3000 RESEARCH ALLEN 1600 SALES TURNER 1500 SALES JAMES 950 SALES WARD 1250 SALES BLAKE 2850 SALES MARTIN 1250 SALES 已選擇15行。 用ON子句建立相等連接。例如：SELECT e.ename, e.sal, d.dname from dept d join emp e on e.deptno = d.deptno; SQL> SELECT e.ename, e.sal, d.dname from dept d join emp e on e.deptno = d.deptno; ENAME SAL DNAME MILLER 1300 ACCOUNTING CLARK 2450 ACCOUNTING KING 5000 ACCOUNTING JONES 2975 RESEARCH EASON 3000 RESEARCH G_EASON 800 RESEARCH FORD 3000 RESEARCH ADAMS 1100 RESEARCH SCOTT 3000 RESEARCH ALLEN 1600 SALES TURNER 1500 SALES JAMES 950 SALES WARD 1250 SALES BLAKE 2850 SALES MARTIN 1250 SALES 已選擇15行。 d、左（外）連接 左連接用于返回滿(mǎn)足連接條件的數(shù)據(jù)，以及不滿(mǎn)足連接條件的左邊表的其他數(shù)據(jù)。（即：左表的全部記錄，右表滿(mǎn)足條件的記錄） 在SQL：1999標(biāo)準(zhǔn)中，左連接是通過(guò)LEFT[OUTER] JOIN選項(xiàng)來(lái)實(shí)現(xiàn)的。如：select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno = d.deptno; SQL> select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno = d.deptno; ENAME SAL DNAME MILLER 1300 ACCOUNTING CLARK 2450 ACCOUNTING KING 5000 ACCOUNTING JONES 2975 RESEARCH EASON 3000 RESEARCH G_EASON 800 RESEARCH FORD 3000 RESEARCH ADAMS 1100 RESEARCH SCOTT 3000 RESEARCH ALLEN 1600 SALES TURNER 1500 SALES JAMES 950 SALES WARD 1250 SALES BLAKE 2850 SALES MARTIN 1250 SALES OPERATIONS 已選擇16行。 e、右（外）連接 右連接用于返回滿(mǎn)足連接條件的左邊表的數(shù)據(jù)，以及不滿(mǎn)足連接條件的右邊表的其他數(shù)據(jù)。（即：右表中的全部記錄，左表滿(mǎn)足條件的記錄。）示例：select e.ename, e.sal, d.dname from dept d right join emp e on e.deptno = d.deptno; SQL> select e.ename, e.sal, d.dname from dept d right join emp e on e.deptno = d.deptno; ENAME SAL DNAME MILLER 1300 ACCOUNTING KING 5000 ACCOUNTING CLARK 2450 ACCOUNTING FORD 3000 RESEARCH ADAMS 1100 RESEARCH SCOTT 3000 RESEARCH JONES 2975 RESEARCH G_EASON 800 RESEARCH EASON 3000 RESEARCH JAMES 950 SALES TURNER 1500 SALES BLAKE 2850 SALES MARTIN 1250 SALES WARD 1250 SALES ALLEN 1600 SALES 已選擇15行。 f、完全（外）連接 完全連接用于返回滿(mǎn)足連接條件的數(shù)據(jù)，以及不滿(mǎn)足鏈接條件的左表和右表中的其他數(shù)據(jù)。（即：左表和右表的全部記錄。） 在SQL：1999標(biāo)準(zhǔn)中，完全連接是通過(guò)制定FULL [OUTER] JOIN 選項(xiàng)來(lái)實(shí)現(xiàn)的。示例：select e.ename, e.sal, d.dname from dept d full join emp e on e.deptno = d.deptno; SQL> select e.ename, e.sal, d.dname from dept d full join emp e on e.deptno = d.deptno; ENAME SAL DNAME EASON 3000 RESEARCH G_EASON 800 RESEARCH ALLEN 1600 SALES WARD 1250 SALES JONES 2975 RESEARCH MARTIN 1250 SALES BLAKE 2850 SALES CLARK 2450 ACCOUNTING SCOTT 3000 RESEARCH KING 5000 ACCOUNTING TURNER 1500 SALES ADAMS 1100 RESEARCH JAMES 950 SALES FORD 3000 RESEARCH MILLER 1300 ACCOUNTING OPERATIONS 已選擇16行。 6、set運(yùn)算符 set運(yùn)算符，即集合運(yùn)算符專(zhuān)門(mén)用于合并多條SELECT語(yǔ)句的結(jié)果，包括4種：UNION/UNION ALL 并集、INTERSECT 交集、MINUS 差集。 例如有集合A和集合B，那么集合A有1、2、3，集合B有3、4。 A UNION B有：1、2、3、4；A UNION ALL B有：1、2、3、3 、4； A INTERSECT B有：3；A MINUS B有：1、2；B MINUS A有：4； Oracle專(zhuān)題7之多表查詢(xún) a、UNION UNION操作符用于取得兩個(gè)結(jié)果集的并集，當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行，并且會(huì)以第一列的結(jié)果進(jìn)行升序排序。 舉例：合并顯示emp02表和emp02表中所有雇員的部門(mén)編號(hào)、員工號(hào)、員工姓名。 SQL> create table emp01 as select * from emp where deptno in(10,20); 表已創(chuàng)建。 SQL> create table emp02 as select * from emp where deptno in(20,30); 表已創(chuàng)建。 SQL> select deptno, empno, ename from emp01 union select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 10 7782 CLARK 10 7839 KING 10 7934 MILLER 20 7369 G_EASON 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 7951 EASON 30 7499 ALLEN 30 7521 WARD 30 7654 MARTIN 30 7698 BLAKE 30 7844 TURNER 30 7900 JAMES 已選擇15行。 b、UNION ALL union all 操作符用于取得兩個(gè)結(jié)果集的并集，單與union操作符不同，該操作符不會(huì)取消重復(fù)行，并且不會(huì)對(duì)結(jié)果集數(shù)據(jù)進(jìn)行排序。 SQL> select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 20 7951 EASON 20 7369 G_EASON 20 7566 JONES ...... 30 7844 TURNER 20 7876 ADAMS 30 7900 JAMES 20 7902 FORD 已選擇21行。 c、INTERSECT intersect操作符用于取得兩個(gè)結(jié)果集的交集，當(dāng)使用該操作符時(shí)，只會(huì)顯示同時(shí)存在于兩個(gè)結(jié)果集中的數(shù)據(jù)，并且會(huì)以第一列的結(jié)果進(jìn)行升序排序。 SQL> select deptno, empno, ename from emp01 intersect select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 20 7369 G_EASON 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 7951 EASON 已選擇6行。 d、MINUS minus操作符用于取得兩個(gè)結(jié)果集中的差集，當(dāng)使用該操作符時(shí)，只會(huì)顯示在第一個(gè)結(jié)果集中存在，在第二個(gè)結(jié)果集中不存在的數(shù)據(jù)，并且會(huì)以第一列的結(jié)果集進(jìn)行升序排序。 SQL> select deptno, empno, ename from emp01 minus select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 10 7782 CLARK 10 7839 KING 10 7934 MILLER e、控制結(jié)果排序 當(dāng)使用集合操作符UNION、INTERSECT和MINUS時(shí)，默認(rèn)情況下會(huì)自動(dòng)基于第一列進(jìn)行升序排序；而當(dāng)使用集合操作符UNION ALL時(shí)，不會(huì)進(jìn)行排序，為了控制結(jié)果的排序順序，可以使用ORDER BY子句。（如果兩個(gè)表查詢(xún)的結(jié)果中列名相同，則可以使用列名名稱(chēng)；如果兩個(gè)表中的列名不同，則必須使用列位置，1表示基于第1列，2表示基于第2列） 示例：select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by 2; SQL> select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by empn DEPTNO EMPNO ENAME 20 7369 G_EASON 20 7369 G_EASON 30 7499 ALLEN 30 7521 WARD 20 7566 JONES ...... 20 7902 FORD 10 7934 MILLER 20 7951 EASON 20 7951 EASON 已選擇21行。 SQL> select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by DEPTNO EMPNO ENAME 20 7369 G_EASON 20 7369 G_EASON 30 7499 ALLEN 30 7521 WARD ...... 20 7902 FORD 10 7934 MILLER 20 7951 EASON 20 7951 EASON 已選擇21行。 f、使用set操作符的注意事項(xiàng) 在SELECT列表中的列名和表達(dá)式在數(shù)量和數(shù)據(jù)類(lèi)型上要相對(duì)應(yīng)。
