数据库学习笔记2018-12-22（多表连接）

多表连接

---

SQL语句的多表查询方式：

例如：按照depaatment_id查询employees(员工表)和departments(部门表)的信息。

1.（通用性）select ......from...where.....

select last_name,e.department_id,department_name from employees e,department d
where e.department_id=d.department_id

2.(有局限性)SELECT ... FROM ... NATURAL JOIN ...

有局限性：会自动连接两个表中相同的列(可能有多个:department_id和manager_id)

SELECT last_name,department_id,department_name
FROM employees
NATURAL JOIN departments

3.（有局限性）SELECT ... JOIN ... USING ...

有局限性：好于方式二，但若多表的连接列列名不同，此法不合适

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

4.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

--内连接
    1）
    --等值连接 id=id
    --不等值连接 between...and..
    2）
    --非自连接：俩表及俩表以上连接
    --自连接：自己与自己连接

--外连接

  --左外连接、右外连接、满外连接

（例：a表与b表连接

1.a中有b中没有要想显示b中没有的用左外连接。

--方式1
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
where e.department_id = d.department_id（+）
--方式2
SELECT last_name,e.department_id,department_name
FROM employees e
left outer JOIN departments d
ON e.department_id = d.department_id

2.b中有a中没有要想显示a中没有的用右外连接

--方式1
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
where e.department_id（+） = d.department_id
--方式2
SELECT last_name,e.department_id,department_name
FROM employees e
right outer JOIN departments d
ON e.department_id = d.department_id

3.要想显示全部 用 满外连接）full join...on 

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