Mysql12——多表查询02_JOIN

已于 2023-09-04 17:50:20 修改
阅读量1.5k 收藏 3
点赞数 1
分类专栏： mysql 文章标签： mysql 数据库 sql
于 2023-09-04 15:03:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tonyshi1989/article/details/132669601
版权

1. SQL99 JOIN多表连接

在MySQL中，我们可以使用JOIN…ON语句进行表连接。JOIN是一个关键字，表示连接两个或多个表，而ON则用于指定连接上的条件。

JOIN有多种类型，包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN等，它们之间的区别在于匹配条件和返回结果的不同。其中，INNER JOIN是最常用的一种类型，它只返回两个表中匹配的记录。

1.1 基本语法

在 MySQL 中，JOIN ON 是一种用于在多表查询中指定连接条件的语法。

JOIN 用于将多个表连接起来，而 ON 关键字则用于指定连接条件。通过 JOIN ON，你可以在多个表之间建立连接，并指定连接的条件。

语法如下：

SELECT columns
FROM table1
JOIN table2 ON condition;

SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件

其中，table1 和 table2 是要连接的表，condition 是连接条件。连接条件通常是指定连接字段之间的相等关系，例如：table1.column = table2.column。根据具体的需求，你还可以使用其他比较操作符，如不等于（<>, !=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）等。

示例：

SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

该示例中，我们将 orders 表和 customers 表连接起来，连接条件是 orders.customer_id = customers.customer_id，即订单表中的 customer_id 字段等于客户表中的 customer_id 字段。

需要注意的是，使用 JOIN ON 进行连接时，连接的字段必须具有相同的数据类型或兼容的数据类型，以确保连接能够正常进行。

  • SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

  • 语法说明：

    • 可以使用 ON 子句指定额外的连接条件。
    • 这个连接条件是与其它条件分开的。
    • ON 子句使语句具有更高的易读性。
    • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

1.2 内连接(INNER JOIN)的实现

INNER JOIN 是 MySQL 中用于在多表查询中进行内连接操作的关键字。内连接（Inner Join）是最常用的连接类型之一，它根据连接条件从两个或多个表中筛选匹配的行，返回包含匹配结果的结果集。

  • 语法：
SELECT columns
FROM table1
INNER JOIN table2 ON condition;

SELECT 字段列表
FROM table1 INNER JOIN table1
ON 关联条件
WHERE 等其他子句;

其中，table1 和 table2 是要连接的表，condition 是连接条件。通常，连接条件是指定连接字段之间的相等关系，例如：table1.column = table2.column。根据具体的需求，你还可以使用其他比较操作符，如不等于（<>, !=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）等。

INNER JOIN 的执行方式是，将第一个表（左表）中的每一行与第二个表（右表）中满足连接条件的行进行匹配，然后将满足条件的行组合成结果集。

示例：

SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

该示例中，我们将 orders 表和 customers 表进行内连接，连接条件是 orders.customer_id = customers.customer_id，即订单表中的 customer_id 字段等于客户表中的 customer_id 字段。最终的结果集将包含满足连接条件的行。

需要注意的是，INNER JOIN 只返回匹配的行，未匹配的行将被忽略。如果两个表之间没有匹配的行，则结果集将为空。INNER JOIN 通常用于根据特定的关联关系从多个表中获取相关的数据。

案例1 员工表、部门表两表联查：

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

在这里插入图片描述

题目2 员工表、部门表、位置表三表联查：

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;

在这里插入图片描述

1.3 外连接(OUTER JOIN)的实现

OUTER JOIN 是 MySQL 中用于在多表查询中进行外连接操作的关键字。外连接（Outer Join）是一种连接类型，它可以返回连接表中的所有行，包括未匹配的行。

在 MySQL 中，有三种类型的外连接：LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN。

1.3.1 左外连接(LEFT OUTER JOIN)

LEFT OUTER JOIN：左外连接返回左表中的所有行，以及与右表匹配的行。如果右表中没有匹配的行，则返回 NULL 值。

  • 语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

在这里插入图片描述

1.3.2 右外连接(RIGHT OUTER JOIN)

RIGHT OUTER JOIN：右外连接返回右表中的所有行，以及与左表匹配的行。如果左表中没有匹配的行，则返回 NULL 值。

  • 语法：
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

在这里插入图片描述

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

2. UNION的使用

合并查询结果

使用 UNION 很简单，所要做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION。

举个例子，假如需要 Illinois、Indiana 和 Michigan 等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的 Fun4All。

当然可以利用 WHERE 子句来完成此工作，不过这次我们使用 UNION。

如上所述，创建 UNION 涉及编写多条 SELECT 语句。首先来看单条语句：

语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符

在这里插入图片描述

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

在这里插入图片描述

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例1 ：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

举例2：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

3. 7种SQL JOINS的实现

在这里插入图片描述

3.1 代码实现

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#左下图：满外连接
# 左中图 + 右上图  A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL  #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右下图
#左中图 + 右中图  A ∪B- A∩B 或者 (A -  A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

3.2 语法格式小结

  • 左中图
#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
  • 右中图
#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
  • 左下图
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句

union 

select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
  • 右下图
#实现A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

4 SQL99语法新特性

4.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

ATURAL JOIN 是 MySQL 中一种用于进行自然连接（Natural Join）操作的关键字。自然连接是一种在没有明确指定连接条件的情况下，根据两个表之间的相同列名进行连接的操作。

使用 NATURAL JOIN 时，MySQL 将根据两个表中具有相同列名的列进行连接，列名的匹配是基于列名的文本匹配，而不是严格的数据类型匹配。如果两个表中存在多个相同列名的列，则所有匹配的列将用于连接。

语法如下：

SELECT columns
FROM table1
NATURAL JOIN table2;

其中，table1 和 table2 是要连接的表。

示例：

SELECT *
FROM orders
NATURAL JOIN customers;

该示例中，我们将 orders 表和 customers 表进行自然连接。MySQL 将根据这两个表中具有相同列名的列进行连接，列名的匹配是基于列名的文本匹配。自然连接将返回满足匹配条件的行。

需要注意的是，自然连接基于列名的匹配，可能会导致不可预期的结果。如果两个表中的列名不是唯一的，或者列名在语义上不匹配，那么自然连接可能会引入意外的行。因此，在实际使用时，建议明确指定连接条件，而不是依赖自然连接。

案例演示：

在SQL92标准中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

4.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。

USING 连接是 MySQL 中一种用于进行连接操作的关键字。它可以在连接多个表时，指定连接条件中要使用的列。

使用 USING 连接时，MySQL 将根据指定的列名进行连接，而不是根据所有列进行连接。这样可以简化连接条件的书写，并且避免重复列在结果集中出现。

语法如下：

SELECT columns
FROM table1
JOIN table2 USING (column);

其中，table1 和 table2 是要连接的表，column 是要使用的连接列名。

案例演示：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

1和table2 是要连接的表，column` 是要使用的连接列名。

案例演示：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
程序喵猴
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL多表查询：SQL JOIN连接查询各种用法总结
君一席
08-04 9035
谈及 SQL 里的各种 JOIN 之间的区别时，被广为引用的是 CodeProject 上C.L. Moffatt的文章, 本文也有所参考，感兴趣的小伙伴可以去看一下。
SQL server 多表连接二(采用四种方法)
qq_54000767的博客
04-22 3499
SQL server 多表连接二(采用四种方法)
MySQL多表查询操作的一写关键字
最新发布
boluomi的博客
04-22 2132
说明：这样是INNER JOIN，只展示有订单的顾客（及其订单），也就是两张表的交集（没订单的顾客不会显示），但注意这里因为一个顾客可能有多个订单，所以INNER JOIN以后顾客信息其实是是广播了的，即一条顾客信息被多条订单记录共用，当然 这叫。如下面的例子，员工的上级也是员工，所以也在员工表里，想得到员工和他的上级信息的合并表，就要员工表自己和自己合并，如果上级是老板没有上级了也需要显示出来，作为合并条件（join condition）的字段在两个表中有相同的字段名时，可用 USING (……
mysql 使用join进行多表关联查询
sinat_16493273的博客
02-04 1717
在常规业务开发(非高并发)进行表join操作是非常常见的，在表连接时候尽量使用小表驱动大表，这里的小表是指条件过滤后参加join条件的数据。它只返回两个表中匹配的行，即两个表中具有相同值的列的行。MRR处理过程是在根据索引获取到主键后不是立即进行回表查询，而是先将主键值放入到一个read_rnd_buffer中，然后对其进行排序，最后再有序的去聚簇索引检索行数据。2、将join buffer中的关联列值，批量的发送给引擎在被驱动表上通过MRR以最优的方式进行索引树搜索匹配，获取到对应的主键（rowId）。
多表查询与7种JOINS的实现
timerring的博客
01-21 1895
接下来就要考虑如上图的两个部分，交集是有部门且有员工的部分，然后左外连接是各个部门号，去除中间的交集就是部门号但是没员工的部分。你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。一般来说 SQL92 的形式更简单，但是写的 SQL 语句会比较长，可读性较差。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。
Python 写字典
Kp0fS的草稿纸
09-21 195
d[k]=v将值v关联到键k上；
MySQL多表链接查询核心优化
12-16
在一般的项目开发中，对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时（例如百万级数据量），我们就需要从数据库的各个方面来进行优化，本文就先从多表查询开始。其他优化操作，后续另外更新，敬请...
day_04MySQL多表&事务_docx1
08-04
在MySQL中，多表查询是数据库操作中非常重要的一个环节，特别是在处理复杂的数据关系时。本教程主要涵盖了多表查询的几个关键概念，包括内连接、外连接以及子查询，同时也涉及到了事务管理和用户权限控制。 首先，...
基于MySql和qt的学生管理系统
04-12
接下来，我们将深入探讨系统中涉及的关键技术——MySQL的视图、触发器、索引和多表查询，以及Qt库中的QSqlTableModel和QTableView模块。 首先，MySQL是广泛使用的开源关系型数据库管理系统，其强大的数据存储和处理...
在MySQL中使用JOIN语句进行连接操作的详细教程
09-10
在MySQL中，JOIN语句是用于合并来自两个或更多个表的数据的关键工具，这对于处理复杂的数据库查询至关重要。在本文中，我们将深入探讨如何在MySQL中使用JOIN语句进行连接操作，这是一项基础但非常重要的技能，对于...
【MySQL作业】外连接查询——美和易思外连接查询应用习题
01-21
由于需要获取所有客户的基本信息，如果采用左连接加以实现，必须从左至右依次连接客户表和订单表，此时客户表将作为左表。 select cName 姓名 , ordersID 订单 ID, ordersDate 下单日期 from customer c left join ...
mysql七种join
小白
02-03 538
数据准备 CREATE TABLE `t_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_emp` ( `id` INT(11) NOT NU
SQL:JOIN ON
weixin_33912638的博客
01-21 4935
JOIN连接组合两个表中的字段记录，包括三种： INNER JOIN运算式：连接组合两个表中的字段记录。 LEFT JOIN运算式：连接组合两个表中的字段记录，并将包含了LEFT JOIN左边表中的全部记录。 RIGHT JOIN运算式：连接组合两个表中的字段记录，并将包含了RIGHT JOIN右边表中的全部记录。 INNER JOIN设定两个表相关连的运算式，以连接组合两个表中的字段记...
多表查询
Stars永恒的博客
01-21 221
微信公众号：菜鸟永恒 第1章 多表关系实战 1.1 实战1：省和市  方案1：多张表，一对多    方案2：一张表，自关联一对多   1.2 实战2：用户和角色 (比如演员和扮演人物)    多对多关系 1.3 实战3：角色和权限 (比如公司职位和开除等权限)    多对多关系 1.4 实战4：客户和联系人(可选)    一对多：一个客户服...
SQL中join操作后面的on与where的区别
热门推荐
ddda的CSDN
07-17 4万+
join关键字的作用是将多个表按一定的条件联合起来，从而可以实现从多个表中获取数据 在join后面可以接on条件和where条件，在这里我主要就是说这两者之间的差别 建立两张简单的用来测试的表并添加数据，如下所示，一张表名为id_name，另一张表名为id_age 首先看看不添加条件——t2.age ='22'时的结果 SELECT * from id_name t1 LEFT ...
mysql left join 左连接查询关联n多张表
FFA(Frighting for Ambition!)
07-19 3万+
最近用mysql 多表关联查询比较多，特此总结一下left join用法，拓展下left join将多表关联。left join 左连接即以左表为基准，显示坐标所有的行，右表与左表关联的数据会显示，不关联的则不显示。关键字为left join on。 **基本用法如下： select table a left join table b on a.id = b.ta_id**注意：1⃣️其中on后
最常问的MySQL面试题集合
IT技术分享社区
10-12 1463
除了基础题部分，本文还收集整理的MySQL面试题还包括如下知识点或题型：MySQL高性能索引SQL语句MySQL查询优化MySQL高扩展高可用MySQL安全性问题1：char、varch...
分析比较多表查询中的IN与JOIN
黄复贵的博客
07-17 3万+
IN 是子查询的关键字，JOIN 是连接的关键字，项目开发中经常会使用到多表查询，而子查询与连接正是实现多表查询的重要途径。那两者的实质有什么区别？IN与JOIN谁更好？下面就来分析与比较。现在有test1与test2两张表，都没有任何像主键，外键那样的约束，且只有一个字段。两张表是非相关的。现在使用IN关键字实现子查询，test2作为子查询表（外部表）：查看执行计划：使用JOIN关键字实现连接，同
mysql查询第三个字母是a的员工_mysql基础(三）——多表查询
05-28
好的，根据您的要求，可以使用以下的 SQL 语句来查询第三个字母是 A 的员工： ```sql SELECT * FROM employees WHERE SUBSTR(last_name, 3, 1) = 'a'; ``` 同时，您提到了 "mysql基础(三）——多表查询"，如果您需要进行多表查询，可以使用 JOIN 来实现。例如，假设您的员工表和部门表分别为 employees 和 departments，他们之间有关联字段 department_id，您可以使用以下的 SQL 语句来查询员工所在的部门名称： ```sql SELECT e.*, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE SUBSTR(e.last_name, 3, 1) = 'a'; ``` 这个查询会返回符合条件的员工记录和他们所在的部门名称。希望这些对您有所帮助。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值