select查詢之三：子查詢

Select語句的總結構：

Select [欄位] [限制條件]  

from [資料表]  /子查詢[巢狀查詢]

where [篩選條件]  /子查詢[巢狀查詢]

group by[分組欄位]  /子查詢[巢狀查詢]

Having [輔助篩選條件] /子查詢[巢狀查詢]

order by[排序欄位];

如以上所示，子查詢或者巢狀查詢就是巢狀在基本select語句中的各個字句裡面，
輔助主查詢完成複雜的查詢任務。子查詢裡面還可以繼續巢狀select語句，叫做巢狀查詢。

1、單行子查詢：
檢視與Sully同部門員工的資訊。

SQL> select employee_id,last_name,salary,department_id

  2  from employees

  3  where department_id=

  4  (select department_id

  5   from employees

  6  where last_name = 'Sully');

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

        145 Russell                        14000            80

        146 Partners                       13500            80

        147 Errazuriz                      12000            80

        148 Cambrault                      11000            80

        149 Zlotkey                        10500            80

        150 Tucker                         10000            80

        151 Bernstein                       9500            80

        152 Hall                            9000            80

        153 Olsen                           8000            80

        154 Cambrault                       7500            80

        155 Tuvault                         7000            80

.....

34 rows selected.

2、多行子查詢：

各部門最高工資的員工資訊：多欄位條件

SQL> select last_name,department_id,salary

  2   from employees

  3  where(department_id,salary) in

  4  (select department_id,max(salary)

  5  from employees

  6  group by department_id);

LAST_NAME                 DEPARTMENT_ID     SALARY

------------------------- ------------- ----------

Greenberg                           100      12008

Raphaely                             30      11000

Hartstein                            20      13000

Baer                                 70      10000

King                                 90      24000

Higgins                             110      12008

Fripp                                50       8200

Mavris                               40       6500

Russell                              80      14000

Whalen                               10       4400

Hunold                               60       9000

11 rows selected.

3、where字句子查詢：

該部門中工資高於1000的員工數量超過2人，列出符合條件的部門：顯示部門名字。

SQL> select department_name,department_id

  2  from departments

  3  where department_id in

  4  (select department_id

  5  from employees

  6  where salary >1000

  7  group by department_id

  8  having count(*)>2);

DEPARTMENT_NAME                DEPARTMENT_ID

------------------------------ -------------

Finance                                  100

Purchasing                                30

Executive                                 90

Shipping                                  50

Sales                                     80

IT                                        60

6 rows selected.

4、exists 或者not exists 的用法：

在HR使用者，查詢沒有任何職員的部門。

SQL> select department_id,department_name

  2  from departments d

  3  where not exists(

  4  select 1 from employees t

  5  where d.department_id = t.department_id);

DEPARTMENT_ID DEPARTMENT_NAME

------------- ------------------------------

          120 Treasury

          130 Corporate Tax

          140 Control And Credit

          150 Shareholder Services

          160 Benefits

          170 Manufacturing

.....

         260 Recruiting

          270 Payroll

16 rows selected.

 
5、from字句子查詢：

From子查詢：檢視工資在12000與14000之間員工的工資：

SQL> select * from(

  2  select employee_id,last_name,salary

  3  from employees

  4  where salary between 12000 and 14000);

EMPLOYEE_ID LAST_NAME                     SALARY

----------- ------------------------- ----------

        201 Hartstein                      13000

        205 Higgins                        12008

        108 Greenberg                      12008

        145 Russell                        14000

        146 Partners                       13500

        147 Errazuriz                      12000

6 rows selected.

 
6、多列子查詢：
在表emp查詢與 Sully相同入職時間與相同部門員工的資訊：

SQL> select employee_id,last_name,department_id,hire_date

  2  from employees

  3  where (department_id,hire_date)=

  4  (select department_id,hire_date

  5  from employees

  6  where last_name = 'Sully');

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID HIRE_DATE

----------- ------------------------- ------------- ---------

        157 Sully                                80 04-MAR-04

在此，還有巢狀查詢與having字句子查詢沒有列出類，用法與以上相類似，不在一一舉例。