Oracle之---子查詢語法

SELECT select_list

FROM  table

WHERE  exPR Operator

     (SELECT select_list

         FROM  table);

   =         

   >= 

   <

   <= 

   <>

題目：返回job_id與141號員工相同，salary比143號員工多的員工

            姓名，job_id和工資

SELECTlast_name,job_id,salary

FROM   employees

WHERE job_id= 

                (SELECTjob_id

                 FROM   employees

                 WHERE employee_id= 141)

AND    salary >

                (SELECT salary

                 FROM   employees

                 WHERE employee_id= 143);

在查詢中使用組函數

SELECTlast_name,job_id, salary

FROM   employees

WHERE  salary =

                (SELECT  MIN(salary)

                 FROM   employees);

子查詢中的HAVING 語句

[

SELECT   department_id,MIN(salary)

FROM     employees

GROUPBY department_id

HAVING   MIN(salary) >

                       (SELECT MIN(salary)

                        FROM   employees

                        WHERE  department_id= 50);

多行子查詢

*返回多行

*使用多行比較符

IN   等于列表的任意一個

ANY  和子查詢返回的某一個值

ALL  和子查詢返回的所有值進行比較

題目：返回其它部門中比job_id為‘IT_PROG’部門任一工資低的員工的員

              工號、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id, salary

FROM   employees

WHERE  salary < ANY

                    (SELECTsalary

                     FROM   employees

                     WHERE  job_id = 'IT_PROG')

AND    job_id <> 'IT_PROG';