Oracle 子查询学习

Oracle 子查询

1.定义：子查询就是嵌套在主查询里的查询了
2.示例：找出所有工资比Den(FIRST_NAME)高的员工

SELECT FIRST_LAME || ' ' || LAST_NAME  FROM EMPLOYEES T0, EMPLOYEES T1  
WHERE T0.SALARY > T1.SALARY
AND T1.FIRST_NAME = 'Den'

SELECT FIRST_LAME || ' ' || LAST_NAME  FROM EMPLOYEES 
WHERE SALARY > ( SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME = 'Den') 

第一句sql只是普通的连接查询，第二句sql就是一个子查询

Oracle 非关联子查询

1.定义：非关联子查询就是指子查询可以脱离主查询独立执行
2.示例：Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁（非关联子查询）

SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = (SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Den' AND  LAST_NAME = 'Raphaely');

Oracle 关联子查询

1.定义：关联子查询就是指子查询与主查询之间有条件关联, 不能独自执行
2.示例：Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁（关联子查询）

SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES T0 WHERE EXISTS (SELECT 1 FROM EMPLOYEES T1 WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely' AND T1.MANAGER_ID = T0.EMPLOYEE_ID);