Mysql基础（8）连接查询

当查询列表中的字段来源于多个数据表，需要用到连接查询。

若在连接查询中没有添加有效的连接条件，则会出现笛卡尔积现象，查出结果无效，此时需要使用连接条件消除笛卡尔现象。

一般来讲，sql99版本是sql92版本的升级版。其中，在连接查询部分，两者区别较明显。

SQL92

sql92只提供了内连接功能。

内连接也叫连接、普通连接或者自然连接，它是从结果表中删除没有匹配的行，输出两个联接表中的匹配行，所以内连接可能会丢失信息。

为了区别sql99的外连接，因此叫做内连接，可分为以下三种情况

等值连接

等值连接是指在进行多表查询时，连接条件是多个表的某些字段相等,那么查询结果为多表的交集部分。其基本语法如下，其中from子句后的数据表名不分前后顺序。

select 查询列表
from 表1 别名1，表2 别名2
where 别名1.key = 别名2.key
and 【其他筛选条件】
group by 分组字段
having 分组后筛选
order by 排序字段

基本实例

# 实例1
SELECT NAME,boyName
FROM boys,beauty  
WHERE boys.id = beauty.boyfriend_id     //等值条件

若查询列表中字段存在于多个数据表中，且在多个数据表中的名称一样，此时需要使用表名限定该字段。

若不显式限定，mysql会提示该列有歧义（ambiguous）

# 区分重名字段job_id。
SELECT last_name, job_title,employees.`job_id`  // 用employees或jobs表都可限定job_id字段
FROM employees,jobs
WHERE employees.`job_id` = employees.`job_id`

多表连接时，一般会给数据表起别名，以提高sql的简洁度，同时也可以区分多个数据表中的重名字段。

SELECT last_name, job_title,e.`job_id`
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`

需要注意的是，若已经为数据表起别名，则查询列表中不能再使用原表名去限定字段，而只能使用别名限定，这是由于sql的执行顺序总是从from子句开始。

SELECT last_name, job_title,e.`job_id`  //此处只能使用employees的别名e或 jobs的别名j限定 job_id字段。
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`

在where子句中存在多个筛选条件，使用and关键字连接。

SELECT last_name, job_title,`job_id`
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`  AND e.`commission_pct` IS NOT NULL  //

等值查询搭配其他查询子句使用

# 搭配排序、分组
SELECT j.`job_title`, COUNT(*) c
FROM employees e, jobs j
WHERE e.`job_id` = j.job_id
GROUP BY e.`job_id` 
ORDER BY c DESC

非等值连接

只要不是等值连接，就可认为是非等值连接。除了连接条件不是等号外，其他特点同等值连接类似。

select e.salary, j.grade_level
from employees e, job_grade j
where e.salary between j.lowest_sal and j.highest_sal   //连接条件不再是等于判断

自连接

使用自连接可将自身的一个镜像当做另外一个表。

假设员工表中包含员工编号、上级领导编号等字段，且上级领导编号的值也是员工编号，那么查询员工名和上级领导就可以用到自连接

#查询员工和上级领导
SELECT e1.last_name 员工,e2.last_name 领导
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id

SQL99

sql99实现了连接条件和筛选条件的分离，提高了sql语句的可读性。其基本语法

select 查询列表
from 表1 as 别名 【连接类型】
join 表2 as 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【havin 分组筛选条件】
【order by 排序条件】

其中，连接类型有内连接（inner）、左外连接（left outer）、右外连接（right outer）、全外连接（full outer）、交叉连接（cross ）

内连接(inner)

内连接是将上面的基本语法中的连接类型设置为inner。在sql99中，内连接同样分为等值连接、非等值连接和自连接。其特点如下

• 可以添加分组、排序、筛选子句
• inner可以省略
• 相对于sql92，连接条件（on 子句）和筛选条件（where子句）分离
• 对于同样的查询场景，sql99的内连接和sql92的内连接只是语法不同，都是查询查询多表的交集，查询效果一样。

等值连接的基本语法为

select 查询列表
from 表1 别名1
inner join  表2 别名2 on 别名1.key1 = 别名2.key1
inner join  表3 别名3 on 别名2.key2 = 别名3.key2
【where子句】

#实例
SELECT last_name,department_name
FROM employees e 
INNER JOIN departments d
ON e.`department_id` = d.`department_id`

非等值连接和自连接同sql92的处理逻辑类似，只需调整语法即可。

外连接

外连接涉及一个主表概念，主表为from后第一个出现的数据表。

左外连接以左表为基础，根据连接条件（on）将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分,不满足的部分填充为null。其基本语法为

select 查询列表
from 表1 别名1
left outer join 表2 别名2
on 别名1.key = 别名2.key
【where等子句】

右外连接同左外类似。

全连接和交叉连接

全外连接不做限制，所有记录都显示。不足地方使用null填充，mysql不支持全外连接。

交叉连接即是实现笛卡尔积，使用cross join