AI 助理
备案控制台
开发者社区 数据库 文章 正文

SQL 连接(JOIN)专题

2022-05-22 146
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： SQL 最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用 SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。另外聚集函数也可以在联结中进行使用。SQL 连接(JOIN) 用于把来自两个或多个表的行结合起来。下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：INNER JOIN：如果表中有至少一个匹配，则返回行LEFT JOIN：即使右表中没有匹配，

SQL 最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用


SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。另外聚集函数也可以在联结中进行使用。


SQL 连接(JOIN) 用于把来自两个或多个表的行结合起来。


下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。


image.png


在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：


INNER JOIN：如果表中有至少一个匹配，则返回行


LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行


RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行


FULL JOIN：只要其中一个表中存在匹配，则返回行


前提准备


导入语句

-- --------------------------------------------------------
-- 主机:                           10.1.212.135
-- 服务器版本:                        10.6.5-MariaDB - mariadb.org binary distribution
-- 服务器操作系统:                      Win64
-- HeidiSQL 版本:                  11.3.0.6295
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- 导出  表 test.access_log 结构
CREATE TABLE IF NOT EXISTS `access_log` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL DEFAULT 0 COMMENT '网站id',
  `count` int(11) NOT NULL DEFAULT 0 COMMENT '访问次数',
  `date` date NOT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3;
-- 正在导出表  test.access_log 的数据：~9 rows (大约)
DELETE FROM `access_log`;
/*!40000 ALTER TABLE `access_log` DISABLE KEYS */;
INSERT INTO `access_log` (`aid`, `site_id`, `count`, `date`) VALUES
    (1, 1, 45, '2016-05-10'),
    (2, 3, 100, '2016-05-13'),
    (3, 1, 230, '2016-05-14'),
    (4, 2, 10, '2016-05-14'),
    (5, 5, 205, '2016-05-14'),
    (6, 4, 13, '2016-05-15'),
    (7, 3, 220, '2016-05-15'),
    (8, 5, 545, '2016-05-16'),
    (9, 3, 201, '2016-05-17');
/*!40000 ALTER TABLE `access_log` ENABLE KEYS */;
-- 导出  表 test.websites 结构
CREATE TABLE IF NOT EXISTS `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT 0 COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3;
-- 正在导出表  test.websites 的数据：~7 rows (大约)
DELETE FROM `websites`;
/*!40000 ALTER TABLE `websites` DISABLE KEYS */;
INSERT INTO `websites` (`id`, `name`, `url`, `alexa`, `country`) VALUES
    (1, 'Google', 'https://www.google.cm/', 1, 'USA'),
    (2, '淘宝', 'https://www.taobao.com/', 13, 'CN'),
    (3, '菜鸟学习网', 'http://www.runoob.com/', 5000, 'CN'),
    (4, '微博', 'http://weibo.com/', 20, 'CN'),
    (5, 'Facebook', 'https://www.facebook.com/', 3, 'USA'),
    (6, '百度', 'https://www.baidu.com/', 4, 'CN'),
    (7, 'stackoverflow', 'http://stackoverflow.com/', 0, 'IND');
/*!40000 ALTER TABLE `websites` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;


预览数据

MariaDB [test]> SELECT * FROM websites;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟学习网    | http://www.runoob.com/    |  5000 | CN      |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|  6 | 百度          | https://www.baidu.com/    |     4 | CN      |
|  7 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
+----+---------------+---------------------------+-------+---------+
7 rows in set (0.003 sec)
MariaDB [test]> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.002 sec)


叉联结


笛卡儿积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。


有时我们会听到返回称为叉联结（cross join）的笛卡儿积的联结类型。


SQL INNER JOIN 内联结

INNER JOIN 关键字在表中存在匹配时返回行。


语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;


可省略 INNER 关键字：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;


注释：INNER JOIN 与 JOIN 是相同的。


示例：返回所有网站的访问记录

MariaDB [test]> SELECT Websites.name, access_log.count, access_log.date
    -> FROM Websites
    -> INNER JOIN access_log
    -> ON Websites.id=access_log.site_id;
+------------+-------+------------+
| name       | count | date       |
+------------+-------+------------+
| Google     |    45 | 2016-05-10 |
| 菜鸟学习网 |   100 | 2016-05-13 |
| Google     |   230 | 2016-05-14 |
| 淘宝       |    10 | 2016-05-14 |
| Facebook   |   205 | 2016-05-14 |
| 微博       |    13 | 2016-05-15 |
| 菜鸟学习网 |   220 | 2016-05-15 |
| Facebook   |   545 | 2016-05-16 |
| 菜鸟学习网 |   201 | 2016-05-17 |
+------------+-------+------------+
9 rows in set (0.002 sec)


外联结


SQL LEFT JOIN 语法


LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。


SQL LEFT JOIN 语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;


或：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;


下面的 SQL 语句将返回所有网站及他们的访问量（如果有的话）。

MariaDB [test]> SELECT Websites.name, access_log.count, access_log.date
    -> FROM Websites
    -> LEFT JOIN access_log
    -> ON Websites.id=access_log.site_id;
+---------------+-------+------------+
| name          | count | date       |
+---------------+-------+------------+
| Google        |    45 | 2016-05-10 |
| 菜鸟学习网    |   100 | 2016-05-13 |
| Google        |   230 | 2016-05-14 |
| 淘宝          |    10 | 2016-05-14 |
| Facebook      |   205 | 2016-05-14 |
| 微博          |    13 | 2016-05-15 |
| 菜鸟学习网    |   220 | 2016-05-15 |
| Facebook      |   545 | 2016-05-16 |
| 菜鸟学习网    |   201 | 2016-05-17 |
| 百度          |  NULL | NULL       |
| stackoverflow |  NULL | NULL       |
+---------------+-------+------------+
11 rows in set (0.002 sec)


注释：LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配。


SQL RIGHT JOIN 语法


RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;


或：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;


注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

MariaDB [test]> SELECT Websites.name, access_log.count, access_log.date
    -> FROM  access_log
    -> RIGHT JOIN  websites
    -> ON Websites.id=access_log.site_id;
+---------------+-------+------------+
| name          | count | date       |
+---------------+-------+------------+
| Google        |    45 | 2016-05-10 |
| 菜鸟学习网    |   100 | 2016-05-13 |
| Google        |   230 | 2016-05-14 |
| 淘宝          |    10 | 2016-05-14 |
| Facebook      |   205 | 2016-05-14 |
| 微博          |    13 | 2016-05-15 |
| 菜鸟学习网    |   220 | 2016-05-15 |
| Facebook      |   545 | 2016-05-16 |
| 菜鸟学习网    |   201 | 2016-05-17 |
| 百度          |  NULL | NULL       |
| stackoverflow |  NULL | NULL       |
+---------------+-------+------------+
11 rows in set (0.002 sec)


由此我们还能得出结论，表 A 左外连接表 B 等价于 表 B 右外连接表 A。


事实上左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。


SQL FULL OUTER JOIN 全外连接


FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.


FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。


注意：MariaDB、MySQL和SQLite 不支持 FULL OUTER JOIN 语法。你可以在 SQL Server 测试以下实例。


SQL FULL OUTER JOIN 语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;


多个连接的写法总结


INNER JOIN 连接两个数据表的用法：

SELECT * FROM 表1 
INNER JOIN 表2 ON 表1.字段号=表2.字段号


INNER JOIN 连接三个数据表的用法：

SELECT * FROM 表1
INNER JOIN 表2 ON 表1.字段号=表2.字段号
INNER JOIN 表3 ON 表1.字段号=表3.字段号


自联结


自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。


自然联结

标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。


自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT＊），而对其他表的列使用明确的子集来完成。


事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。


联结的性能考虑

注意：性能考虑 DBMS 在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意：


  • 不要联结不必要的表。联结的表越多，性能下降越厉害。


  • 应该总是提供联结条件，否则会得出笛卡儿积。

SELECT cust_name, cust_contact
FROM customers AS c, orders, orderitems AS oi
where c.cust_id = orders.cust_id and orders.order_num = oi.order_num and oi.prod_id = 'RGAN01';


子查询并不总是执行复杂 SELECT 操作的最有效方法，以下语句也可使用联结的相同查询。


多做实验 正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

SELECT cust_name, cust_contact FROM customers
where cust_id in (
   select cust_id from orders
   where order_num in (
          select order_num from orderitems where prod_id = 'RGAN01')
);


例题


提问：查找值等于或大于1000的所有订单号和订单数量至少达到这个数的顾客名称。

解答：可以使用使用简单的等联结或ANSI的INNER JOIN语法。

-- Equijoin syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;
-- ANSI INNER JOIN syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers 
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;


参考

SQL UNION 操作符 | 菜鸟教程


https://www.runoob.com/sql/sql-union.html


SQL必知必会（第5版）-本·福达-微信读书


https://weread.qq.com/web/reader/f7632a30720befadf7636bb




文章标签：
SQL
关系型数据库
索引
数据库管理
MySQL
数据库
关键词：
SQL JOIN
SQL连接
SQL连接join
acc8226
目录
相关文章
hju6meadphitw
|
23天前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库：详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步，ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中，我们经常需要与数据库进行交互，特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库，并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前，请确保您
hju6meadphitw
111 3
算精通
|
4天前
|
SQL
SQL JOIN
【11月更文挑战第06天】
算精通
16 4
mzlogin
|
9天前
|
SQL 关系型数据库 MySQL
图解 SQL 里的各种 JOIN
用文氏图表示 SQL 里的各种 JOIN，一下子就理解了。
mzlogin
23 2
1100237741946300
|
17天前
|
SQL 数据库 索引
SQL语句实现投影连接：方法与技巧详解
在SQL数据库查询中，投影和连接是两个核心概念
1100237741946300
81 2
东方睿赢
|
25天前
|
SQL Java 数据库连接
如何使用`DriverManager.getConnection()`连接数据库，并利用`PreparedStatement`执行参数化查询，有效防止SQL注入。
【10月更文挑战第6天】在代码与逻辑交织的世界中，我从一名数据库新手出发，通过不断探索与实践，最终成为熟练掌握JDBC的开发者。这段旅程充满挑战与惊喜，从建立数据库连接到执行SQL语句，再到理解事务管理和批处理等高级功能，每一步都让我对JDBC有了更深的认识。示例代码展示了如何使用`DriverManager.getConnection()`连接数据库，并利用`PreparedStatement`执行参数化查询，有效防止SQL注入。
东方睿赢
68 5
1100237741946300
|
25天前
|
SQL 数据库 决策智能
SQL语句实现投影连接详解
在SQL中，投影（Projection）和连接（Join）是数据查询和处理中非常重要的两个操作
1100237741946300
89 5
yeye向阳而生
|
26天前
|
SQL 存储 数据可视化
SQL 数据库大揭秘：连接数字世界的魔法桥梁
在数字化时代，数据如繁星般璀璨，而 SQL 数据库则像强大的引力场，有序汇聚、整理和分析这些数据。SQL 数据库是一个巨大的数字宝库，装满各行各业的“宝藏”。本文将带你探索 SQL 数据库在电商、金融、医疗和教育等领域的应用。例如，在电商中，它能精准推荐商品；在金融中，它是安全卫士，防范欺诈；在医疗中，它是健康管家，管理病历；在教育中，则是智慧导师，个性化教学。此外，还将介绍如何利用板栗看板等工具实现数据可视化，提升决策效率。
yeye向阳而生
34 4
1100237741946300
|
17天前
|
SQL 数据库 索引
SQL语句实现投影连接：技巧与方法详解
在SQL数据库操作中，投影连接（Projection Join）是一种常见的数据查询技术，它结合了投影（Projection）和连接（Join）两种操作
1100237741946300
40 0
1100237741946300
|
23天前
|
SQL 存储 监控
串口调试助手连接SQL数据库的技巧与方法
串口调试助手是电子工程师和软件开发人员常用的工具，它能够帮助用户进行串口通信的调试和数据分析
1100237741946300
19 0
1100237741946300
|
23天前
|
SQL 数据库 索引
内连接（INNER JOIN）在SQL中的简单应用与技巧
在SQL查询中，内连接（INNER JOIN）是一种基本且常用的连接类型，用于从两个或多个表中检索匹配的记录
1100237741946300
94 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    SQL Server 2008 R2 死锁监控
  • 3
    SQL Server 2005合并列成字符串 2008-11-07
  • 4
    sql script: select database select all table
  • 5
    使用T-SQL脚本在SQL Server创建角色，并给角色赋予相应权限
  • 6
    基于SQL Server的打车系统数据库分析与设计
  • 7
    【译】SQL Server索引进阶第十篇：索引的内部结构
  • 8
    SQL Server-聚焦事务对本地变量、临时表、表变量影响以及日志文件存满时如何收缩（三十一）
  • 9
    SQL疑难杂症【3】链接服务器提示"无法启动分布式事物"
  • 10
    SQL Server 事务日志传输
  • 1
    SQL常见面试题总结2
    97
  • 2
    mysql一条sql查询出多个统计结果
    58
  • 3
    启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
    59
  • 4
    【MySQL】慢SQL分析流程
    91
  • 5
    java.sql.SQLException: No operations allowed after statement closed.
    224
  • 6
    一文搞懂SQL优化——如何高效添加数据
    426
  • 7
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
    111
  • 8
    Cause: java.sql.SQLIntegrityConstraintViolationException: Column ‘id‘ in field list is ambiguous
    95
  • 9
    MySQL SQL语句面试准备
    37
  • 10
    Auditing SQL Statements, Privileges, and Other General Activities
    35

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • SQL的增删改查及函数应用
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群
  • 使用DAS实现数据库自动SQL优化
  • 使用DAS实现数据库自动SQL限流
  • 一小时快速掌握 SQL 语法
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月