Oracle学习之多表查询，子查询（三）

一. SQL语句的多表查询方式

• 方式一(通用型)：SELECT … FROM … WHERE

SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
where e.department_id = d.department_id;

• 方式二：SELECT … FROM … NATURAL JOIN …
  有局限性：会自动连接两个表中相同的列(可能有多个:department_id和manager_id)

SELECT last_name,department_id,department_name
FROM employees
NATURAL JOIN departments;

• 方式三：SELECT … JOIN … USING …
  有局限性：两个表中必须的是相同的列名和数据类型，好于方式二，但若多表的连接列列名不同，此法不合适

SELECT last_name,department_id,department_name
FROM employees
JOIN departments
USING(department_id);

• 方式四：SELECT … FROM … JOIN … ON …
  常用方式，较方式一，更易实现外联接(左、右、满)

SELECT last_name,e.department_id,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

二. 内连接

• 等值连接

#两个表连接
select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id=d.department_id;

#n个表连接
select employee_id,e.department_id,d.department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id = l.location_id;

• 不等值连接

select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal;

• 自连接

select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp,employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name)='chen';

三. 外连接

• 左外连接

select employee_id,d.department_id,department_name
from employees e left outer join departments  d
on e.department_id = d.department_id;

• 右外连接

select employee_id,d.department_id,department_name
from employees e right outer join departments  d
on e.department_id = d.department_id;

• 满外连接

select employee_id,d.department_id,department_name
from employees e full outer join departments  d
on e.department_id = d.department_id;

四. 子查询

单行子查询

#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
select last_name,job_id,salary
from employees
where job_id = (select job_id from employees where employee_id = 141) and
salary > (select salary from employees where employee_id = 143 );

#题目：返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary
from employees
where salary = (select min(salary) from employees);

#题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by 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 job_id <> 'IT_PROG' and salary < any  #<>不等于
(select salary from employees where job_id = 'IT_PROG');

#题目：返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员工号、姓名、job_id 以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <> 'IT_PROG' and salary < all 
(select salary from employees where job_id = 'IT_PROG');