MySQL中多表连接查询总结与实践-阿里云开发者社区

连接查询：将多张表（>=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';

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

【1】交叉连接cross join

【2】内连接[inner]join

【3】外连接outer join

① left [outer] join

② right [outer] join

【4】自然连接natural join

【5】全连接full[outer] join

【6】left join中的 on where

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

【8】union联合查询

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

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

【1】交叉连接cross join

【2】内连接[inner]join

【3】外连接outer join

① left [outer] join

② right [outer] join

【4】自然连接natural join

【5】全连接full[outer] join

【6】left join中的 on where

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

【8】union联合查询

热门文章

最新文章

相关课程

相关电子书

推荐镜像