oracle中子查询关联,ORACLE关联子查询和ALL的用法

由一道题引发的思考：

103. View the Exhibit and examine the structure of

EMPLOYEES and JOB_HISTORY

tables.The EMPLOYEES table maintains the most

recent information regarding

salary, department, and job for all the employees.

The JOB_HISTORY table

maintains the record for all the job changes for

the employees. You want to

delete all the records from the JOB_HISTORY table

that are repeated in the

EMPLOYEES table. Which two SQL statements can you

execute to accomplish the

task? (Choose two.)

A. DELETE FROM job_history j

WHERE employee_id =(SELECT

employee_id

FROM employees e WHERE j.employee_id =

e.employee_id)

AND job_id = (SELECT job_id FROM employees

e

WHERE j.job_id = e.job_id);

B. DELETE FROM job_history j

WHERE (employee_id, job_id) =

ALL

(SELECT employee_id, job_id FROM employees e WHERE

j.employee_id =

e.employee_id and j.job_id = e.job_id

)

C. DELETE FROM job_history j WHERE employee_id

=(SELECT employee_id

FROM employees e WHERE j.employee_id =

e.employee_id and j.job_id =

e.job_id )

D. DELETE FROM job_history j

WHERE (employee_id, job_id) = (SELECT employee_id,

job_id

FROM employees e WHERE j.employee_id =

e.employee_id and j.job_id =

e.job_id )

Answer: CD

构建测试表EMPLOYEES和BONUSES的数据分别如下：

select EMPLOYEE_ID,job_id

from EMPLOYEES

/

EMPLOYEE_ID

JOB_ID

2

2

3

3

4

4

5

5

6

6

7

7

8

8

9

9

1

2

select EMPLOYEE_ID,job_id

from BONUSES

/

EMPLOYEE_ID

JOB_ID

10

8

2

1

1

2

4

3

测试几种不同写法语句的执行结果：

select e.EMPLOYEE_ID,e.job_id

from EMPLOYEES e

where e.EMPLOYEE_ID =(select

b.EMPLOYEE_ID

from BONUSES b

where b.EMPLOYEE_ID=e.EMPLOYEE_ID)

and e.JOB_ID = (

select b.job_id

from BONUSES b

where

b.job_id=e.job_id

)

/

EMPLOYEE_ID

JOB_ID

2

2

1

2

--对两列分别进行查询，返回EMPLOYEE_ID和JOB_ID在BONUSES表里能查到数据的结果，但是并不是EMPLOYEE_ID和JOB_ID完全对应匹配一致的数据；因此结果不正确；并且，这种写法在子查询里返回多行的时候会出现语法错误；

select e.EMPLOYEE_ID,e.job_id

from EMPLOYEES e

where e.EMPLOYEE_ID=(select

b.EMPLOYEE_ID

from BONUSES b

where b.EMPLOYEE_ID=e.EMPLOYEE_ID

and b.job_id=e.JOB_ID)

/

select e.EMPLOYEE_ID,e.job_id

from EMPLOYEES e

where e. JOB_ID =(select b. JOB_ID

from BONUSES b

where b.EMPLOYEE_ID=e.EMPLOYEE_ID

and b.job_id=e.JOB_ID)

/

EMPLOYEE_ID

JOB_ID

1

2

----查询EMPLOYEE_ID和JOB_ID完全对应匹配一致的数据；两种写法的效果是完全一样的；

select e.EMPLOYEE_ID,e.job_id

from EMPLOYEES e

where (e.EMPLOYEE_ID,e.JOB_ID) = (

select b.EMPLOYEE_ID,b.job_id

from BONUSES b

where b.EMPLOYEE_ID=e.EMPLOYEE_ID

and b.job_id=e.job_id

)

/

EMPLOYEE_ID

JOB_ID

1

2

--使用多列关联子查询，查询EMPLOYEE_ID和JOB_ID完全对应匹配一致的数据

select e.EMPLOYEE_ID,e.job_id

from EMPLOYEES e

where (e.EMPLOYEE_ID,e.JOB_ID) = all(

select b.EMPLOYEE_ID,b.job_id

from BONUSES b

where b.EMPLOYEE_ID=e.EMPLOYEE_ID

and b.job_id=e.job_id

)

/

EMPLOYEE_ID

JOB_ID

2

2

3

3

4

4

5

5

6

6

7

7

8

8

9

9

1

2

--加上all之后的语句把EMPLOYEES表里的所有记录都查出来了，相当于加了一个TRUE的条件；

在这里ALL的作用究竟是什么？作为逻辑运算符还是算数运算符？不太明白