MySQL中多表连接查询总结与实践

流烟默

已于 2022-09-05 16:31:21 修改

阅读量8k

点赞数 3

分类专栏： MySQL 文章标签： mysql 连接查询

于 2017-06-06 10:35:08 首次发布

本文链接：https://blog.csdn.net/J080624/article/details/72875627

版权

MySQL 专栏收录该内容

43 篇文章 8 订阅

订阅专栏

关联博文：
MySQL中Union联合查询使用实践总结

连接查询：将多张表（>=2）进行记录的连接（按照某个指定的条件进行数据拼接）。

连接查询的意义：在用户查看数据的时候，数据往往来源于多张表。

连接查询的真谛：根据从左表拿出一条记录到右表匹配根据条件进行过滤。

连接查询的分类

SQL 中将连接查询分为四类：内连接，外连接，自然连接和交叉连接。其中外连接又分为左外连接和右外连接以及全外连接。

区分重复的列名

使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。
如果使用了表别名，则在select语句中需要使用表别名代替表名
表别名最多支持32个字符长度，但建议越少越好

表的别名

使用别名可以简化查询。
使用表名前缀可以提高执行效率。

【1】交叉连接cross join

从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配。匹配一定保留(没有条件匹配)，而连接本身字段就会增加(保留)，最终形成的结果叫做：笛卡尔积。

笛卡尔积没有意义，交叉连接只是保证连接这种结构的完整性。

语法如下：

select column from table1 cross join table2 
on table1.column=table2.column

示例如下：

#被称为显示交叉连接
select * from c_user cross join p_user;

#等价于如下,又被称为隐式交叉连接
select * from c_user,p_user;

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

省略连接条件
连接条件无效
所有表中的所有行互相连接

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

# 等值连接
SELECTtable1.column, table2.column
FROM table1, table2
WHERE table1.column1 =table2.column2;

在WHERE 子句中写入连接条件，在表中有相同列时，在列名之前加上表名前缀。

【2】内连接[inner]join

从左表中取出每一条记录，去右表中与所有的记录进行匹配。匹配必须是某个条件在左表中与右表中都相同最终才会保留结果，否则不保留。

要求连接两边记录都存在，null将会被过滤掉 ;
内连接可以没有on条件，这时候系统会保留所有结果(笛卡尔积) ;
内连接还可以使用where代替on关键字，但是没有on效率高。

筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读。inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集。

语法如下：

左表 [inner] join 右表 on 左表.字段 = 右表.字段;

inner可省略， on表示连接条件，条件字段就是代表相同的业务含义。on 可以被where替换

可以尝试使用表别名和字段别名简洁查询结果。

使用ON 子句创建连接

自然连接中是以具有相同名字的列为连接条件的。
可以使用ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。

使用ON 子句创建多表连接

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

等值连接

SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DES

非等值连接

SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades 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`
WHERE e.`last_name` LIKE '%k%';

【3】外连接outer join

以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接。不管能不能匹配上条件，最终都会保留：能匹配，正确保留；不能匹配，其他表的字段都置空NULL。

外连接分为两种：

left join(left outer join) : 左外连接(左连接)，以左表为主表；
right join(right outer join) : 右外连接(右连接)，以右表为主表。

语法如下：

左表 left/right join 右表 on 左表.字段 = 右表.字段;
on表示连接条件，条件字段就是代表相同的业务含义。

① left [outer] join

select c.id ,c.name,p.age from c_user c LEFT OUTER JOIN p_user p on c.id = p.id;

这里写图片描述

以左表为主，左表所有字段都会列出，右表无的将会置为null。最终的记录数至少不少于左表的记录数。

② right [outer] join

select c.id ,c.name,p.age from c_user c
right OUTER JOIN p_user p on c.id = p.id;

这里写图片描述

以右表为主，右表所有字段都会列出，左表无的将会置为null。最终的记录数至少不少于右表的记录数。

在这里插入图片描述

【4】自然连接natural join

自然连接，就是自动匹配条件。系统以字段名字作为匹配模式(同名字段就作为条件，如果有多个同名字段，都作为条件)。

SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列，不允许使用ON语句。

自然连接可分为自然内连接和自然外连接。

自然内连接：左表 natural join 右表。

select * from s_user natural join p_user ;

自然外连接：左表 natural left/right join 右表。

select * from s_user natural left join p_user ;

其实，内连接和外连接都可以模拟自然连接。交叉连接不可以使用自然连接。

左表 left/right/inner join 右表 using(字段名),使用同名字段作为条件，自动合并条件。

select * from p_user left join p_user_2 USING(name,age) ;

这里写图片描述
等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。

等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。

【5】全连接full[outer] join

MySQL不支持全连接，Oracle支持。

语法如下：

左表 full[outer] join 右表 on 左表.字段 = 右表.字段

示例如下：

select c.id ,c.name,p.age from c_user c full  JOIN p_user p on c.id = p.id;

MySQL中可以使用下面语句进行等价：

select c.id ,c.name,p.age from c_user c right OUTER JOIN p_user p on c.id = p.id

union 

select c.id ,c.name,p.age from c_user c left OUTER JOIN p_user p on c.id = p.id;

在这里插入图片描述

【6】left join中的 on where

如果需要左表展示所有数据，那么条件放在on后面。如果条件放在了 where后面，那么和inner join 效果一样。

不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

在使用left join时，on和where条件的区别如下：

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

示例一展示所有数据：

<select id="getUserRowerList" parameterType="hashmap" resultType="hashmap">
  	SELECT user_id,login_code,real_name,nick_name, 
  	DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') as create_time,
  	GROUP_CONCAT(power_code)as 'power_code'from 
	(SELECT p.id as power_id,p.power_code,p.power_type,su.id as user_id,su.login_code,su.nick_name,su.real_name,
	 DATE_FORMAT(su.create_time ,'%Y-%m-%d %H:%i:%S') as create_time 
	FROM sys_user su 
	left join sys_user_role ru on su.id = ru.user_id  and ru.if_del = 0 
	left join sys_role_power rp on rp.role_id=ru.role_id and rp.if_del=0
	left join  sys_power p on  p.id=rp.power_id AND p.if_del=0 
    union all
    SELECT p.id,p.power_code,p.power_type,su.id as user_id,su.login_code,su.nick_name,su.real_name,
    DATE_FORMAT(su.create_time ,'%Y-%m-%d %H:%i:%S') as create_time 
	FROM sys_user su 
	left join sys_user_power pu on pu.user_id = su.id  and pu.if_del = 0 
	left join  sys_power p on p.id=pu.power_id and p.if_del = 0
    )t where 1=1
    <if test="loginCode != null and loginCode != ''">
    	and login_code like concat('%', #{loginCode},'%')
    </if>
    <if test="nickName != null and nickName != ''">
    	and nick_name like concat('%', #{nickName},'%')
    </if>
     GROUP BY user_id
    <if test="sortname!= null and sortname!='' and sortorder!= null and sortorder!=''">
           order by ${sortname} ${sortorder}
     </if>
	limit ${(page-1)*pagesize},${pagesize}
</select>

这里写图片描述

示例二展示有效数据：

SELECT
	user_id,
	login_code,
	real_name,
	nick_name,
	DATE_FORMAT(
		create_time,
		'%Y-%m-%d %H:%i:%S'
	) AS create_time,
	GROUP_CONCAT(power_code) AS 'power_code'
FROM
	(
		SELECT
			p.id AS power_id,
			p.power_code,
			p.power_type,
			su.id AS user_id,
			su.login_code,
			su.nick_name,
			su.real_name,
			DATE_FORMAT(
				su.create_time,
				'%Y-%m-%d %H:%i:%S'
			) AS create_time
		FROM
			sys_user su
		LEFT JOIN sys_user_role ru ON su.id = ru.user_id
		LEFT JOIN sys_role_power rp ON rp.role_id = ru.role_id
		LEFT JOIN sys_power p ON p.id = rp.power_id
		WHERE
			ru.if_del = 0
		AND rp.if_del = 0
		AND p.if_del = 0
		UNION ALL
			SELECT
				p.id,
				p.power_code,
				p.power_type,
				su.id AS user_id,
				su.login_code,
				su.nick_name,
				su.real_name,
				DATE_FORMAT(
					su.create_time,
					'%Y-%m-%d %H:%i:%S'
				) AS create_time
			FROM
				sys_user su
			LEFT JOIN sys_user_power pu ON pu.user_id = su.id
			LEFT JOIN sys_power p ON p.id = pu.power_id
			WHERE
				pu.if_del = 0
			AND p.if_del = 0
	) t
WHERE
	1 = 1
GROUP BY
	user_id

这里写图片描述

【7】on、where和having条件字句

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。

有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。

根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。

在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。

如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上面写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。

在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

【8】union联合查询

union 联合查询，将多条查询语句的结果合并成一个结果。

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

要求多条查询语句的查询列数是一致的！
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重，如果使用union all 可以包含重复项

案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

流烟默

关注

3
点赞
踩
23

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL中多表连接查询总结与实践

连接查询：将多张表（>=2）进行记录的连接（按照某个指定的条件进行数据拼接）。连接查询的意义：在用户查看数据的时候，数据往往来源于多张表。连接查询的真谛：根据从左表拿出一条记录到右表匹配根据条件进行过滤。多表如是。【1】连接查询的分类SQL 中将连接查询分为四类：内连接，外连接，自然连接和交叉连接。其中外连接又分为左外连接和右外连接。【2】交叉连接交叉连接：cross join。从一张表中循
复制链接

扫一扫