Oracle课程档案,第四天
“子查询”就是查询中嵌套着另一个查询,也即通过SELECT语句的嵌套使用形成子查询。当我们不知道特定的查询条件时,可以用子查询来为父查询提供查询条件以获得查询结果。
子查询先清除子查询 在清除主查询
子查询必须放在括号内。
单行子查询并不是最后输出的结果只能返回一行,而是指子查询只能返回一行
employee_id:员工的工号
department_id:部门编号
department_name:部门名称
exists:是否存在 ★ 关联子查询
employees和departments 两个表
is not null:不是空值
in包含里面的条件 个人这么理解
SQL> select e.department_id ,d.department_name,e.salary from employees e, departments d where e.department_id=d.department_id;
多表连接查询部门编号,名称,工资的命令。
条件里面有in 子查询里面可以不加 is not null 如果是not in 则必须加 is not null is not null不是空值
in not in 受null的影响
单行子查询的思路:
SQL> select salary from employees where last_name='Feeney';
SQL> select last_name from employees where salary>3000;
SQL> select last_name from employees where salary>(select salary from employees where last_name='Feeney');
多行子查询的思路:
SQL> select distinct department_id from employees where department_id is not null;
SQL> select department_name from departments where department_id in (10, 20,30);
SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);
用多表连接改写:
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id