SQL 基础之多表查询（十）

JOINS 类型和它的语法

Natural joins（自然连接）:

– NATURAL JOIN 子句

– USING 子句

– ON 子句

自连接

  非等值连接

Outer joins（外连接）:

– LEFT OUTER JOIN（左外连接）

– RIGHT OUTER JOIN（右外连接）

– FULL OUTER JOIN（全外连接）

笛卡尔积

– Cross join（交叉连接）

语法：

select table1.column, table2.column

from table1

[natural join table2] |

[join table2 using (column_name)] |

[join table2

on (table1.column_name = table2.column_name)]|

[left|right|full outer join table2

on (table1.column_name = table2.column_name)]|

[cross join table2];

限制重复的列名

• 在多表中使用表前缀限制列名

• 使用表前缀可以提高效率

• 使用表别名代替全表名前缀

• 表别名提供一个较短的名称：

    – sql代码量更少，使用较少的内存

• 在不同表中具有相同列名的列可以用别名加以区分

创建自然连接

• NATURAL JOIN子句，会以两个表中具有相同名字的列为条件 创建等值连接。

• 在表中查询满足等值条件的数据。

• 如果只是列名相同而数据类型不同，则会产生错误。

• 如果多个列名符合，都会做为条件。

1、查询department_id 和department_name 在哪些城市

select department_id,department_name, location_id,city from departments natural join locations;

使用 USING 子句创建连接

• 如果多个列具有相同的名称，但自然连接的数据类型又不匹配，则可以使用using子句来指定，使用一 个等值的列

• 当有多个列匹配时，用using子句匹配唯一的列

• NATURAL JOIN 和 USING 子句互斥

• 不要给选中的列中加上表名前缀或别名

1、查询employee_id,last_name,location_id 从员工表，并且使用department_id为指定键值

select employee_id, last_name,location_id, department_id from employees join departments using (department_id);

ON 子句创建连接

• 自然连接中是以具有相同名字的列为连接条件的    

• 使用ON子句指定要连接任意条件或指定列连接条件

• 这个连接条件是与其它条件分开的

• ON 子句使语句具有更高的易读性

1、查找employees和 的departments 两张表员工信息，并按照department_id为条件

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id from employees e join departments d on (e.department_id = d.department_id);

使用AND子句或WHERE子句适用附加条件： 查询manager_id为149的

select e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

from employees e join departments d

on (e.department_id = d.department_id)

and e.manager_id = 149;

或者

select e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

from employees e join departments d

on (e.department_id = d.department_id)

where e.manager_id = 149 ;

使用 ON 子句自连接

使用条件 WORKER 表 表 MANAGER_ID 等于MANAGER 的EMPLOYEE_ID

select worker.last_name emp, manager.last_name mgr

from employees worker join employees manager

on (worker.manager_id = manager.employee_id);

非等值连接

select e.last_name, e.salary, j.grade_level

from employees e join job_grades j

on e.salary

between j.lowest_sal and j.highest_sal;

使用外连接返回没有直接匹配的记录

• 在SQL:1999中，两个表连接，只返回匹配的行，被称为内连接。

• 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。

• 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为全 外联接。

左外连接

select e.last_name, e.department_id, d.department_name

from employees e left outer join departments d

on (e.department_id = d.department_id) order by department_id desc;

右外连接

select e.last_name, e.department_id, d.department_name

from employees e right outer join departments d

on (e.department_id = d.department_id) ;

全外连接

select e.last_name, d.department_id, d.department_name

from employees e full outer join departments d

on (e.department_id = d.department_id) ;

笛卡尔积

• 笛卡尔集会在下面条件下产生:

– 连接条件被遗漏

– 连接条件不正确

– 所有表中的所有行互相连接

• 为了避免笛卡尔集，可以在 WHERE 加入有效的连接条件。

创建交叉连接

• 使用 CROSS JOIN子句使连接的表产生叉集。

• 叉集也被称为在两个表之间的笛卡尔乘积。

select last_name, department_name from employees cross join departments;

练习题:

1. 为HR部门写一条查询语句，要求结果生成所有部分的地址。请使用 LOCATIONS 和 COUNTRIES 表，

要求输出 location_id，street_address，city，state_province，以及 country。使用自然连接获得要求的结果

select location_id,street_address,city,state_province,country_id,country_name from locations  natural join countries;

2. HR部门需要一个可以查出所有员工的 last_name，department_id,department_name 的查询语句

select last_name,department_id,department_name from employees join departments using(department_id);

3.  请查出所有在 Toronto工作的员工的 last_name,job_id,department_id,department_name.

select e.last_name,e.job_id,e.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';

如果Toronto 为小写的toronto可以用下面语句

select e.last_name,e.job_id,e.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 lower(l.city) ='toronto';

4.  请查询出每个员工的 last_name,employee_id,经理的 last_name,manager_id。请依次为这

些 列 取 合 适 的 别 名 ”Emeployee”,”EMP#”,”Manager”,”Mgr#”。

select w.last_name "Employee",w.employee_id "EMP#",

m.last_name "Manager",m.employee_id "Mgr#"

from employees w join employees m

on (w.manager_id = m.employee_id);

5、显示所有员工（包括没有经理的 King），请根据员工编号进行排序

select w.last_name "employee", w.employee_id "emp#",

m.last_name "manager",m.employee_id "mgr#"

from employees w

left outer join employees m

on (w.manager_id = m.employee_id)

order by 2;

6.  请查询出每个员工的 department_id，员工的 last_name，以及在同一部门一起工作的同事

select e.department_id department,e.last_name employee,

c.last_name colleague

from employees e join employees c

on (e.department_id = c.department_id)

where e.employee_id <> c.employee_id

order by e.department_id,e.last_name,c.last_name;

7、查 询 所 有 员 工 的last_name,job_id,department_name,salary,grade_level。

select e.last_name,e.job_id,d.department_name,

e.salary,j.grade_level

from employees e join departments d

on (e.department_id = d.department_id)

join job_grades j

on (e.salary between j.lowest_sal and j.highest_sal);

8、 HR 部门的同事想知道所有在 Davies 之后被雇佣的员工。请帮忙写出一条 SQL 查出来这些员工的last_name 以及 hire_date

select e.last_name,e.hire_date

from employees e join employees davies

on (davies.last_name='Davies')

where davies.hire_date < e.hire_date;

9.  HR 部门的同事想要找出那些在他们的经理之前被雇佣的员工的 last_name,hire_date，以及他们

经理的 last_name，和 hire_date

select w.last_name,w.hire_date,m.last_name,m.hire_date

from employees w join employees m

on (w.manager_id=m.employee_id)

where w.hire_date < m.hire_date;