mysql case join_mysql中的JOIN用法总结

最新推荐文章于 2021-01-19 08:52:13 发布
最新推荐文章于 2021-01-19 08:52:13 发布
阅读量428 收藏
点赞数
文章标签： mysql case join
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39871162/article/details/113225173
版权

join是mysql中一个基础的关键词，一般在多表连接查询中使用，这里做一下总结

1、JOIN的语法格式

table_references:

table_reference[, table_reference]...

table_reference:

table_factor|join_table

table_factor:

tbl_name[[AS]alias][{USE|IGNORE|FORCE} INDEX (key_list)]

|( table_references )| { OJ table_reference LEFT OUTER JOINtable_referenceONconditional_expr }

join_table:

table_reference[INNER | CROSS] JOIN table_factor [join_condition]

|table_reference STRAIGHT_JOIN table_factor| table_reference STRAIGHT_JOIN table_factor ONcondition| table_reference LEFT [OUTER] JOINtable_reference join_condition| table_reference NATURAL [LEFT [OUTER]] JOINtable_factor| table_reference RIGHT [OUTER] JOINtable_reference join_condition| table_reference NATURAL [RIGHT [OUTER]] JOINtable_factor

join_condition:ONconditional_expr| USING (column_list)

2、JOIN解析说明

我们先准备实验例子(mysql 版本：mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i686) using readline 5.1)

CREATE TABLE`test_join_a` (

`id`bigint(20) NOT NULL DEFAULT '0',

`name`varchar(200),PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE`test_join_b` (

`id`bigint(20) NOT NULL DEFAULT '0',

`sex`varchar(200),PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE`test_join_c` (

`id`bigint(20) NOT NULL DEFAULT '0',

`class`varchar(200),PRIMARY KEY(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into test_join_a (id) values (1);insert into test_join_a (id, name) values (2, "abc");insert into test_join_a (id, name) values (3, "abcd");insert into test_join_a (id, name) values (4, "abcde");insert into test_join_b (id) values (1);insert into test_join_b (id, sex) values (2, "abc");insert into test_join_b (id, sex) values (3, "abc");insert into test_join_c (id) values (1);insert into test_join_c (id, class) values (2, "abc");insert into test_join_c (id, class) values (3, "abc");

a、关于LEFT JOIN

对于 A LEFT JOIN (B， C)  ON join_condition ，A表m，B表n， C表t 条记录   【说明：由于未发现专业术语描述A，B，C三表区别，此处，将A表暂称基础表，B,C表称附加表】

* 以A表为基础表，从B表和C表中挑选符合ON join_condition条件的数据，如果没有符合的，则附加表(B,C)的列对应的行设置为NULL。

* 通过这种连接方式，查询后的数据条数，最多为 m * n * t条， 最少为 m条

同理，对于(A, B) LEFT JOIN C ON join_condition

* 以A， B表为基础表，从C表中挑选符合ON join_condition条件的数据，如果没有符合的，则附加表(C)的列对应的行设置为NULL。

* 通过这种连接方式，查询后的数据条数，最多为 m * n * t条， 最少为 m * n条

在LEFT JOIN 和 RIGHT JOIN 中，ON 子句后还有 WHERE 子句的筛选条件，区别在于

1、where子句可以省略，ON 子句不能省略，如果不用ON join_condition，则可以使用ON 1=1 或者 ON 1

2、ON join_condtion 会以基础表的笛卡尔积为基础，附加表没有符合ON join_condtion的记录，则将附加表列对应的行设为NULL。

3、where 子句会将 ON join_condition生成的虚拟表做筛选。所以通过where子句后，获取的记录数，最少为 0条

注意： NULL=NULL 既不符合 join的on condition 也不符合 where子句的筛选

下面是两个实验过程，a表4条记录，b表3条，c表3条：

1、可以看到生成记录条数4 * 3 * 3 = 36 条

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) on 1=1;+-----+-------+------+------+------+-------+

| aid | name | bid | sex | cid | class |

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

| 1 | NULL | 1 | NULL | 1 | NULL |

| 1 | NULL | 1 | NULL | 2 | abc |

| 1 | NULL | 1 | NULL | 3 | abc |

| 1 | NULL | 2 | abc | 1 | NULL |

| 1 | NULL | 2 | abc | 2 | abc |

| 1 | NULL | 2 | abc | 3 | abc |

| 1 | NULL | 3 | abc | 1 | NULL |

| 1 | NULL | 3 | abc | 2 | abc |

| 1 | NULL | 3 | abc | 3 | abc |

| 2 | abc | 1 | NULL | 1 | NULL |

| 2 | abc | 1 | NULL | 2 | abc |

| 2 | abc | 1 | NULL | 3 | abc |

| 2 | abc | 2 | abc | 1 | NULL |

| 2 | abc | 2 | abc | 2 | abc |

| 2 | abc | 2 | abc | 3 | abc |

| 2 | abc | 3 | abc | 1 | NULL |

| 2 | abc | 3 | abc | 2 | abc |

| 2 | abc | 3 | abc | 3 | abc |

| 3 | abcd | 1 | NULL | 1 | NULL |

| 3 | abcd | 1 | NULL | 2 | abc |

| 3 | abcd | 1 | NULL | 3 | abc |

| 3 | abcd | 2 | abc | 1 | NULL |

| 3 | abcd | 2 | abc | 2 | abc |

| 3 | abcd | 2 | abc | 3 | abc |

| 3 | abcd | 3 | abc | 1 | NULL |

| 3 | abcd | 3 | abc | 2 | abc |

| 3 | abcd | 3 | abc | 3 | abc |

| 4 | abcde | 1 | NULL | 1 | NULL |

| 4 | abcde | 1 | NULL | 2 | abc |

| 4 | abcde | 1 | NULL | 3 | abc |

| 4 | abcde | 2 | abc | 1 | NULL |

| 4 | abcde | 2 | abc | 2 | abc |

| 4 | abcde | 2 | abc | 3 | abc |

| 4 | abcde | 3 | abc | 1 | NULL |

| 4 | abcde | 3 | abc | 2 | abc |

| 4 | abcde | 3 | abc | 3 | abc |

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

36 rows in set (0.00 sec)

2、筛选符合条件的记录，如果没有符合的，则设置为NULL，参看实验表中aid=1，3, 4情况。

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) on

a.name=b.sex;+-----+-------+------+------+------+-------+

| aid | name | bid | sex | cid | class |

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

| 1 | NULL | NULL | NULL | NULL | NULL |

| 2 | abc | 2 | abc | 1 | NULL |

| 2 | abc | 2 | abc | 2 | abc |

| 2 | abc | 2 | abc | 3 | abc |

| 2 | abc | 3 | abc | 1 | NULL |

| 2 | abc | 3 | abc | 2 | abc |

| 2 | abc | 3 | abc | 3 | abc |

| 3 | abcd | NULL | NULL | NULL | NULL |

| 4 | abcde | NULL | NULL | NULL | NULL |

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

9 rows in set (0.00 sec)

3、针对 (A, B) LEFT JOIN C ON join_condition 的情况，筛选符合条件的记录，以A，B表的笛卡尔积为基础，查找C中符合 on join_condition的记录数，如果没有，则将C中对应列的行值设置未NULL。

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a, test_join_b b) left join test_join_c c on b.id>5;+-----+-------+-----+------+------+-------+

| aid | name | bid | sex | cid | class |

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

| 1 | NULL | 1 | NULL | NULL | NULL |

| 1 | NULL | 2 | abc | NULL | NULL |

| 1 | NULL | 3 | abc | NULL | NULL |

| 2 | abc | 1 | NULL | NULL | NULL |

| 2 | abc | 2 | abc | NULL | NULL |

| 2 | abc | 3 | abc | NULL | NULL |

| 3 | abcd | 1 | NULL | NULL | NULL |

| 3 | abcd | 2 | abc | NULL | NULL |

| 3 | abcd | 3 | abc | NULL | NULL |

| 4 | abcde | 1 | NULL | NULL | NULL |

| 4 | abcde | 2 | abc | NULL | NULL |

| 4 | abcde | 3 | abc | NULL | NULL |

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

12 rows in set (0.00 sec)

4、可以看出ON condition的执行在WHERE之前，ON condition会形成虚拟表。

mysql> explain select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) on b.id>5 where b.id>5;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | b | range | PRIMARY | PRIMARY | 8 | NULL | 1 | Using where |

| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 3 | Using join buffer |

| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | Using join buffer |

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

3 rows in set (0.00 sec)

5、WHERE 对虚拟表的筛选和ON条件是不一样的，ON condition会保存基础表的每条数据，附加表中没有符合的，则将附加表列设置为NULL，WHERE 只是筛选符合WHERE 子句的记录。 (可以从下面的第三次查询的结果看出，查询的是通过ON condition形成的虚拟表)

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) on b.id>5;+-----+-------+------+------+------+-------+

| aid | name | bid | sex | cid | class |

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

| 1 | NULL | NULL | NULL | NULL | NULL |

| 2 | abc | NULL | NULL | NULL | NULL |

| 3 | abcd | NULL | NULL | NULL | NULL |

| 4 | abcde | NULL | NULL | NULL | NULL |

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

4 rows in set (0.00sec)

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) on b.id>5where b.id>5;

Emptyset (0.00 sec)

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) ON a.name=b.sex where b.sex is NULL;

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

| aid | name  | bid  | sex  | cid  | class |

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

|   1 | NULL  | NULL | NULL | NULL | NULL  |

|   3 | abcd  | NULL | NULL | NULL | NULL  |

|   4 | abcde | NULL | NULL | NULL | NULL  |

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

6、NULL=NULL 既不符合 join的on condition 也不符合 where子句的筛选。

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) ON a.name=b.sex;+-----+-------+------+------+------+-------+

| aid | name | bid | sex | cid | class |

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

| 1 | NULL | NULL | NULL | NULL | NULL |

| 2 | abc | 2 | abc | 1 | NULL |

| 2 | abc | 2 | abc | 2 | abc |

| 2 | abc | 2 | abc | 3 | abc |

| 2 | abc | 3 | abc | 1 | NULL |

| 2 | abc | 3 | abc | 2 | abc |

| 2 | abc | 3 | abc | 3 | abc |

| 3 | abcd | NULL | NULL | NULL | NULL |

| 4 | abcde | NULL | NULL | NULL | NULL |

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

9 rows in set (0.00sec)

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from test_join_a a left join (test_join_b b, test_join_c c) ON a.name=b.sex where a.name=b.sex;+-----+------+------+------+------+-------+

| aid | name | bid | sex | cid | class |

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

| 2 | abc | 2 | abc | 1 | NULL |

| 2 | abc | 3 | abc | 1 | NULL |

| 2 | abc | 2 | abc | 2 | abc |

| 2 | abc | 3 | abc | 2 | abc |

| 2 | abc | 2 | abc | 3 | abc |

| 2 | abc | 3 | abc | 3 | abc |

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

6 rows in set (0.00 sec)

b、关于RIGHT JOIN

right join和 left join类似，区别在于

1、对于 A right join (B, C) on join_condition ，会以 B,C表为基础表，A表为附加表

2、对于 (B, C) right join A on join_condition ，会以 A 表为基础表，B,C表为附加表

C、关于JOIN

JOIN与LEFT JOIN 和RIGHT JOIN的区别在于 JOIN 不区别左右基础表，可以认为他的ON condition 退化成where 子句形式，

例如：对于下面的SQL

select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a ,test_join_b b) join test_join_c c ON a.name=b.sex;

结果上等价于

select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a ,test_join_b b) join test_join_c c ON 1=1 where a.name=b.sex;

同时对于 A JOIN (B, C) 等价于 (A，B) JOIN C

关于JOIN的解析过程，我们可以理解成， 将A,B,C表做笛卡尔积，构成新的虚拟表， ON condition转化成where 子句，结合后面的where 子句做筛选，得出筛选结果。

我们来看一下实验情况：

mysql> explain select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a ,test_join_b b) join test_join_c c ON a.name=b.sex;+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 3 | |

| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 3 | Using join buffer |

| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer |

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

3 rows in set (0.00sec)

mysql> explain select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a ,test_join_b b) join test_join_c c ON 1=1 where a.name=b.sex;+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 3 | |

| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 3 | Using join buffer |

| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer |

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

3 rows in set (0.00 sec)

可以看出，两个sql的explain结果是一样的，拥有相同的执行计划

我们再看看这两个sql的执行结果

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a ,test_join_b b) join test_join_c c ON a.name=b.sex;+-----+------+-----+------+-----+-------+

| aid | name | bid | sex | cid | class |

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

| 2 | abc | 2 | abc | 1 | NULL |

| 2 | abc | 3 | abc | 1 | NULL |

| 2 | abc | 2 | abc | 2 | abc |

| 2 | abc | 3 | abc | 2 | abc |

| 2 | abc | 2 | abc | 3 | abc |

| 2 | abc | 3 | abc | 3 | abc |

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

6 rows in set (0.00sec)

mysql> select a.id as aid, a.name, b.id as bid, b.sex, c.id as cid, c.class from (test_join_a a ,test_join_b b) join test_join_c c ON 1=1 where a.name=b.sex;+-----+------+-----+------+-----+-------+

| aid | name | bid | sex | cid | class |

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

| 2 | abc | 2 | abc | 1 | NULL |

| 2 | abc | 3 | abc | 1 | NULL |

| 2 | abc | 2 | abc | 2 | abc |

| 2 | abc | 3 | abc | 2 | abc |

| 2 | abc | 2 | abc | 3 | abc |

| 2 | abc | 3 | abc | 3 | abc |

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

6 rows in set (0.00 sec)

weixin_39871162
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql case join,使用CASE进行MYSQL LEFT JOIN优化
weixin_42516490的博客
01-19 729
I spent some time trying to get working this SELECT with CASE but I failed... (thank to that I'm using COLASCE() now)How could I optimize this SELECT by using CASE/IF sentences? Is this a fast way to ...
Mysql数据库—高阶语句二（连接查询、交集、case）
Yusheng9527的博客
02-16 945
Mysql数据库—高阶语句二（连接查询、交集、case） 一、连接查询1.1 内连接-inner join1.2 左连接-left join1.3 右连接-right join二、视图-create view三、联集-union3.1 作用3.2 语法四、交集值-取两个sql语句结果的交集4.1 方法1：distinct inner join on.../using(...)4.2 方法2：union all + group by + having count(../*) > 14.3 方法三：外查询
mysql case 连接,根据Mysql Case语句将一个表连接到另一个表
weixin_30341445的博客
01-19 328
I have a table called PublicForum that 4 different users can post to, on this table I have 2 fields called AuthorId(int) and AuthorType(Enum) that shows the User Id and which user wrote the post.Now I...
mysql join查询
weixin_34357436的博客
11-26 193
下面是例子分析 表A记录如下： aIDaNum1a200501112a200501123a200501134a200501145a20050115 表B记录如下： bIDbName120060324012200603240232006032403420060...
MySQL排序中使用CASE WHEN的方法示例
12-16
以下是一个关于如何在MySQL排序中使用`CASE WHEN`方法的详细说明。 ### 1. `CASE WHEN` 基本语法 `CASE WHEN` 的基本语法如下： ```sql CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ...
mysql多个left join连接查询用法分析
12-16
本篇文章将深入探讨如何在MySQL中有效地使用多个`LEFT JOIN`进行连接查询，并通过示例分析其性能优化。 首先，我们要理解`LEFT JOIN`的工作原理。`LEFT JOIN`返回左表的所有记录，即使右表没有匹配的记录。如果右表...
浅析Mysql Join语法以及性能优化
09-10
MySQL中的JOIN操作是数据库查询中不可或缺的部分，它用于在多个表之间建立关联，获取符合特定条件的数据。在本文中，我们将深入理解JOIN语法，并探讨如何进行性能优化。 首先，JOIN的基本语法涉及FROM子句后的两个...
mysql_proc_page_order.zip_page_proc_page_排序MYSQL
09-21
在MySQL数据库系统中，处理大量数据时，翻页与排序是常见的需求，这涉及到数据库查询的性能优化。本篇将详细解析标题“mysql_proc_page_order.zip_page_proc_page_排序MYSQL”所涵盖的知识点，以及如何在MySQL中实现...
mysql-function.rar_mysql pdf
09-23
MySQL是一种广泛使用的开源关系型数据库管理系统，以其高效、稳定和易于学习的特点深受开发者喜爱。这份“mysql函数大全(PDF版)”文档无疑是学习和查询MySQL函数的宝贵资源。它涵盖了MySQL中的各种内置函数，包括但...
MySQL中Case When用法详解
热门推荐
午夜阳光
07-09 15万+
      最近，在学习Hive基础知识时，遇到了遇到了Case When Else End语法，以前学习MySQL时忽略了这部分知识点，现总结一下相关的知识给大家。首先练习一个例子如下：一、学生课程成绩统计1)建表use hiveDemo; CREATE TABLE `course` ( `id` int, `sid` int , `course` string, `score`...
mysql case left join_一个复杂的mysql查询语句(case,when,then,left join )
weixin_39576066的博客
01-19 1187
select `a`.`id` AS `id`,`a`.`UserName` AS `UserName`,(case when (`a`.`sRegDate` = _utf8'1990-01-01 00:00:00.0') then _utf8'' else cast(date_format(`a`.`sRegDate`,_utf8'%Y-%m-%d %H:%i:%S') as char char...
mysql中join的四种连接
快乐的鱼儿学敲码
09-13 1673
MySQL是通过Join语句把两个或多个表连接起来，而生成的新表可以被当成表来使用，也可以保存起来。Join语句的含义是把两张表的属性通过它们的值组合在一起. 1. 内连接 表A与表B进行内连接，就是将表A的某属性与表B的相应属性进行逐行比较，找出满足条件的组合，并按照输出需求合并成一行。有两种：相等连接和自然连接。 例如——相等连接（最常用） 查询的是列出所有城市的所属国家以及独
sql when case 根据不同 case 查询不同关联表信息
彻底拆分，一切可控！
07-13 1万+
sql case when
sql join中能否使用case when_SQL：多表查询
weixin_39683734的博客
12-03 595
目录一、 表的加法二、 表的联结交叉联结（笛卡尔积）内联结左联结右联结全联结三、联结应用案列四、 case表达式一 、表的加法加法：union（把两个表的查询语句合并在一起，会删除重复的数据） union all（把两个表的查询语句合并在一起，不会删除重复的数据）二、表的联结两个表之间产生关系的联结（join）school中四张表之间的关系：1.交叉联结（笛卡尔积cross jo...
传感器驱动+imx296驱动+Linux驱动
最新发布
08-10
传感器驱动+imx296驱动+Linux驱动
mysql left join 行转列_mysql行转列转换
05-31
MySQL中可以使用GROUP_CONCAT函数和CASE语句实现行转列的转换。 假设我们有两个表，一个是用户表user，一个是订单表order，每个用户可以有多个订单，现在我们要将用户和他们的订单信息进行行转列的转换，将每个用户...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值