MySQL的几种连接 join/inner join/cross join/逗号/left join/right join/natural join

最新推荐文章于 2024-08-02 10:32:57 发布
最新推荐文章于 2024-08-02 10:32:57 发布
阅读量560 收藏
点赞数
分类专栏： MySQL

转载出自：https://www.cnblogs.com/liulaoshi/p/6219540.html

连接：A xjoin B（主表 操作 关联表）

 select过程：from->where->group by->having->order by->limit

在不使用on语法时，join、inner join、逗号、cross join结果相同，都是取2个表的笛卡尔积。逗号与其他操作符优先级不同，所以有可能产生语法错误，尽量减少用逗号

 

join、inner join、cross join支持on和using语法，逗号不支持on和using语法

on语法：筛选连接后的结果，两表的对应列值相同才在结果集中，可以通过and连接多个列值的匹配要求，列名可以不同

     select * from tb_test1 inner join tb_student on tb_test1.id=tb_student.id;

using语法：筛选连接后的结果，两表的对应列值相同才在结果集中，括号内用多个列名要求用逗号连接，列名必须相同

     select * from tb_test1 cross join tb_student using(id);

 

仅MySQL中！：（MySQL中没有全外连接，用UNION操作实现吧）

join=inner join=cross join（笛卡尔积）

left join=left outer join

right join=right outer join

 

sample数据：

 

 select * from tb_test1;

+----+-----------+--------+-------+

| id | name      | gender | score |

+----+-----------+--------+-------+

|  1 | 李毅      | 男     | 95.30 |

|  2 | 二丫      | 女     | 95.30 |

|  3 | 张三      | 女     | 95.30 |

|  4 | 李四      | 女     | 95.30 |

|  7 | 胡鲁瓦    | 男     | 95.30 |

|  9 | 后羿      | 男     | 95.30 |

+----+-----------+--------+-------+

 

 select * from tb_student;

+----+--------+--------+------------+

| id | name   | gender | birthday   |

+----+--------+--------+------------+

|  1 | 李毅   | 男     | 1988-03-20 |

|  2 | kevin  | 男     | 1987-08-23 |

|  3 | marry  | 女     | 1989-11-25 |

|  4 | lucy   | 女     | 1989-11-25 |

|  5 | lily   | 女     | 1992-01-25 |

+----+--------+--------+------------+

 

natural join：自然连接（不允许带on/using）

     natrual join：全自然连接，对左右2个表相同名字的列进行等值匹配，不可用on，using操作指定，自动删除多余重名列

     natrual left join：左自然连接，保留2个表的列（删除多余重名列），以左表为准，不存在匹配的右表列，值置为NULL

     natrual right join：和左自然连接相反

 

select * from tb_test1 natural join tb_student;

+----+--------+--------+-------+------------+

| id | name   | gender | score | birthday   |

+----+--------+--------+-------+------------+

|  1 | 李毅   | 男     | 95.30 | 1988-03-20 |

+----+--------+--------+-------+------------+

 

select * from tb_test1 natural left join tb_student;

+----+-----------+--------+-------+------------+

| id | name      | gender | score | birthday   |

+----+-----------+--------+-------+------------+

|  1 | 李毅      | 男     | 95.30 | 1988-03-20 |

|  2 | 二丫      | 女     | 95.30 | NULL       |

|  3 | 张三      | 女     | 95.30 | NULL       |

|  4 | 李四      | 女     | 95.30 | NULL       |

|  7 | 胡鲁瓦    | 男     | 95.30 | NULL       |

|  9 | 后羿      | 男     | 95.30 | NULL       |

+----+-----------+--------+-------+------------+

 

select * from tb_test1 natural right join tb_student;

+----+--------+--------+------------+-------+

| id | name   | gender | birthday   | score |

+----+--------+--------+------------+-------+

|  1 | 李毅   | 男     | 1988-03-20 | 95.30 |

|  2 | kevin  | 男     | 1987-08-23 |  NULL |

|  3 | marry  | 女     | 1989-11-25 |  NULL |

|  4 | lucy   | 女     | 1989-11-25 |  NULL |

|  5 | lily   | 女     | 1992-01-25 |  NULL |

+----+--------+--------+------------+-------+

 

inner join：内连接（不带on/using时做笛卡尔积）

     主表和关联表的笛卡尔积结果，通过on或者using操作筛选结果集（先求笛卡尔积，后筛选，所以性能上和where一样），2个表的所有列都被保存

 

 select * from tb_student a join tb_test1 b on a.id=b.id;

+----+--------+--------+------------+----+--------+--------+-------+

| id | name   | gender | birthday   | id | name   | gender | score |

+----+--------+--------+------------+----+--------+--------+-------+

|  1 | 李毅   | 男     | 1988-03-20 |  1 | 李毅   | 男     | 95.30 |

|  2 | kevin  | 男     | 1987-08-23 |  2 | 二丫   | 女     | 95.30 |

|  3 | marry  | 女     | 1989-11-25 |  3 | 张三   | 女     | 95.30 |

|  4 | lucy   | 女     | 1989-11-25 |  4 | 李四   | 女     | 95.30 |

+----+--------+--------+------------+----+--------+--------+-------+

 

select * from tb_student a join tb_test1 b on a.gender=b.gender and a.name=b.name;

+----+--------+--------+------------+----+--------+--------+-------+

| id | name   | gender | birthday   | id | name   | gender | score |

+----+--------+--------+------------+----+--------+--------+-------+

|  1 | 李毅   | 男     | 1988-03-20 |  1 | 李毅   | 男     | 95.30 |

|  6 | 李毅   | 男     | 1985-10-11 |  1 | 李毅   | 男     | 95.30 |

+----+--------+--------+------------+----+--------+--------+-------+

 

 

left join：左外连接（必须带on/using，等价与left outer join）

     左表为主表，保留全部记录全部列，右表为关联表，加上on操作之外的其他列，符合on条件的记录连接上

 

on和where的区别（在外连接时有差别）：on和where在性能上不存在差异，都是在笛卡尔积后筛选，但on在from阶段，属于join操作，先于where。所以当left join连接时，虽然左表的列不符合on条件，也必须保留，只是该记录右表全是null。而where对连接后的结果做筛选，不符合条件的直接剔除

 

select * from tb_student a left join tb_test1 b on a.id=b.id;

+----+--------+--------+------------+------+--------+--------+-------+

| id | name   | gender | birthday   | id   | name   | gender | score |

+----+--------+--------+------------+------+--------+--------+-------+

|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 |

|  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 |

|  3 | marry  | 女     | 1989-11-25 |    3 | 张三   | 女     | 95.30 |

|  4 | lucy   | 女     | 1989-11-25 |    4 | 李四   | 女     | 95.30 |

|  5 | lily   | 女     | 1992-01-25 | NULL | NULL   | NULL   |  NULL |

|  6 | 李毅   | 男     | 1985-10-11 | NULL | NULL   | NULL   |  NULL |

+----+--------+--------+------------+------+--------+--------+-------+

 

select * from tb_student a left join tb_test1 b on a.id=b.id and a.id<4;

+----+--------+--------+------------+------+--------+--------+-------+

| id | name   | gender | birthday   | id   | name   | gender | score |

+----+--------+--------+------------+------+--------+--------+-------+

|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 |

|  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 |

|  3 | marry  | 女     | 1989-11-25 |    3 | 张三   | 女     | 95.30 |

|  4 | lucy   | 女     | 1989-11-25 | NULL | NULL   | NULL   |  NULL |

|  5 | lily   | 女     | 1992-01-25 | NULL | NULL   | NULL   |  NULL |

|  6 | 李毅   | 男     | 1985-10-11 | NULL | NULL   | NULL   |  NULL |

+----+--------+--------+------------+------+--------+--------+-------+

 

select * from tb_student a left join tb_test1 b on a.id=b.id where a.id<4;

+----+--------+--------+------------+------+--------+--------+-------+

| id | name   | gender | birthday   | id   | name   | gender | score |

+----+--------+--------+------------+------+--------+--------+-------+

|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 |

|  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 |

|  3 | marry  | 女     | 1989-11-25 |    3 | 张三   | 女     | 95.30 |

+----+--------+--------+------------+------+--------+--------+-------+

 

right join：右外连接，和左外连接相反

MYSQL-交叉连接
m0_69378371的博客
04-09 772
因此，交叉链接在实际应用中并不常见，因为它会产生非常大的结果集，特别是当连接的表很大时。在这个例子中，笛卡尔积的结果是每个产品和每个地区的组合，即使这些组合在实际销售数据中并不存在。我们可以使用交叉链接来获取产品表和地区表的笛卡尔积，然后再根据实际的销售数据填充空白的销售值。你想要创建一个报表，显示每个产品在每个地区的销售情况，即使某个产品在某个地区没有销售也要显示出来。这样我们就可以获得每个产品在每个地区的销售情况，即使某个产品在某个地区没有销售，也能在结果中显示出来。表，另一个是存储地区信息的。
Mysql 多表查询详解
热门推荐
Jintao_Ma的博客
04-27 12万+
纸上得来终觉浅 1.前言：上篇讲到Mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 : 1)多表连接有哪些分类； 2)针对这些分类有哪些连接方法？ 3)这些连接方法分别作用于哪些应用场景？ 这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 2.假设有两张表格A和B，把表格当作一个集合，那么表格中
mysql cross join的用法_SQL中笛卡尔积－cross join的用法
weixin_32440261的博客
01-27 211
在数学中，笛卡尔乘积是指两个集合X和Y的笛卡尓积(Cartesian product)，又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。MySQL中笛卡尔积相当于我们学习数学中集合...
mysql cross join
zezeshenshi的专栏
03-08 1074
笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如： SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2 由于其返回的结果为被连接的两个数据表的乘积，因此当有
[Mysql] CROSS JOIN 交叉连接
Hudas的博客
05-26 3672
本文主要讲解交叉连接CROSS JOIN
Mysql中的join操作
12-16
内联结的几种写法包括标准的逗号分隔表名、`JOIN`、`INNER JOIN`以及`CROSS JOIN`。虽然`CROSS JOIN`和`INNER JOIN`在没有指定连接条件时行为相同，但它们在有明确条件时可能会有所不同。`STRAIGHT_JOIN`则允许强制...
mysql join语法
03-08
首先，我需要回忆一下自己知道的关于JOIN的内容，可能包括不同类型的JOIN，比如INNER JOIN、LEFT JOIN、RIGHT JOIN，还有FULL JOIN？不过可能MySQL不支持FULL JOIN？这点需要确认。 用户的问题是要求介绍MySQL的...
oracle中from后两张表用逗号隔开加where条件比两张表直接用join on好还是坏，有什么区别
最新发布
06-20
-**JOINON（显性连接）**：-使用明确的关键字（如INNERJOIN,LEFTJOIN,RIGHTJOIN,FULLJOIN,CROSSJOIN）来指定连接类型。-连接条件写在ON子句中，过滤条件可以单独放在WHERE子句。-示例：```sql--内连接SELECT*...
应为 ',', <constraint>, <hierarchical query clause>, CROSS, FETCH, FOR, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, MATCH_RECOGNIZE, MINUS, MODEL, NATURAL, OFFSET, ORDER, OUTER, PARTITION, PIVOT, RIGHT, UNION, UNPIVOT 或 WHERE，得到 '1'
03-12
有时开发者可能遗漏了重要的分隔符号（像逗号 `,`）或者是连接两个子句所需的适当关键字（比如 `JOIN`, `AND/OR`）。检查你的查询是否在合适的位置包含了这些必需的部分，并且确保它们拼写无误。 #### 2. 括号配对...
mysql-交叉连接Cross join(笛卡尔连接)
weixin_72935819的博客
12-06 638
cross join和inner join的表现是一样的，在不加ON条件得到的都是笛卡尔积，但是使用非MySQL时的查询时必须加on。INSERT INTO `user_a` VALUES ('张三', '男');INSERT INTO `user_a` VALUES ('李四', '女');INSERT INTO `user_b` VALUES ('赵六', 33);INSERT INTO `user_b` VALUES ('王五', 23);注：实际上将cross join替换成“,”，效果相同。
mysql 交叉连接_MySQL交叉连接（CROSS JOIN）
weixin_28840591的博客
01-18 1003
在本教程中，您将了解MySQL CROSS JOIN子句以及如何应用它来解决一些有趣的数据问题。MySQL CROSS JOIN子句简介CROSS JOIN子句从连接的表返回行的笛卡儿乘积。假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。要特别注意的是，如果每个表有10...
mysql cross join的用法_MySql 中Join操作的用法
weixin_32019329的博客
01-27 3561
SQL标准中的Join的类型：首先，设置表employees和department的数据为：1、inner join … on操作类型内连接inner join是基于连接谓词将两张表(如A和B)的列组合在一起的，产生新的结果表。例子：SELECT *FROM employees ainner join department bON a.department_id = b.department_i...
11、CROSS JOIN：交叉连接（笛卡尔积）
you can you up up up的博客
10-15 5651
前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。 在 MySQL 中，多表查询主要有交叉连接、内连接和外连接。 交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。 本节的末尾介绍了笛卡尔积，不了解笛卡尔积的读者可以先阅读文章末尾部分，然后再继续学习交叉连接。 交叉连接的语法格式如下： SELECT <字段名> FROM <表1> CROSS JOIN <表2&
mysql数据库交叉连接_MySQL交叉连接（CROSS JOIN）
weixin_39525617的博客
01-19 1109
在本教程中，您将了解MySQL CROSS JOIN子句以及如何应用它来解决一些有趣的数据问题。MySQL CROSS JOIN子句简介CROSS JOIN子句从连接的表返回行的笛卡儿乘积。假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。要特别注意的是，如果每个表有10...
MySQL入门学习-连接查询.CROSS JOIN
weixin_69763181的博客
06-23 1040
CROSS JOIN(交叉连接) ：返回左表中的所有行，左表中的每一行与右表中的所有行组合，返回的行数等于左表行数乘以右表行数。
mysql之CROSS JOIN的使用
m0_51014049的博客
08-02 436
mysql之CROSS JOIN的使用
MySQL(56)MySQL CROSS JOIN：交叉连接
逍遥人世欢
06-15 1452
前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。在 MySQL 中，多表查询主要有交叉连接、内连接和外连接。由于篇幅有限，本节主要讲解交叉连接查询。交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。字段名：需要查询的字段名称。<表1><表1>：需要交叉连接的表名。WHERE 子句：用来设置交叉连接的查询条件。
MySQL应用之CROSS JOIN用法（有时挺管用）
weixin_43390992的博客
07-24 2360
目录 cross join简介 cross join用法 本博客翻译自两篇博客的： http://www.mysqltutorial.org/mysql-cross-join/ https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php cross join简介 MySQL cross join是mysql中的一种连接方式，区别于内连接和外连接，对于cross join连接来说，其实使用的就是笛卡尔连接。在M
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值