Oracle-查询-多表连接查询

秒速五厘米 2022-04-03 13:17 328阅读 0赞

*  **左外连接是以join左边作为主表，右连接以join右边做为主表**
 *  **外连接查询出来的结果相当于两个部分，一个部分是交集部分（相当于利用等值活非等值连接查询出来的结果），另外一个部分是连接条件主表中有而从表中没有的部分（这一部分显示的连接条件为null，这一部分是等值活非等值连接不能满足的）**

# sql92 #

### 等值连接 ###

SELECT e.last_name,d.department_name,l.city
    FROM employees e,departments d,locations l
    WHERE e.department_id = d.department_id AND d.location_id = l.location_id

### 非等值连接 ###

SELECT e.last_name,e.salary,j.grade_level
    FROM employees e,job_grades j
    WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal FOR UPDATE;

### 自链接 ###

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
    FROM employees e,employees m
    WHERE e.manager_id = m.employee_id;

### 左连接 ###

SELECT last_name,e.department_id,department_name
    FROM employees e,departments d
    WHERE e.department_id = d.department_id(+)

### 右外连接 ###

SELECT last_name,e.department_id,department_name
    FROM employees e,departments d
    WHERE e.department_id(+) = d.department_id

# sql99 #

### 内连接 ###

与等值连接结果相同

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

SELECT e.last_name,d.department_name,l.city
    FROM employees e 
    JOIN departments d ON e.department_id = d.department_id
    JOIN locations l ON d.location_id = l.location_id
    -- 等价于
    SELECT e.last_name,d.department_name,l.city
    FROM employees e,departments d,locations l
    WHERE e.department_id = d.department_id AND d.location_id = l.location_id

### 左外连接 ###

SELECT e.employee_id,e.last_name,d.department_name
    FROM employees e 
    LEFT OUTER JOIN departments d ON e.department_id = d.department_id
    LEFT OUTER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
    WHERE d.department_name LIKE '%S%';

### 右外连接 ###

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

### 全外连接 ###

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

## ##

# 练习 #

-- 查询出 last_name 为 'Chen' 的 manager 的信息. (员工的 manager_id 是某员工的 employee_id) 
    SELECT e.employee_id, e.email,m.last_name
    FROM employees e,employees m
    WHERE e.department_id = m.employee_id AND e.last_name = 'Chen';
    -- 子查询
    SELECT m.employee_id,m.last_name
    FROM employees m
    WHERE m.employee_id = (
                            SELECT e.employee_id
                            FROM employees e
                            WHERE e.last_name = 'Chen'
                        );

-- 左外连接
    select last_name, e.department_id, department_name
    from employees e, departments d
    where e.department_id = d.department_id(+)
    -- 右外连接
    select last_name, d.department_id, department_name
    from employees e, departments d
    where e.department_id(+) = d.department_id

-- 1.	显示所有员工的姓名，部门号和部门名称。
    SELECT e.last_name,d.department_id,d.department_name
    FROM employees e 
    JOIN departments d
    ON e.department_id = d.department_id
    --
    SELECT e.last_name,department_id,d.department_name
    FROM employees e 
    JOIN departments d USING(department_id);

-- 2.	查询90号部门员工的job_id和90号部门的location_id
    SELECT DISTINCT e.job_id,d.location_id
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.department_id = '90';

-- 3.	选择所有有奖金的员工的 last_name , department_name , location_id , city
    SELECT e.last_name,d.department_name,l.location_id,l.city
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    JOIN locations l
    ON d.location_id = l.location_id
    WHERE e.commission_pct IS NOT NULL;

-- 4.	选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
    SELECT e.last_name,e.job_id,d.department_id,d.department_name
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    JOIN locations l
    ON d.location_id = l.location_id
    WHERE l.city = 'Toronto';