4.3 檢索數據

視頻講解：光盤\TM\lx\4\檢索數據．mp4

用戶對表或視圖最常進行的操作就是檢索數據，檢索數據可以通過SELECT語句來實現，該語句由多個子句組成，通過這些子句可以完成篩選、投影和連接等各種數據操作，最終得到用戶想要的查詢結果。該語句的基本語法格式如下：

        select {[ distinct | all ] columns | *}
        [into table_name]
        from {tables | views | other select}
        [where conditions]
        [group by columns]
        [having conditions]
        [order by columns]

在上面的語法中，共有7個子句，它們的功能分別如下。

select子句：用于選擇數據表、視圖中的列。

into子句：用于將原表的結構和數據插入新表中。

from子句：用于指定數據來源，包括表、視圖和其他select語句。

where子句：用于對檢索的數據進行篩選。

group by子句：用于對檢索結果進行分組顯示。

having子句：用于從使用group by子句分組后的查詢結果中篩選數據行。

order by子句：用來對結果集進行排序（包括升序和降序）。

接下來將對上面的各種子句和查詢方式進行詳細介紹。

4.3.1 簡單查詢

只包含SELECT子句和FROM子句的查詢就是簡單查詢，SELECT子句和FROM子句是SELECT語句的必選項，即每個SELECT語句都必須包含這兩個子句。其中，SELECT子句用于選擇想要在查詢結果中顯示的列，對于這些要顯示的列，即可以使用列名來表示，也可以使用星號（*）來表示。在檢索數據時，數據將按照SELECT子句后面指定的列名的順序來顯示；如果使用星號（*），則表示檢索所有的列，這時數據將按照表結構的自然順序來顯示。

1．檢索所有的列

如果要檢索指定數據表的所有列，可以在SELECT子句后面使用星號（*）來實現。

在檢索一個數據表時，要注意該表所屬的模式。如果在指定表所屬的模式內部檢索數據，則可以直接使用表名；如果不在指定表所屬的模式內部檢索數據，則不但要查看當前模式是否具有查詢的權限，而且還要在表名前面加上其所屬的模式名稱。

【例4.7】 在SCOTT模式下，在SELECT語句中使用星號（*）來檢索dept表中所有的數據，代碼如下。

        SQL> connect scott/1qaz2wsx
        已連接。
        SQL> select * from dept;

本例運行結果如圖4.3所示。

圖4.3 檢索dept表中所有的數據

在上面的例子中，from子句的后面只有一個數據表，實際上可以在from子句的后面指定多個數據表，每個數據表名之間使用逗號（,）分隔開，其語法格式如下：

        FROM table_name1, table_name2, table_name3…table_namen

【例4.8】 在SCOTT模式下，在from子句中指定兩個數據表dept和salgrade，代碼如下。

        SQL> select * from dept, salgrade;

2．檢索指定的列

用戶可以指定查詢表中的某些列而不是全部列，并且被指定列的順序不受限制，指定部分列也稱作投影操作。這些列名緊跟在SELECT關鍵字的后面，每個列名之間用逗號隔開。其語法格式如下：

        SELECT column_name1, column_name2, column_name3, column_namen

【例4.9】在SCOTT模式下，檢索emp表中指定的列job、ename、empno，代碼如下。

        SQL> select job, ename, empno from emp;

本例運行結果如圖4.4所示。

圖4.4 檢索emp表中指定的列

在Oracle數據庫中，有一個標識行中唯一特性的行標識符，該行標識符的名稱為ROWID。行標識符ROWID是Oracle數據庫內部使用的隱藏列，由于該列實際上并不是定義在表中，所以也稱為偽列。偽列ROWID長度為18位字符，包含該行數據在Oracle數據庫中的物理地址。用戶使用DESCRIBE命令是無法查到ROWID列的，但是可以在SELECT語句中檢索到該列。

【例4.10】 在SCOTT模式下，檢索emp表中指定的列job和ename。另外，還包括ROWID偽列，代碼如下。

        SQL> select rowid, job, ename from emp;

本例運行結果如圖4.5所示。

圖4.5 顯示emp表的ROWID偽列

3．查詢日期列

日期列是指數據類型為DATE的列。查詢日期列與查詢其他列沒有任何區別，但日期列的默認顯示格式為DD-MON-RR。

下面通過實例來說明查詢日期列和以特定語言或者格式顯示日期列數據的方法。

（1）以簡體中文顯示日期結果

【例4.11】 在SCOTT模式下，檢索emp表中員工的入職時間（hiredate），并且以簡體中文顯示日期結果。如果以簡體中文顯示日期結果，可以將會話的NLS_DATE_LANGUAGE參數設置為SIMPLIFIED CHINESE。代碼如下。

        SQL> alter session set nls_date_language = 'SIMPLIFIED CHINESE';
        SQL> select ename, hiredate from emp;

本例運行結果如圖4.6所示。

圖4.6 以簡體中文顯示日期結果

（2）以美國英語顯示日期結果

【例4.12】 在SCOTT模式下，檢索emp表中員工的入職時間（hiredate），并且以美國英語顯示日期結果。如果以美國英語顯示日期結果，可以將會話的NLS_DATE_LANGUAGE參數設置為AMERICAN。代碼如下。

        SQL> alter session set nls_date_language = 'AMERICAN';
        SQL> select ename, hiredate from emp;

本例運行結果如圖4.7所示。

圖4.7 以美國英語顯示日期結果

（3）以特定格式顯示日期結果

不同國家地區、不同民族、不同人員都具有不同的日期使用習慣，如果希望定制日期顯示格式，并按照特定方式顯示日期格式，那么可以設置會話的NLS_DATE_FORMAT參數。

【例4.13】 在SCOTT模式下，檢索emp表中員工的入職時間（hiredate），并且以“××××年××月××日”格式顯示日期結果。代碼如下。

        SQL> alter session set nls_date_format = 'YYYY"年"MM"月"DD"日"';
        SQL> select ename, hiredate from emp;

本例運行結果如圖4.8所示。

圖4.8 以特定格式顯示日期結果

（4）使用TO_CHAR函數定制日期顯示函數

除了可以使用參數NLS_DATE_FORMAT設置日期顯示格式外，也可以使用TO_CHAR函數將日期值轉變為特定格式的字符串。

TO_CHAR函數將在本章4.4.4節中進行詳細講解。

4．帶有表達式的SELECT子句

在使用SELECT語句時，對于數字數據和日期數據都可以使用算術表達式。在SELECT語句中可以使用算術運算符，包括（+）、減（-）、乘（*）、除（/）和括號。另外，在SELECT語句中不僅可以執行單獨的數學運算，還可以執行單獨的日期運算以及與列名關聯的運算。

【例4.14】 檢索emp表的sal列，把其值調整為原來的1.1倍，代碼如下。

        SQL> select sal*(1+0.1), sal from emp;

本例運行結果如圖4.9所示。

圖4.9 顯示工資調整后的值

5．為列指定別名

由于許多數據表的列名都是一些英文的縮寫，用戶為了方便查看檢索結果，常常需要為這些列指定別名。在Oracle系統中，為列指定別名既可以使用AS關鍵字，也可以不使用任何關鍵字而直接指定。

【例4.15】 在SCOTT模式下，檢索emp表的指定列empno、ename、job，并使用AS關鍵字為這些列指定中文的別名，代碼如下（實例位置：光盤\TM\sl\4\5）。

        SQL> connect scott/1qaz2wsx
        已連接。
        SQL>select empno as"員工編號", ename as"員工名稱", job as"職務"  from emp;

本例運行結果如圖4.10所示。

圖4.10 指定中文的別名1

在為列指定別名時，關鍵字AS是可選項，用戶也可以在列名后面直接指定列的別名。

【例4.16】 在SCOTT模式下，檢索emp表的指定列empno、ename、job，不使用任何關鍵字而直接為這些列指定中文的別名，代碼如下。

        SQL>select empno"員工編號", ename"員工名稱", job"職務"  from emp;

本例運行結果如圖4.11所示。

圖4.11 指定中文的別名2

6．顯示不重復記錄

默認情況下，結果集中包含所有符合查詢條件的數據行，這樣結果集中就有可能出現重復數據。而在實際的應用中，這些重復的數據除了占據較大的顯示空間外，可能不會給用戶帶來太多有價值的東西，這樣就需要去除重復記錄，保留唯一的記錄即可。在SELECT語句中，可以使用DISTINCT關鍵字來限制在查詢結果顯示不重復的數據，該關鍵字用在SELECT子句的列表前面。

【例4.17】 在SCOTT模式下：

（1）顯示emp表中的job（職務）列，代碼如下。

        SQL> select job from emp;

本例運行結果如圖4.12所示。

圖4.12 顯示不重復記錄1

（2）顯示emp表中的job（職務）列，要求顯示的“職務”記錄不重復，代碼如下。

        SQL> select distinct job from emp;

本例運行結果如圖4.13所示。

圖4.13 顯示不重復記錄2

7．處理NULL值

NULL表示未知值，它既不是空格，也不是0。當插入數據時，如果沒有為特定列提供數據，并且該列沒有默認值，那么其結果為NULL。

但是在實際應用程序中，NULL顯示結果往往不能符合應用需求，在這種情況下需要使用函數NVL處理NULL，并將其轉換為合理的顯示結果。下面通過實例來說明不處理NULL的顯示結果，以及處理NULL的具體方法。

（1）不處理NULL。當算術表達式包含NULL時，如果不處理NULL，那么顯示結果為空。

【例4.18】 顯示emp表中的雇員名、工資、獎金以及實發工資（SAL+COMM），代碼如下。

        SQL> select ename, sal, comm, sal+comm from emp;

本例運行結果如圖4.14所示。

圖4.14 不處理NULL值的顯示結果

通過顯示結果可以看出，COMM值為NULL的列，計算SAL+COMM的值也為NULL。

（2）使用NVL函數處理NULL。如果雇員的實發工資顯示為空，那么顯然是不符合實際情況的。為了避免出現這種情況，就應該處理NULL。

【例4.19】 顯示emp表中的雇員名、工資、獎金以及實發工資（SAL+COMM）并處理NULL值，代碼如下。

        SQL> select ename, sal, comm, sal+nvl(comm,0) from emp;

本例運行結果如圖4.15所示。

圖4.15 使用NVL函數處理NULL值的顯示結果

當使用函數NVL（COMM,0）時，如果COMM存在數值，則函數返回其原有數值；如果COMM列為NULL，則函數返回0。

8．連接字符串

當執行查詢操作時，為了顯示更有意義的結果值，有時需要將多個字符串連接起來。連接字符串可以使用“||”操作符或者CONCAT函數。

（1）使用“||”操作符連接字符串

【例4.20】 顯示emp表中所有雇員的雇員名及其崗位信息，使用“||”操作符連接雇員名和崗位信息，代碼如下。

        SQL> select ename, || ''''||'s job is '||job from emp;

本例運行結果如圖4.16所示。

圖4.16 使用“||”操作符連接字符串

（2）使用函數CONCAT連接字符串

【例4.21】 顯示emp表中所有雇員的雇員名及其工資信息，使用CONCAT函數連接雇員名和工資信息，代碼如下。

        SQL> select concat(concat(ename, '''s salary is '), sal) from emp;

本例運行結果如圖4.17所示。

圖4.17 使用函數CONCAT連接字符串

具體CONCAT函數用法將在本章中4.4.1節做詳細介紹。

4.3.2 篩選查詢

在SELECT語句中使用WHERE子句可以實現對數據行的篩選操作，只有滿足WHERE子句中判斷條件的行才會顯示在結果集中，而那些不滿足WHERE子句判斷條件的行則不包括在結果集中。這種篩選操作是非常有意義的，通過篩選數據，可以從大量的數據中得到用戶所需要的數據。在SELECT語句中，WHERE子句位于FROM子句之后，其語法格式如下：

        SELECT columns_list
        FROM table_name
        WHERE conditional_expression

columns_list：字段列表。

table_name：表名。

conditional_expression：篩選條件表達式。

接下來對幾種常用的篩選情況進行詳細講解。

1．比較篩選

可以在WHERE子句中使用比較運算符來篩選數據，這樣只有滿足篩選條件的數據行才會被檢索出來，不滿足比較條件的數據行則不會被檢索出來。基本的“比較篩選”操作主要有以下6種情況。

A=B：比較A與B是否相等。

A! B或A< >B：比較A與B是否不相等。

A>B：比較A是否大于B。

A<B：比較A是否小于B。

A>=B：比較A是否大于或等于B。

A<=B：比較A是否小于或等于B。

【例4.22】 在SCOTT模式下，查詢emp表中工資（sal）大于1500的數據記錄，代碼如下。

        SQL> select empno, ename, sal from emp where sal > 1500;

本例運行結果如圖4.18所示。

圖4.18 查詢工資大于1500的記錄

另外，除了基本的“比較篩選”操作外，還有以下兩個特殊的“比較篩選”操作。

A{operator}ANY（B）：表示A與B中的任何一個元素進行operator運算符的比較，只要有一個比較值為true，就返回數據行。

A={operator}ALL（B）：表示A與B中的所有元素進行operator運算符的比較，只有與所有元素比較值都為true，才返回數據行。

【例4.23】 在SCOTT模式下，使用all關鍵字過濾工資（sal）同時不等于3000、950和800的員工記錄，代碼如下。

        SQL> select empno, ename, sal from emp where sal <> all(3000,950,800);

本例運行結果如圖4.19所示。

圖4.19 過濾指定記錄

2．使用特殊關鍵字篩選

SQL語言提供了LIKE、IN、BETWEEN和IS NULL等關鍵字來篩選數據，這些關鍵字的功能分別是匹配字符串、查詢目標值、限定值的范圍和判斷值是否為空等，下面將對這些關鍵字進行詳細講解。

（1）LIKE關鍵字

在WHERE子句中使用LIKE關鍵字查詢數據的方式也稱為字符串模式匹配或字符串模糊查詢，LIKE關鍵字需要使用通配符在字符串內查找指定的模式，所以需要了解常用的通配符。

通配符的英文原文為wildcard，該詞的原意為撲克牌中的2或王，因為它們可以代替任何其他的牌，所以稱為wildcard。

LIKE關鍵字可以使用以下兩個通配符。

%：代表0個或多個字符。

_：代表一個且只能是一個字符。

例如，“K%”表示以字母K開頭的任意長度的字符串，“%M%”表示包含字母M的任意長度的字符串，“_MRKJ”表示5個字符長度且后面4個字符是MRKJ的字符串。

【例4.24】 在emp表中，使用LIKE關鍵字匹配以字母S開頭的任意長度的員工名稱，代碼如下。

        SQL> select empno, ename, job from emp where ename like 'S%';

本例運行結果如圖4.20所示。

圖4.20 使用LIKE關鍵字

【例4.25】 在emp表中，查詢工作是SALESMAN的員工姓名，但是不記得SALESMAN的準確拼寫，但還記得它的第1個字符是S，第3個字符是L，第5個字符為S，代碼如下。

        SQL> select empno, ename, job from emp where job like 'S_L_S%';

本例運行結果如圖4.21所示。

圖4.21 查找工作是SALESMAN的員工姓名

從例4.24和例4.25的查詢語句中可以看出，通過在LIKE表達式中使用不同的通配符“%”和“_”的組合，可以構造出相當復雜的限制條件。

另外，LIKE關鍵字還可以幫助簡化某些WHERE子句。

【例4.26】 在emp表中，要顯示在1981年雇用的所有員工的信息，代碼如下。

        SQL> select empno, ename, sal, hiredate
          2  from emp
          3  where hiredate like'%81';

本例運行結果如圖4.22所示。

圖4.22 顯示在1981年雇用的所有員工的信息

但是如果要查詢的字符串中含有“%”或“_”，又該如何處理呢？

要查詢的字符串中含有“%”或“_”時，可以使用轉義（escape）關鍵字實現查詢。

為了進行練習，必須先創建一個臨時表，之后再往該表中插入1行記錄，該記錄中包含通配符。

【例4.27】 ① 創建一個和dept表相同結構和數據的表dept_temp，代碼如下。

        SQL> create table dept_temp
          2  as
          3  select*from dept;

本例運行結果如圖4.23所示。

圖4.23 創建一個臨時表dept_temp

② 插入一條記錄，代碼如下。

        SQL> insert into dept_temp
          2  values(60, 'IT_RESEARCH', 'BEIJING');

本例運行結果如圖4.24所示。

圖4.24 向臨時表dept_temp中插入一條含有通配符的數據

③ 顯示臨時表dept_temp中部門名稱以IT_開頭的所有數據行，代碼如下。

        SQL> select *
          2  from dept_temp
          3  where dname like'IT\_%'escape'\';

本例運行結果如圖4.25所示。

圖4.25 顯示臨時表dept_temp中部門名稱以IT_開頭的所有數據行

在上述查詢語句中使用了“\”，“\”為轉義字符，即在“\”之后的“_”字符已不是通配符，而是它本來的含義，即下劃線。因此該查詢的結果為：前兩個字符為“IT”，第3個字符為“_”，后跟任意字符的字符串。

沒有必要一定使用“\”字符作為轉義符，完全可以使用任何字符來作為轉義符。當然，許多Oracle的專業人員之所以經常使用“\”字符作為轉義符，是因為該字符在UNIX操作系統和C語言中就是轉義符。

為了驗證以上的論述，輸入如下的語句。

        SQL> select *
          2  from dept_temp
          3  where dname like'ITa_%'escape'a';

本例運行結果如圖4.26所示。

圖4.26 驗證轉義符可以是任何字符

在上面的查詢中，將’a’定義為轉義符，但是顯示結果和圖4.25中顯示的結果完全相同。

（2）IN關鍵字

當測試一個數據值是否匹配一組目標值中的一個時，通常使用IN關鍵字來指定列表搜索條件。IN關鍵字的格式是IN（目標值1，目標值2，目標值3, …），目標值的項目之間必須使用逗號分隔，并且括在括號中。

【例4.28】 在emp表中，使用IN關鍵字查詢職務為PRESIDENT、MANAGER和ANALYST中任意一種的員工信息，代碼如下。

        SQL> select empno, ename, job from emp where job in('PRESIDENT', 'MANAGER', 'ANALYST');

本例運行結果如圖4.27所示。

圖4.27 使用IN關鍵字

另外，NOT IN表示查詢指定的值不在某一組目標值中，這種方式在實際應用中也很常見。

【例4.29】 在emp表中，使用NOT IN關鍵字查詢職務不在指定目標列表（PRESIDENT、MANAGER、ANALYST）范圍內的員工信息，代碼如下。

        SQL> select empno, ename, job from emp where job not in('PRESIDENT', 'MANAGER', 'ANALYST');

本例運行結果如圖4.28所示。

圖4.28 使用NOT IN關鍵字

（3）BETWEEN關鍵字

需要返回某一個數據值是否位于兩個給定的值之間，可以使用范圍條件進行檢索。通常使用BETWEEN…AND和NOT…BETWEEN…AND來指定范圍條件。

使用BETWEEN…AND查詢條件時，指定的第一個值必須小于第二個值。因為BETWEEN…AND實質是查詢條件“大于等于第一個值，并且小于等于第二個值”的簡寫形式，即BETWEEN…AND要包括兩端的值，等價于比較運算符（>=…<=）。

【例4.30】 在emp表中，使用BETWEEN…AND關鍵字查詢工資（sal）在2000到3000之間的員工信息，代碼如下。

        SQL> select empno, ename, sal from emp where sal between 2000 and 3000;

本例運行結果如圖4.29所示。

圖4.29 使用BETWEEN…AND關鍵字

而NOT…BETWEEN…AND語句返回在兩個指定值的范圍以外的某個數據值，但并不包括兩個指定的值。

【例4.31】 在emp表中，使用NOT…BETWEEN…AND關鍵字查詢工資（sal）不在1000到3000之間的員工信息，代碼如下。

        SQL> select empno, ename, sal from emp where sal not between 1000 and 3000;

本例運行結果如圖4.30所示。

圖4.30 使用NOT…BETWEEN…AND關鍵字

（4）IS NULL關鍵字

空值（NULL）從技術上來說就是未知的、不確定的值，但空值與空字符串不同，因為空值是不存在的值，而空字符串是長度為0的字符串。

因為空值代表的是未知的值，所以并不是所有的空值都相等。例如，student表中有兩個學生的年齡未知，但無法證明這兩個學生的年齡相等。這樣就不能用“=”運算符來檢測空值。所以SQL引入了一個IS NULL關鍵字來檢測特殊值之間的等價性，并且IS NULL關鍵字通常在WHERE子句中使用。

【例4.32】 在HR模式下，使用IS NULL關鍵字過濾出locations表中省份或州（state_province）的名稱為空值的街道地址信息，代碼及運行結果如下（實例位置：光盤\TM\sl\4\6）。

        SQL> connect hr/hr;
        已連接。
        SQL> select street_address from locations where state_province is null;


        STREET_ADDRESS
        ---------------------------------------
        1297 Via Cola di Rie
        93091 Calle della Testa
        9450 Kamiya-cho
        40-5-12 Laogianggen
        198 Clementi North
        8204 Arthur St


        已選擇6行。

注意

當與NULL進行比較時，不要使用等于（=）、不等于（< >）操作符。盡管使用它們不會有任何語法錯誤，但條件總是FALSE。下面以顯示公司總裁為例，說明NULL語句前面使用IS和等于或不等于操作符的區別。

使用IS NULL關鍵字時，代碼如下。

        SQL>select ename, mgr from emp where mgr is null;

本例運行結果如圖4.31所示。

圖4.31 使用IS NULL關鍵字

在NULL前面使用等號（=），代碼如下。

        SQL>select ename, mgr from emp where mgr=null;

本例運行結果如圖4.32所示。

圖4.32 在NULL前面使用等號（=）

3．邏輯篩選

邏輯篩選是指在WHERE子句中使用邏輯運算符AND、OR和NOT進行數據篩選操作，那么這些邏輯運算符可以把多個篩選條件組合起來，這樣便于用戶獲取更加準確的數據記錄。

AND邏輯運算符表示兩個邏輯表達式之間“邏輯與”的關系，用戶完全可以使用AND運算符加比較運算符來代替BETWEEN…AND關鍵字。

【例4.33】 在emp表中，使用AND運算符查詢工資（sal）在2000到3000之間的員工信息，代碼如下。

        SQL> select empno, ename, sal from emp where sal >= 2000 and sal <= 3000;

本例運行結果如圖4.33所示。

圖4.33 使用AND運算符

OR邏輯運算符表示兩個邏輯表達式之間“邏輯或”的關系，兩個表達式的結果中有一個為true，則這個邏輯或表達式的值就為true。

【例4.34】 在emp表中，使用OR邏輯運算符查詢工資小于2000或工資大于3000的員工信息，代碼如下（實例位置：光盤\TM\sl\4\7）。

        SQL> connect scott/1qaz2wsx
        已連接。
        SQL> select empno, ename, sal from emp where sal < 2000 or sal > 3000;

本例運行結果如圖4.34所示。

圖4.34 使用OR運算符

NOT邏輯運算符對表達式執行邏輯非的運算，在前面的示例中已經多次用到過，這里不再給出示例。

4.3.3 分組查詢

數據分組的目的是用來匯總數據或為整個分組顯示單行的匯總信息，通常在查詢結果集中使用GROUP BY子句對記錄進行分組。在SELECT語句中，GROUP BY子句位于FROM子句之后，其語法格式如下：

        SELECT columns_list
        FROM table_name
        [WHERE conditional_expression]
        GROUP BY columns_list

columns_list：字段列表，在GROUP BY子句中也可以指定多個列分組。

table_name：表名。

conditional_expression：篩選條件表達式。

GROUP BY子句可以基于指定某一列的值將數據集合劃分為多個分組，同一組內所有記錄在分組屬性上具有相同值，也可以基于指定多列的值將數據集合劃分為多個分組。

1．使用GROUP BY子句進行單列分組

單列分組是指基于列生成分組統計結果。當進行單列分組時，會基于分組列的每個不同值生成一個統計結果。

【例4.35】 在emp表中，按照部門編號（deptno）和職務（job）列進行分組，具體代碼如下。

        SQL> select deptno, job from emp group by deptno, job order by deptno;

本例運行結果如圖4.35所示。

圖4.35 分組顯示

GROUP BY子句經常與聚集函數一起使用。使用GROUP BY子句和聚集函數，可以實現對查詢結果中每一組數據進行分類統計。所以，在結果中每組數據都有一個與之對應的統計值。在Oracle系統中，經常使用的統計函數如表4.2所示。

表4.2 常用的統計函數

【例4.36】 在emp表中，使用GROUP BY子句對工資記錄進行分組，并計算平均工資（AVG）、所有工資的總和（SUM）以及最高工資（MAX）和各組的行數（COUNT），具體代碼如下。

        SQL> select job, avg(sal), sum(sal), max(sal), count(job)
          2  from emp
          3  group by job;

本例運行結果如圖4.36所示。

圖4.36 分組統計

在使用GROUP BY子句時，要注意：

在SELECT子句的后面只可以有兩類表達式：統計函數和進行分組的列名。

在SELECT子句中的列名必須是進行分組的列，除此之外添加其他的列名都是錯誤的，但是GROUP BY子句后面的列名可以不出現在SELECT子句中。

在默認情況下，將按照GROUP BY子句指定的分組列升序排列，如果需要重新排序，可以使用ORDER BY子句指定新的排列順序。

GROUP BY子句中的列可以不在SELECT列表中。這里以下面的示例進行說明。

【例4.37】 查詢emp表，顯示按職位（job）分類（job并沒有包含在SELECT子句中）的每類員工的平均工資，并且顯示的結果是按職位由小到大排列的，具體代碼如下。

        SQL> select avg(sal)
          2  from emp
          3  group by job;

本例運行結果如圖4.37所示。

圖4.37 演示GROUP BY子句中的列可以不在SELECT列表中

從上面的運行結果中很難看出這一結果是按什么排序的。為了提高程序的可讀性，應盡可能不使用這樣的查詢方法。

下面的實例是錯誤的，Oracle會返回錯誤信息，代碼如下。

        SQL> select job, avg(sal)
          2  from emp;

運行結果如圖4.38所示。

圖4.38 分組函數與GROUP BY子句的非法操作

為什么在Oracle中會有這樣的規定呢？在SELECT子句中的列名稱job告訴Oracle系統顯示每行數據的職位（job），在emp表中有多條數據。而在SELECT子句中的AVG（sal）告訴Oracle系統顯示emp表中所有數據行的平均工資，在這個查詢語句中只能產生一個平均工資。查詢語句的這兩個要求顯然是矛盾的，因此，Oracle系統會報錯。

為了改正這一錯誤，可以在查詢語句中增加GROUP BY子句，并把列job放在該子句中，修改后的查詢語句代碼如下。

        SQL> select job, avg(sal)
          2  from emp
          3  group by job;

運行結果如圖4.39所示。

圖4.39 增加GROUP BY子句使語句正確

顯示的結果給出了emp表中每種職位（job）的平均工資（AVG（sal））。

2．使用GROUP BY子句進行多列分組

多列分組是指基于兩個或兩個以上的列生成分組統計結果。當進行多列分組時，會基于多個列的不同值生成統計結果。

【例4.38】 使用GROUP BY進行多列分組。查詢emp表，顯示每個部門每種崗位的平均工資和最高工資，具體代碼如下。

        SQL> select deptno, job, avg(sal), max(sal)
          2  from emp
          3  group by deptno, job;

本例運行結果如圖4.40所示。

圖4.40 使用GROUP BY子句進行多列分組

3．使用ORDER BY子句改變分組排序結果

當使用GROUP BY子句執行分組統計時，會自動基于分組列進行升序排列。為了改變分組數據的排序結果，需要使用ORDER BY子句。

【例4.39】 查詢emp表，顯示每個部門的部門號及工資總額，而且工資總額降序排列，具體代碼如下。

        SQL> select deptno, sum(sal)
          2  from emp
          3  group by deptno
          4  order by sum(sal)desc;

本例運行結果如圖4.41所示。

圖4.41 使用ORDER BY子句改變分組排序結果

4．使用HAVING子句限制分組結果

HAVING子句通常與GROUP BY子句一起使用，在完成對分組結果統計后，可以使用HAVING子句對分組的結果做進一步的篩選。如果不使用GROUP BY子句，HAVING子句的功能與WHERE子句一樣。HAVING子句和WHERE子句的相似之處都是定義搜索條件。唯一不同的是HAVING子句中可以包含聚合函數，如常用的（count）、（avg）、（sum）等；在WHERE子句中則不可以使用聚合函數。

如果在SELECT語句中使用了GROUP BY子句，那么HAVING子句將應用于GROUP BY子句創建的那些組。如果指定了WHERE子句，而沒有指定GROUP BY子句，那么HAVING子句將應用于WHERE子句的輸出，并且整個輸出被看作一個組，如果在SELECT語句中既沒有指定WHERE子句，也沒有指定GROUP BY子句，那么HAVING子句將應用于FROM子句的輸出，并且將其看作一個組。

【例4.40】 在emp表中，首先通過分組的方式計算出每個部門的平均工資，然后再通過HAVING子句過濾出平均工資大于2000的記錄信息，具體代碼如下。

        SQL>select deptno as部門編號，avg(sal)as平均工資
          2  from emp
          3  group by deptno
          4  having avg(sal)>2000;

本例運行結果如圖4.42所示。

圖4.42 平均工資大于2000的記錄信息

從上面的查詢結果中可以看出，SELECT語句使用GROUP BY子句對emp表進行分組統計，然后再由HAVING子句根據統計值作進一步篩選。

上面的示例無法使用WHERE子句直接過濾出平均工資大于2000的部門信息，因為在WHERE子句中不可以使用聚合函數（這里是AVG）。

通常情況下，HAVING子句與GROUP BY子句一起使用，這樣可以匯總相關數據后再進一步篩選匯總的數據。

5．在GROUP BY子句中使用ROLLUP和CUBE操作符

默認情況下，當使用GROUP BY子句生成數據統計結果時，只會生成相關列的數據統計信息，而不會生成小計和總計統計。例如，當使用GROUP BY子句統計不同部門、不同崗位的平均工資時，會生成如表4.3所示的統計結果。

表4.3 使用GROUP BY子句統計不同部門、不同崗位的平均工資

在實際應用程序中，不僅需要獲得以上統計結果，而且可能還需要取得橫向、縱向小計統計以及總計統計，例如部門的平均工資、崗位的平均工資、所有雇員的平均工資等。為了取得更全面的數據統計，可以使用ROLLUP和CUBE操作符。當使用ROLLUP操作符時，在保留原有統計結果的基礎上，還會生成橫向小計（部門平均工資）和總計（所有雇員平均工資），如表4.4所示。

表4.4 使用ROLLUP操作符生成橫向小計和總計

當使用CUBE操作符時，在保留原有統計結果的基礎上，還會生成橫向小計（部門平均工資）、縱向小計崗位平均工資和總計（所有雇員平均工資），如表4.5所示。

表4.5 使用CUBE操作符生成橫向小計、縱向小計和總計

（1）使用ROLLUP操作符執行數據統計

當直接使用GROUP BY子句進行多列分組時，只能生成簡單的數據統計結果。為了生成數據統計、橫向小計和總計統計，可以在GROUP BY子句中使用ROLLUP操作符。

【例4.41】 在emp表中，使用ROLLUP操作符，顯示各部門每崗位的平均工資、每部門的平均工資、所有雇員的平均工資，具體代碼如下。

        SQL>select deptno as部門編號，job as崗位，avg(sal)as平均工資
          2  from emp
          3  group by rollup(deptno, job);

本例運行結果如圖4.43所示。

圖4.43 使用ROLLUP操作符

（2）使用CUBE操作符執行數據統計

為了生成數據統計、橫向小計、縱向小計以及總計統計，可以使用CUBE操作符。

【例4.42】 在emp表中，使用CUBE操作符，顯示各部門各崗位的平均工資、部門平均工資、崗位平均工資、所有雇員平均工資，具體代碼如下。

        SQL>select deptno as部門編號，job as崗位，avg(sal)as平均工資
          2  from emp
          3  group by cube(deptno, job);

本例運行結果如圖4.44所示。

圖4.44 使用CUBE操作符

（3）使用GROUPING函數

當使用ROLLUP或者CUBE操作符生成統計結果時，某個統計結果行可能用到一列或者多列，也可能沒有使用任何列。為了確定統計結果是否使用了特定列，可以使用GROUPING函數。如果該函數返回0，則表示統計結果使用了該列；如果函數返回1，則表示統計結果沒有使用該列。

【例4.43】 在emp表中，使用GROUPING函數確定統計結果所使用的列，具體代碼如下。

        SQL> select deptno, job, sum (sal), grouping(deptno), grouping(job)
          2  from emp
          3  group by rollup(deptno, job);

本例運行結果如圖4.45所示。

圖4.45 使用GROUPING函數

（4）在ROLLUP操作符中使用復合列

復合列被看作一個邏輯單元的列組合，當引用復合列時，需要用括號括住相關列。通過在ROLLUP操作符中使用復合列，可以略過ROLLUP操作符的某些統計結果。例如，子句GROUP BY ROLLUP（a, b, c）的統計結果等同于GROUP BY（a, b, c）、GROUP BY（a, b）、GROUP BY a以及GROUP BY（）的并集；而如果將（b, c）作為復合列，那么子句GROUP BY ROLLUP（a,（b, c））的結果等同于GROUP BY（a, b, c）、GROUP BY a以及GROUP BY（）的并集。

【例4.44】 在ROLLUP操作符中使用復合列，在emp表中顯示特定部門特定崗位的工資總額以及所有雇員的工資總額，具體代碼如下。

        SQL> select deptno, job, sum (sal)
          2  from emp
          3  group by rollup((deptno, job));

本例運行結果如圖4.46所示。

圖4.46 在ROLLUP操作符中使用復合列

（5）在CUBE操作符中使用復合列

通過在CUBE操作符中使用復合列，可以略過CUBE操作符的某些統計結果。例如，子句GROUP BY CUBE（a, b, c）的統計結果等同于GROUP BY（a, b, c）、GROUP BY（a, b）、GROUP BY（a, c）、GROUP BY（b, c）、GROUP BY a、GROUP BY b、GROUP BY c以及GROUP BY（）的并集；而如果將（a, b）作為復合列，那么子句GROUP BY CUBE（（a, b）, c）的結果等同于GROUP BY（a, b, c）、GROUP BY（a, b）、GROUP BY c以及GROUP BY（）的并集。

【例4.45】 在CUBE操作符中使用復合列，在emp表中顯示特定部門特定崗位的工資總額以及所有雇員的工資總額，具體代碼如下。

        SQL> select deptno, job, sum (sal)
          2  from emp
          3  group by cube((deptno, job));

本例運行結果如圖4.47所示。

圖4.47 在CUBE操作符中使用復合列

互動練習：使用帶ROLLUP操作符的GROUP BY子句。

6．使用GROUPING SETS操作符

GROUPING SETS操作符是GROUP BY子句的進一步擴展。在Oracle Database 9i之前，使用GROUP BY子句一次只能顯示單種分組結果，如果要生成多種分組統計結果，那么需要編寫多條SELECT分組語句。從Oracle Database 9i開始，通過使用GROUPING SETS操作符，可以合并多個分組的統計結果，從而簡化了多個分組操作。下面用實例來說明GROUPING SETS操作符的作用及使用方法。

【例4.46】 在emp表中，執行以下操作。

（1）顯示每個部門的平均工資，需要使用部門號（deptno）執行分組統計操作，具體代碼如下。

        SQL> select deptno, avg (sal)
          2  from emp
          3  group by deptno;

運行結果如圖4.48所示。

圖4.48 顯示每個部門的平均工資

（2）顯示每個崗位的平均工資，需要使用崗位（job）執行分組統計，具體代碼如下。

        SQL> select job, avg (sal)
          2  from emp
          3  group by job;

運行結果如圖4.49所示。

圖4.49 顯示每個崗位的平均工資

（3）顯示部門平均工資和崗位平均工資，具體代碼如下。

        SQL> select deptno, job, avg (sal)
          2  from emp
          3  group by grouping sets(deptno, job);

為了顯示多個分組的統計結果，可以使用GROUPING SETS操作符合并分組統計結果。例如，如果既要顯示部門平均工資，也要顯示崗位的平均工資，那么可以使用GROUPING SETS操作符合并分組結果。運行結果如圖4.50所示。

圖4.50 顯示部門平均工資和崗位平均工資

4.3.4 排序查詢

在檢索數據時，如果把數據從數據庫中直接讀取出來，這時查詢結果將按照默認順序排列，但往往這種默認排列順序并不是用戶所需要的。尤其返回數據量較大時，用戶查看自己想要的信息非常不方便，因此需要對檢索的結果集進行排序。在SELECT語句中，可以使用ORDER BY子句對檢索的結果集進行排序，該子句位于FROM子句之后，其語法格式如下：

        SELECT columns_list
        FROM table_name
        [WHERE conditional_expression]
        [GROUP BY columns_list]
        ORDER BY{order_by_expression[ASC|DESC]}  [, ...n]

columns_list：字段列表，在GROUP BY子句中也可以指定多個列分組。

table_name：表名。

conditional_expression：篩選條件表達式。

order_by_expression：表示要排序的列名或表達式。關鍵字ASC表示按升序排列，這也是默認的排序方式；關鍵字DESC表示按降序排列。

ORDER BY子句可以根據查詢結果中的一個列或多個列對查詢結果進行排序，并且第一個排序項是主要的排序依據，其他的是次要的排序依據。

1．單列排序

【例4.47】 在SCOTT模式下，檢索emp表中的所有數據，并按照部門編號（deptno）、員工編號（empno）排序，具體代碼如下。

        SQL> select deptno, empno, ename from emp order by deptno, empno;

本例運行結果如圖4.51所示。

圖4.51 使用ORDER BY子句進行排序

還可以在ORDER BY子句中使用列號。當執行排序操作時，不僅可以按照列名、列別名進行排序，也可以按照列或表達式在選擇列表中的位置進行排序。如果列名或表達式名稱很長，那么使用列位號排序可以縮減排序語句長度。另外，當使用UNION、UNION ALL、INTERSECT、MINUS等集合操作符合并查詢結果時，如果選擇列表的列名不同，并且希望進行排序，則必須使用列位置進行排序。

【例4.48】 查詢emp表中員工的年工資，并按照年工資降序排列，代碼如下。

        SQL> select empno, ename, sal*12 Annual Salary
          2  from emp
          3  order by 3 desc;

這里的3表示第3列，所以ORDER BY 3就是按第3列排序。

本例運行結果如圖4.52所示。

圖4.52 使用列號排序

但是在SQL語句中應該盡可能地不使用ORDER BY子句的上述用法，因為這種用法的易讀性實在太差了。在不少有關Oracle SQL的書中根本就沒有介紹ORDER BY子句的這種用法。盡管如此，當使用UNION、UNION ALL、INTERSECT、MINUS等集合操作符合并查詢結果時，或者為了減少輸入，特別是當放在ORDER BY子句之后的列名或表達式很長時，還是可以使用此種用法。本書介紹這一用法的目的是，當看到SQL語句中包含了這一用法時，讀者可以理解它，但并不鼓勵用。

可以使用非選擇列表列排序。當執行排序操作時，多數情況都會選擇列表中的列執行排序操作，以便于更直觀地顯示數據。但是在執行排序操作時，排序列也可以不是選擇列表中的列。

【例4.49】 使用非選擇列表列進行排序的方法，按工資降序顯示員工名。代碼如下。

        SQL> select ename from emp order by sal desc;

本例運行結果如圖4.53所示。

圖4.53 使用非選擇列表列排序

互動練習：使用列的別名排序。

2．多列排序

當執行排序操作時，不僅可以基于單列進行排序，也可以基于多列進行排序。當以多列進行排序時，首先按照第一列進行排序，當第一列存在相同數據時，再以第二列進行排序，以此類推。

【例4.50】 查詢emp表，按照部門號升序工資降序顯示雇員名、部門號和工資，代碼如下。

        SQL> select ename, deptno, sal
          2  from emp
          3  order by deptno, sal desc;

在此查詢語句，首先按照部門號排序，在同一個部門中，按照工資的從高到低進行排序。本例運行結果如圖4.54所示。

圖4.54 多列排序

4.3.5 多表關聯查詢

在實際的應用系統開發中會設計多個數據表，每個表的信息不是獨立存在的，而是若干個表之間的信息存在一定的關系，當用戶查詢某一個表的信息時，很可能需要查詢關聯數據表的信息，這就是多表關聯查詢。SELECT語句自身是支持多表關聯查詢的，多表關聯查詢要比單表查詢復雜得多。在進行多表關聯查詢時，可能會涉及表別名、內連接、外連接、自然連接和交叉連接等概念，下面將對這些內容進行講解。

1．表的別名

在多表關聯查詢時，如果多個表之間存在同名的列，則必須使用表名來限定列的引用。例如，在SCOTT模式中，DEPT表和EMP表都有DEPTNO列，那么當用戶使用該列關聯查詢兩個表時，就需要通過指定表名來區分這兩個列的歸屬。但是，隨著查詢變得越來越復雜，語句就會因為每次限定列必須輸入表名而變得冗長。對于這種情況，SQL語言提供了設定表別名的機制，使用簡短的表別名就可以替代原有較長的表名稱，這樣就可以大大縮減語句的長度。

【例4.51】 在SCOTT模式下，通過DEPTNO（部門號）列來關聯emp表和dept表，并檢索這兩個表中相關字段的信息，代碼及運行結果如下（實例位置：光盤\TM\sl\4\8）。

        SQL>select e.empno as員工編號，e.ename as員工名稱，d.dname as部門
          2  from emp e, dept d
          3  where e.deptno=d.deptno
          4  and e.job='MANAGER';


          員工編號                    員工名稱              部門
        ---------------            ----------          -------------
              7782                  CLARK               ACCOUNTING
              7566                  JONES               RESEARCH
              7698                  BLAKE               SALES

在上面的SELECT語句中，FROM子句最先執行，然后才是WHERE子句和SELECT子句，這樣在FROM子句中指定表的別名后，當需要限定引用列時，其他所有子句都可以使用表的別名。

另外，還需要注意一點，一旦在FROM子句中為表指定了別名，則必須在剩余的子句中都使用表的別名，而不允許再使用原來的表名稱，否則，將出現如圖4.55所示的提示。

圖4.55 關于標識符無效的錯誤提示

總結一下，使用表的別名的注意事項如下。

表的別名在FROM子句中定義，別名放在表名之后，它們之間用空格隔開。

別名一經定義，在整個查詢語句中就只能使用表的別名而不能再使用表名。

表的別名只在所定義的查詢語句中有效。

應該選擇有意義的別名，表的別名最長為30個字符，但越短越好。

互動練習：理解笛卡爾積的概念。

2．內連接

內連接是一種常用的多表關聯查詢方式，一般使用關鍵字INNER JOIN來實現。其中，INNER關鍵字可以省略，當只使用JOIN關鍵字時，語句只表示內連接操作。在使用內連接查詢多個表時，必須在FROM子句之后定義一個ON子句，ON子句指定內連接操作列出與連接條件匹配的數據行，使用比較運算符比較被連接列的值。簡單地說，內連接就是使用JOIN指定用于連接的兩個表，使用ON指定連接表的連接條件。若進一步限制查詢范圍，則可以直接在后面添加WHERE子句。內連接的語法格式如下：

        SELECT columns_list
        FROM table_name1[INNER] JOIN table_name2
        ON join_condition;

columns_list：字段列表。

table_name1和table_name2：兩個要實現內連接的表。

join_condition：實現內連接的條件表達式。

【例4.52】 在SCOTT模式下，通過deptno字段來內連接emp表和dept表，并檢索這兩個表中相關字段的信息，代碼及運行結果如下。

        SQL>select e.empno as員工編號，e.ename as員工名稱，d.dname as部門
          2  from emp e inner join dept d
          3  on e.deptno=d.deptno;

本例運行結果如圖4.56所示。

圖4.56 內連接操作

由于上面代碼表示內連接操作，所以在FROM子句中完全可以省略INNER關鍵字。

3．外連接

使用內連接進行多表查詢時，返回的查詢結果中只包含符合查詢條件和連接條件的行。內連接消除了與另一個表中的任何行不匹配的行，而外連接擴展了內連接的結果集，除了返回所有匹配的行外，還會返回一部分或全部不匹配的行，這主要取決于外連接的種類。外連接通常有以下3種。

左外連接：關鍵字為LEFT OUTER JOIN或LEFT JOIN。

右外連接：關鍵字為RIGHT OUTER JOIN或RIGHT JOIN。

完全外連接：關鍵字為FULL OUTER JOIN或FULL JOIN。

與內連接不同的是，外連接不只列出與連接條件匹配的行，還能夠列出左表（左外連接時）、右表（右外連接時）或兩個表（全部外連接時）中所有符合搜索條件的數據行。

（1）左外連接

左外連接的查詢結果中不僅包含了滿足連接條件的數據行，而且還包含左表中不滿足連接條件的數據行。

【例4.53】 首先使用INSERT語句在emp表中插入新記錄（注意沒有為deptno和dname列插入值，即它們的值為null），然后實現emp表和dept表之間通過deptno列進行左外連接，具體代碼如下（實例位置：光盤\TM\sl\4\9）。

        SQL> insert into emp(empno, ename, job) values(9527, 'EAST', 'SALESMAN');


        已創建1行。


        SQL> select e.empno, e.ename, e.job, d.deptno, d.dname
          2  from emp e left join dept d
          3  on e.deptno=d.deptno;

本例運行結果如圖4.57所示。

圖4.57 左外連接操作

從上面的查詢結果中可以看到，雖然新插入數據行的deptno列值為null，但該行記錄仍然出現在查詢結果中，這說明左外連接的查詢結果會包含左表中不滿足“連接條件”的數據行。

（2）右外連接

同樣道理，右外連接的查詢結果中不僅包含了滿足連接條件的數據行，而且還包含右表中不滿足連接條件的數據行。

【例4.54】 在SCOTT模式下，實現emp表和dept表之間通過deptno列進行右外連接，具體代碼如下。

        SQL> select e.empno, e.ename, e.job, d.deptno, d.dname
          2  from emp e right join dept d
          3  on e.deptno=d.deptno;

本例運行結果如圖4.58所示。

圖4.58 右外連接

從上面的查詢結果中可以看到，雖然部門編號為40的部門現在在emp表中還沒有員工記錄，但它卻出現在查詢結果中，這說明右外連接的查詢結果會包含右表中不滿足“連接條件”的數據行。

在外連接中也可以使用外連接的連接運算符，外連接的連接運算符為“（+）”，該連接運算符可以放在等號的左邊，也可以放在等號的右邊，但一定要放在缺少相應信息的那一邊，如放在e.deptno所在的一方。

上面的查詢語句還可以這么寫，代碼如下：

        SQL> select e.empno, e.ename, e.job, d.deptno, d.dname
          2  from emp e, dept d
          3  where e.deptno(+)=d.deptno;

本例運行結果如圖4.59所示。

圖4.59 使用外連接的連接運算符“（+）”

使用“（+）”操作符時應注意：

當使用“（+）”操作符執行外連接時，如果在WHERE子句中包含多個條件，則必須在所有條件中都包含“（+）”操作符。

“（+）”操作符只適用于列，而不能用在表達式上。

“（+）”操作符不能與ON和IN操作符一起使用。

（3）完全外連接

在執行完全外連接時，Oracle會執行一個完整的左外連接和右外連接查詢，然后將查詢結果合并，并消除重復的記錄行。

【例4.55】 在SCOTT模式下，實現emp表和dept表之間通過deptno列進行完全外連接，具體代碼如下（實例位置：光盤\TM\sl\4\10）。

        SQL>  select e.empno, e.ename, e.job, d.deptno, d.dname
          2  from emp e full join dept d
          3  on e.deptno=d.deptno;

本例運行結果如圖4.60所示。

圖4.60 完全外連接

4．自然連接

自然連接和內連接的功能相似，自然連接是指在檢索多個表時，Oracle會將第一個表中的列與第二個表中具有相同名稱的列進行自動連接。在自然連接中，用戶不需要明確指定進行連接的列，這個任務由Oracle系統自動完成，自然連接使用NATURAL JOIN關鍵字。

【例4.56】 在emp表中檢索工資（sal字段）大于2000的記錄，并實現emp表與dept表的自然連接，具體代碼如下：

        SQL> select empno, ename, job, dname
          2  from emp natural join dept
          3  where sal>2000;

本例運行結果如圖4.61所示。

圖4.61 自然連接

由于自然連接強制要求表之間必須具有相同的列名稱，這樣容易在設計表時出現不可預知的錯誤，所以在實際應用系統開發中很少用到自然連接。但這畢竟是一種多表關聯查詢數據的方式，在某些特定情況下還是有一定的使用價值。另外，需要注意的是，在使用自然連接時，不能為列指定限定詞（即表名或表的別名），否則Oracle系統會彈出“ORA-25155: NATURAL連接中使用的列不能有限定詞”的錯誤提示。

5．自連接

在應用系統開發中，用戶可能會擁有“自引用式”的外鍵。“自引用式”外鍵是指表中的一個列可以是該表主鍵的一個外鍵。

自連接主要用在自參考表上顯示上下級關系或者層次關系。自參照表是指在同一張表的不同列之間具有參照關系或主從關系的表。例如，emp表包含empno（雇員號）和mgr（管理員號）列，兩者之間就具有參照關系。這樣用戶就可以通過mgr列與empno列的關系，實現查詢某個管理者所管理的下屬員工信息，如圖4.62所示。

圖4.62 emp表中empno列和mgr列之間的關系

根據empno列和mgr列的對應關系，可以確定雇員JONES、BLAKE和CLARK的管理者為KING。為了顯示雇員及其管理者之間的對應關系，可以使用自連接。因為自連接是在同一張表之間的連接查詢，所以必須定義表別名。通過下面的實例，說明使用自連接的方法。

【例4.57】 在SCOTT模式下，查詢所有管理者所管理的下屬員工信息，具體代碼如下：

        SQL>select em2.ename上層管理者，em1.ename as下屬員工
          2  from emp em1 left join emp em2
          3  on em1.mgr=em2.empno
          4  order by em1.mgr;

本例運行結果如圖4.63所示。

圖4.63 自連接

6．交叉連接

交叉連接實際上就是不需要任何連接條件的連接，它使用CROSS JOIN關鍵字來實現，其語法格式如下：

        Select colums_list
        From table_name1 cross join table_name2

colums_list：字段列表。

table_name1和table_name2：兩個實現交叉連接的表名。

交叉連接的執行結果是一個笛卡爾積，這種查詢結果是非常冗余的，但可以通過WHERE子句來過濾出有用的記錄信息，如下面實例所示。

【例4.58】 在SCOTT模式下，通過交叉連接dept表和emp表，計算出查詢結果的行數，具體代碼如下：

        SQL> select count(*)
          2  from dept cross join emp;

本例運行結果如圖4.64所示。

圖4.64 交叉連接