当查询列表中的字段来源于多个数据表,需要用到连接查询。
若在连接查询中没有添加有效的连接条件,则会出现笛卡尔积现象,查出结果无效,此时需要使用连接条件消除笛卡尔现象。
一般来讲,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