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联合查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像