【MySQL笔记】七种JOIN的SQL

数据库 MySQL
本文主要是作者总结的关于七种JOIN的SQL的MySQL笔记,包括左连接(LEFT JOIN)、右连接(RIGHT JOIN)、内连接(INNER JOIN)、左独有连接(LEFT JOIN)等。

准备数据

以一个简易问答系统为例,包括问题表和问题所属标签,问题表如下:

  1. CREATE TABLE `t_qa` ( 
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  3.   `title` varchar(200) NOT NULL DEFAULT '' COMMENT '标题'
  4.   `answer_count` int(5) unsigned NOT NULL DEFAULT '0' COMMENT '回答个数'
  5.   `label_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '标签id'
  6.   `create_by` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建人'
  7.   `create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间'
  8.   `update_by` bigint(20) unsigned DEFAULT NULL COMMENT '更新人'
  9.   `update_date` datetime DEFAULT NULL COMMENT '更新时间'
  10.   `del_flag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0:不删除,1:删除'
  11.   PRIMARY KEY (`id`) 
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  13.  
  14. INSERT INTO `t_qa` (`id`, `title`, `answer_count`, `label_id`, `create_by`, `create_date`, `update_by`, `update_date`, `del_flag`) 
  15. VALUES 
  16.     (1, 'Java是什么?', 5, 1, 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  17.     (2, 'PHP是什么?', 4, 2, 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  18.     (3, '前端是什么?', 3, 3, 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  19.     (4, 'nodejs是什么?', 2, 0, 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  20.     (5, 'css是什么?', 1, 0, 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  21.     (6, 'JavaScript是什么?', 0, 0, 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0);  

标签表如下:

  1. CREATE TABLE `t_label` ( 
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  3.   `namevarchar(50) NOT NULL DEFAULT '' COMMENT '名称'
  4.   `create_by` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建人'
  5.   `create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间'
  6.   `update_by` bigint(20) unsigned DEFAULT NULL COMMENT '更新人'
  7.   `update_date` datetime DEFAULT NULL COMMENT '更新时间'
  8.   `del_flag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0:不删除,1:删除'
  9.   PRIMARY KEY (`id`) 
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
  11.  
  12. INSERT INTO `t_label` (`id`, `name`, `create_by`, `create_date`, `update_by`, `update_date`, `del_flag`) 
  13. VALUES 
  14.     (1, 'java', 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  15.     (2, 'php', 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  16.     (3, '大前端', 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  17.     (4, 'mybatis', 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  18.     (5, 'python', 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0), 
  19.     (6, '多线程', 0, '2017-08-24 17:43:53', 0, '2017-08-24 17:43:53', 0);  

一、左连接(LEFT JOIN)

 

问题 回答个数 标签id 标签名称
Java是什么? 5 1 java
PHP是什么? 4 2 php
前端是什么? 3 3 大前端
nodejs是什么? 2 NULL NULL
css是什么? 1 NULL NULL
JavaScript是什么? 1 NULL NULL

    

  1. SELECT  
  2.     tq.title, tq.answer_count, tl.id, tl.name 
  3. FROM  
  4.     t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id  

二、右连接(RIGHT JOIN)

 

问题 回答个数 标签id 标签名称
Java是什么? 5 1 java
PHP是什么? 4 2 php
前端是什么? 3 3 大前端
NULL NULL 4 mybatis
NULL NULL 5 python
NULL NULL 6 多线程

 

  1. SELECT  
  2.    tq.title, tq.answer_count, tl.id, tl.name 
  3. FROM  
  4.     t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id  

三、内连接(INNER JOIN)

 

问题 回答个数 标签id 标签名称
Java是什么? 5 1 java
PHP是什么? 4 2 php
前端是什么? 3 3 大前端

   

  1. SELECT  
  2.     tq.title, tq.answer_count, tl.id, tl.name 
  3. FROM  
  4.     t_qa tq INNER JOIN t_label tl ON tq.label_id = tl.id  

四、左独有连接(LEFT JOIN)

 

问题 回答个数 标签id 标签名称
nodejs是什么? 2 NULL NULL
css是什么? 1 NULL NULL
JavaScript是什么? 0 NULL NULL

    

  1. SELECT  
  2.     tq.title, tq.answer_count, tl.id, tl.name 
  3. FROM  
  4.     t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id 
  5. WHERE 
  6.     tl.id IS NULL  

五、右独有连接(RIGHT JOIN)

 

问题 回答个数 标签id 标签名称
NULL NULL 4 mybatis
NULL NULL 5 python
NULL NULL 6 多线程

    

  1. SELECT  
  2.     tq.title, tq.answer_count, tl.id, tl.name 
  3. FROM  
  4.     t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id 
  5. WHERE 
  6.     tq.label_id IS NULL  

六、全连接(FULL JOIN)

 

由于MySQL不支持FULL OUTER JOIN,所以如果有全连接需求时,可用表达式:full outer join = left outer join UNION right outer join来实现。

问题 回答个数 标签id 标签名称
Java是什么? 5 1 java
PHP是什么? 4 2 php
前端是什么? 3 3 大前端
nodejs是什么? 2 NULL NULL
css是什么? 1 NULL NULL
JavaScript是什么? 0 NULL NULL
NULL NULL 4 mybatis
NULL NULL 5 python
NULL NULL 6 多线程

    

  1. SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id 
  2. UNION 
  3. SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id   

七、全连接去交集(FULL JOIN)

 

问题 回答个数 标签id 标签名称
nodejs是什么? 2 NULL NULL
css是什么? 1 NULL NULL
JavaScript是什么? 0 NULL NULL
NULL NULL 4 mybatis
NULL NULL 5 python
NULL NULL 6 多线程

    

  1. SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id WHERE tl.id IS NULL 
  2. UNION 
  3. SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id WHERE tq.label_id IS NULL  
责任编辑:庞桂玉 来源: segmentfault
相关推荐

2010-10-15 10:02:01

Mysql表类型

2022-05-10 08:08:01

find命令Linux

2020-01-14 08:00:00

.NET缓存编程语言

2020-01-14 11:09:36

CIO IT技术

2013-01-07 10:14:06

JavaJava枚举

2019-09-06 09:00:00

开发技能代码

2010-08-31 10:57:36

2017-06-14 16:44:15

JavaScript原型模式对象

2021-07-16 09:55:46

数据工具软件

2017-06-02 09:52:50

2019-10-29 06:30:31

告警疲劳网络安全安全风险

2010-06-08 09:49:45

UML元件

2011-03-14 10:46:03

2023-12-22 14:27:30

2020-07-15 14:56:35

欺骗性防御网络威胁网络攻击

2020-07-23 10:20:28

物联网工业物联网技术

2017-12-15 09:05:16

Linux终端高手Bash shell

2014-05-13 09:56:24

数据挖掘

2021-04-28 09:27:56

MySQLInnoDB数据库

2020-10-28 09:24:05

存储网络协议
点赞
收藏

51CTO技术栈公众号