连接查询——等值连接、非等值连接、自连接

含义：又称为多表查询，连接查询；

笛卡尔乘积现象：表1 有m行，表2，有n行；得到结果为m*n行

没有添加有效的连接条件；

Select name,boyName from boys,beauty;（这种写法是错误的）

Select name,boyName from boys,beauty where beauty.boyfriend_id=boys.id;

分类：按年代分：sq1992标准：仅支持内连接,sql 1999标准推荐：支持内外连接+交叉连接；

按功能分：

（1）内连接：等值连接，非等值连接，自连接；

(2)外连接：右外连接，左外连接，全外连接；

(3)交叉连接

Sql92标准：

特点：多表等职连接的结果为多表的交集部分；

n表连接，至少需要n-1个连接条件

多表的顺序没有要求，一般需要为表起别名，可以搭配前面介绍的所有子句使用， 比如排序、分组、筛选；

等值查询

查询员工名和对应的部门名

Select last_name,department_name from employees,departments where employees.’department_id’=department.’department_id’;

为表起别名

提高语句的简洁度，区分多个重名的字段；

注意:如果为表起了别名，用别名，不能使用原来的字段；

1、查询员工名、工种名、工种号

Select last_name,e.job_id,j.job_title from employees e,jobs j

Where e.’job_id’=j.’job_id’;

2、查询有奖金的员工名，部门名；

Select last_name,department_name commission_pct  from employees e,department d

Where e.’department_id’=d.’department_id’ and e.’commission_pct’ is not null;

可以加筛选

查询城市名中第二个字符为o的部门名和城市名

Select department_name city  from department d ,location l

Where d.’location_id’=l.’location_id’ and city like ‘_o%’;

分组

查询有奖金的每个部门的部门名和部门的领导编号和部门的最低工资

Select department_name,d.’manager_id’,min(salary) from departments d ,employees e where d.’department_id’=e.’department_id’ and commission _pct is not null group by department_name,d.’manager_id’;

可以加排序

查询每个工种的工种名和员工的个数，并按员工个数降序

Select job_title,count(*) from employees e,jobs j where e.’job_id’=j.’job_id’ group by job_title order by count(*) desc;

非等值连接：除去等值连接；

自连接：自己连接自己；

数据库199语法

内连接，inner；外连接 左外left【outer】右外right【outer】全外 full 【outer】，outer可以省略；交叉连接cross

语法：select 查询列表

from 表1 别名 【连接类型】

 join 表2 别名

on 连接条件

Where 筛选条件

Group by分组

Having 筛选条件

Order by 排序列表

内连接：语法：select 查询列表

from 表1 别名 【连接类型】

Inner join 表2 别名

on 连接条件

Where 筛选条件

Group by分组

Having 筛选条件

Order 排序

等值连接

查询员工名、部门名

Select last_name,department_name from employees e inner join departments d on e.’department_id’=d.’department_id’;

查询员工名、部门名、工种名并按部门名降序

Select last_name,department_name job_title from employees e inner join departments d on e.’department_id’=d.’department_id’;

Inner join jobs j on e.’job_id’=j.’job_id’

Order by department_name desc;

特点：添加排序、分组、筛选 ；inner可以省略； 筛选条件放在where后面，连接条件放在On后面，提高分离性，便于阅读；

Inner join连接和sq192语法中的等职连接效果是一样的，都是查询多表的交集。

非等值连接

查询员工的工资级别个数大于20，并按工资级别降序

Select count(*),grade_level from employees e join job_grade g on e.’salary’ between g.’lowest_sal’ and g.’highest_sal’ group by grade_level having count(*)>20 order by grade_level desc;

自连接

查询员工名字、上级名字

Select e.last_name,m.last_name from employees e join employees m  on e.’manager_id’=m.’employee_id’;

外连接

1、应用于：一个表中有，一个表中没有记录。

特点：外连接的查询结果为主表中的所有记录，如果表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示Null；

外连接查询结果=内连接结果+主表汇总有而从表中没有的记录

2、左外连接：left  join的是主表；

右外连接：right join的是主表；

3、左外和右外交换两个表的顺序，可以实现同样的效果

4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有；

例题：查询那个部门没有员工

Select d.*,e.employee_id from department d left outer join employees e on d.’department_id’=e.’department_id’ where e.employee_id’ is null;

交叉连接：

Select b.*,bo.* from beauty b cross join boys bo;