溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×

忘記密碼？

登錄注冊×

獲取短信驗(yàn)證碼

其他方式登錄

點(diǎn)擊登錄注冊即表示同意《億速云用戶服務(wù)條款》

用戶登錄×

賬戶密碼登錄

請使用微信掃描上方二維碼

使用幫助

請求超時！

請點(diǎn)擊重新獲取二維碼

Oracle專題7之多表查詢

發(fā)布時間：2020-08-10 18:56:49 來源：網(wǎng)絡(luò) 閱讀：736 作者：糖醋白糖欄目：MySQL數(shù)據(jù)庫

什么是多表查詢？
多表查詢就是從多個表中獲取數(shù)據(jù)。
1、笛卡爾集
笛卡爾集是集合中的一種。假設(shè)A和B都是集合，A和B的笛卡爾集用 A B來表示。即A B所形成的集合叫做笛卡爾集。
下表中，部門表員工表即為部門表和員工表的笛卡爾集。（可以看出，部門表員工表中有三條數(shù)據(jù)并不正確。）

a、演示笛卡爾集

創(chuàng)建部門表、和員工表并添加數(shù)據(jù)：

    SQL> create table 部門表(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 部門表(deptno));
    表已創(chuàng)建。
    SQL> insert into 部門表(deptno, dname) values(10,'銷售部');
    已創(chuàng)建 1 行。
    SQL> insert into 部門表(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 行。

部門表和員工表的集合：

SQL> select empno, ename, 員工表.deptno, 部門表.deptno, dname from 部門表， 員工表;
         EMPNO ENAME          DEPTNO     DEPTNO DNAME
                 1           張三                      10         10            銷售部
                 2          李四                       20         10            銷售部
                 3          王五                       10         10            銷售部
                 1          張三                       10         20            人事部
                 2          李四                       20         20            人事部
                 3          王五                      10          20            人事部
已選擇6行。

b、sql語句多表查詢顯示滿足條件的表的集合

SQL> select empno, ename, 員工表.empno, 部門表.deptno, dname from 部門表, 員工表 where 部門表.deptno = 員工表.deptno;
     EMPNO ENAME           EMPNO     DEPTNO DNAME
             1             張三                1                 10             銷售部
             2            李四                2                  20            人事部
             3            王五                3                  10            銷售部

為了避免笛卡爾集，可以在where子句中加入有效的連接條件。
連接條件至少有n-1個，n代表表的個數(shù)。
在表中有相同列時，在列名之前加上表名前綴。
2、等值與非等值連接
等值連接：是指使用等值比較符（=）指定連接條件的查詢。舉例：查詢員工信息，要求顯示：員工號、姓名、職位、部門名稱。

使用表名前綴在多個表中區(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行。

使用表的別名，可以簡化連接查詢并提高查詢性能：

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行。

多個連接條件時使用AND操作符：在連接查詢中，當(dāng)有多個連接條件時，使用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í)行連接查詢。舉例：顯示所有員工的員工號、姓名、工資以及其工資的等級。

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ò)展，它不僅會返回滿足連接條件的所有記錄，而且還會返回不滿足連接條件的部分記錄。
舉例：按照部門統(tǒng)計員工的人數(shù)，要求顯示：部門號，部門名稱，和人數(shù)。

普通的表連接：（發(fā)現(xiàn)沒有顯示DEPTNO為40的記錄，原因是該部門沒有員工，但是本例中需要顯示該記錄。）

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

外連接是使用（+）操作符來完成的。
右（外）連接語法格式：SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column;（返回table2表中的所有記錄，返回table1表中所有滿足條件的記錄）

左（外）連接語法格式：SELECT table1.column, table2.column FROM table1, table2 WHERE table1, table2 WHERE table1.column = table2.column(+);（返回table1表中的所有記錄，返回table2表中所有滿足條件的記錄）

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ì)是：將同一張表看成是多張表。

舉例：查詢所有員工的姓名以及其直屬上級的姓名。

SQL> select e.ename 員工名, m.ename 直屬上級 from emp e, emp m where e.mgr = m.empno;
員工名     直屬上級
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連接

為了簡化連接查詢，使得連接查詢更加直觀、更容易編寫。SQL：1999標(biāo)準(zhǔn)為連接查詢提供新語法，如下所示：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

自然連接是一種特殊的等價連接，它將表中具有相同名稱的列自動進(jìn)行記錄匹配。自然連接不必指定任何同等連接條件。語法格式為：SELECT table1.column_name, table2.column_name FROM table1 NATURAL JOIN table2;

舉例：查詢員工名、工資以及所在部門名稱

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)連接

返回兩個表中相匹配的數(shù)據(jù)（只返回滿足條件的數(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、左（外）連接

左連接用于返回滿足連接條件的數(shù)據(jù)，以及不滿足連接條件的左邊表的其他數(shù)據(jù)。（即：左表的全部記錄，右表滿足條件的記錄）

在SQL：1999標(biāo)準(zhǔn)中，左連接是通過LEFT[OUTER] JOIN選項(xiàng)來實(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、右（外）連接

右連接用于返回滿足連接條件的左邊表的數(shù)據(jù)，以及不滿足連接條件的右邊表的其他數(shù)據(jù)。（即：右表中的全部記錄，左表滿足條件的記錄。）示例：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、完全（外）連接

完全連接用于返回滿足連接條件的數(shù)據(jù)，以及不滿足鏈接條件的左表和右表中的其他數(shù)據(jù)。（即：左表和右表的全部記錄。）

在SQL：1999標(biāo)準(zhǔn)中，完全連接是通過制定FULL [OUTER] JOIN 選項(xiàng)來實(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)算符專門用于合并多條SELECT語句的結(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；

a、UNION
UNION操作符用于取得兩個結(jié)果集的并集，當(dāng)使用該操作符時，會自動去掉結(jié)果集中的重復(fù)行，并且會以第一列的結(jié)果進(jìn)行升序排序。

舉例：合并顯示emp02表和emp02表中所有雇員的部門編號、員工號、員工姓名。

    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 操作符用于取得兩個結(jié)果集的并集，單與union操作符不同，該操作符不會取消重復(fù)行，并且不會對結(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操作符用于取得兩個結(jié)果集的交集，當(dāng)使用該操作符時，只會顯示同時存在于兩個結(jié)果集中的數(shù)據(jù)，并且會以第一列的結(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操作符用于取得兩個結(jié)果集中的差集，當(dāng)使用該操作符時，只會顯示在第一個結(jié)果集中存在，在第二個結(jié)果集中不存在的數(shù)據(jù)，并且會以第一列的結(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時，默認(rèn)情況下會自動基于第一列進(jìn)行升序排序；而當(dāng)使用集合操作符UNION ALL時，不會進(jìn)行排序，為了控制結(jié)果的排序順序，可以使用ORDER BY子句。（如果兩個表查詢的結(jié)果中列名相同，則可以使用列名名稱；如果兩個表中的列名不同，則必須使用列位置，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ù)類型上要相對應(yīng)。

向AI問一下細(xì)節(jié)

推薦閱讀：

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

上一篇新聞：
debian8 安裝percona-xtrabackup 很簡單
下一篇新聞：
【巨杉數(shù)據(jù)庫Sequoiadb】【咨詢】【SequoiaFS】

猜你喜歡

AI
助
手

產(chǎn)品服務(wù)

地區(qū)劃分

專題活動

幫助支持

關(guān)于我們

售后咨詢

7*24小時在線電話：400-100-2938

7*24小時在線 QQ：800811969

關(guān)注億速云

億速云公眾號

手機(jī)網(wǎng)站二維碼

<blockquote id="inu66"><optgroup id="inu66"></optgroup></blockquote>