图解SQL inner join、left join、right join、full outer join、union、union all的区别
转于:http://justcoding.iteye.com/blog/2006487
这是一篇来自Coding Horror的文章。
SQL的Join语法有很多:有inner的,有outer的,有left的,有时候,对于Select出来的结果集是什么样子有点不是很清楚。
假设我们有两张表。Table A是左边的表,Table B是右边的表。其各有四条记录,其中有两条记录name是相同的,如下所示:
A表 | |
id | name |
1 | Pirate |
2 | Monkey |
3 | Ninja |
4 | Spaghetti |
B表 | |
id | name |
1 | Rutabaga |
2 | Pirate |
3 | Darth Vade |
4 | Ninja |
1、INNER JOIN
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
结果集 | |||
(TableA.) | (TableB.) | ||
id | name | id | name |
1 | Pirate | 2 | Pirate |
3 | Ninja | 4 | Ninja |
Inner join产生的结果集是A和B的交集。
(1) SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
结果集 | |||
(TableA.) | (TableB.) | ||
id | name | id | name |
1 | Pirate | 2 | Pirate |
2 | Monkey | null | null |
3 | Ninja | 4 | Ninja |
4 | Spaghetti | null | null |
null | null | 1 | Rutabaga |
null | null | 3 | Darth Vade |
Full outer join产生A和B的并集。但是需要注意的是,对于没有匹配的记录,则会以null做为值。可以使用IFNULL判断。
注意:
mysql 不支持全连接full join 的数据库,可以用union 来代替:
select * from hx_user LEFT JOIN hx_admin on hx_user.name = hx_admin.admin_name
UNION
SELECT * from hx_user RIGHT JOIN hx_admin on hx_user.name = hx_admin.admin_name;
(2) SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null
结果集 | |||
(TableA.) | (TableB.) | ||
id | name | id | name |
2 | Monkey | null | null |
4 | Spaghetti | null | null |
null | null | 1 | Rutabaga |
null | null | 3 | Darth Vade |
产生A表和B表没有交集的数据集。
select * from hx_user LEFT JOIN hx_admin on hx_user.name = hx_admin.admin_name where hx_user.name is NULL or hx_admin.admin_name is NULL
UNION
SELECT * from hx_user RIGHT JOIN hx_admin on hx_user.name = hx_admin.admin_name where hx_user.name is NULL or hx_admin.admin_name is NULL
3、LEFT [OUTER] JOIN
(1) SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
结果集 | |||
(TableA.) | (TableB.) | ||
id | name | id | name |
1 | Pirate | 2 | Pirate |
2 | Monkey | null | null |
3 | Ninja | 4 | Ninja |
4 | Spaghetti | null | null |
Left outer join产生表A的完全集,而B表中匹配的则有值,没有匹配的则以null值取代。
(2) SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
结果集 | |||
(TableA.) | (TableB.) | ||
id | name | id | name |
2 | Monkey | null | null |
4 | Spaghetti | null | null |
。
4、RIGHT [OUTER] JOIN
RIGHT OUTERJOIN是后面的表为基础,与LEFT OUTER JOIN用法类似。这里不介绍了。
5、UNION 与 UNION ALL
UNION操作符用于合并两个或多个SELECT语句的结果集。 请注意,UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条SELECT语句中的列的顺序必须相同。 UNION只选取记录,而UNION ALL会列出所有记录。
(1) SELECT name FROM TableA UNION SELECT name FROM TableB
新结果集 |
name |
Pirate |
Monkey |
Ninja |
Spaghetti |
Rutabaga |
Darth Vade |
选取不同值
(2) SELECT name FROM TableA UNION ALL SELECT name FROM TableB
新结果集 |
name |
Pirate |
Monkey |
Ninja |
Spaghetti |
Rutabaga |
Pirate |
Darth Vade |
Ninja |
全部列出来
(3) 注意:SELECT * FROM TableA UNION SELECT * FROM TableB
新结果集 | |
id | name |
1 | Pirate |
2 | Monkey |
3 | Ninja |
4 | Spaghetti |
1 | Rutabaga |
2 | Pirate |
3 | Darth Vade |
4 | Ninja |
由于id 1 Pirate与id 2 Pirate并不相同,不合并。
还需要注册的是我们还有一个是“交差集”cross join,这种join没有办法用文式图表示,因为其就是把表A和表B的数据进行一个N*M的组合,即笛卡尔积。表达式如下:SELECT * FROM TableA CROSS JOIN TableB,这个笛卡尔乘积会产生4 x 4 = 16条记录,一般来说,我们很少用到这个语法。但是我们得小心,如果不是使用嵌套的select语句,一般系统都会产生笛卡尔乘积然再做过滤。这是对于性 能来说是非常危险的,尤其是表很大的时候。
最新文章
- PHP 语法
- 解决android expandablelistview 里面嵌入gridview行数据重复问题
- Java for LeetCode 061 Rotate List
- ios开发——常用经典算法OC篇&;冒泡/快速
- 详解QUiLoader 动态加载.ui文件
- [原]Hadoop海量视频、图像分析分布式处理总结
- [week4]每周总结与工作计划
- OBIEE 缓存机制
- 《java入门第一季》之Arrays类
- WIN10下微信崩溃(已经是最新版)的解决方法
- Java多线程(一) 什么是线程
- Jetson tk1 安装 Intel 7260 无线网卡驱动
- 【python003-变量】
- Misha and Palindrome Degree CodeForces - 501E (回文串计数)
- HDU 2048:神、上帝以及老天爷(错排公式,递推)
- ”数学口袋精灵“第二个Sprint计划---第二天
- 【纪中集训2019.3.12】Mas的仙人掌
- [Linux] 关于Unix哲学
- Java之面向对象编程20170619
- Sql Server优化之路
热门文章
- Elasticsearch-Kibana 5.5.1插件安装
- [Bug]IE11下,forms认证,出现无法保存cookie的问题
- 使用BusyBox制作linux根文件系统(CramFS+mdev)
- [Ubuntu Setup] Ubuntu 13.04 安装 ia32-libs
- Spring Dataflow批处理框架在OCP上的部署
- 剖析ASP.NET Core(Part 4)- 调用MVC中间件(译)
- iOS:quartz2D绘图(在PDF文件上绘制图片)
- 对自助提卡系统EDLM的一次代码审计
- mac设置多个屏幕显示的问题
- 流畅的python第十七章使用期物处理并发