mysql 各种连接比较
来源:互联网 发布:c 窗体编程视频教程 编辑:程序博客网 时间:2024/05/16 17:37
Assuming you're joining on columns with no duplicates, which is a very common case:
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
Examples
Suppose you have two tables, with a single column each, and data as follows:
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
Right outer join
A right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a(+) = b.b;
Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
Examples
Suppose you have two tables, with a single column each, and data as follows:
A B- -1 32 43 54 6Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b--+--3 | 34 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
a | b--+-----1 | null2 | null3 | 34 | 4
Right outer join
A right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a(+) = b.b;
a | b-----+----3 | 34 | 4null | 5null | 6
Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b-----+----- 1 | null 2 | null 3 | 3 4 | 4null | 6null | 5
0 0
- mysql 各种连接比较
- 各种连接的比较
- 各种连接池的比较
- mysql各种引擎的比较
- Mysql的各种连接方式
- Java字符串各种连接方式性能比较
- Java字符串各种连接方式性能比较
- hibernate各种连接池的比较
- Java各种字符串连接方法性能比较
- JDBC连接mysql和各种数据库连接串
- 实解MySQL中各种连接的区别
- mysql各种查询包括连接查询
- mybatis 下连接mysql各种问题
- jdbc连接mysql各种情况总结
- mysql中各种连接查询图解
- MySQL中各种连接(JOIN)详解
- spring下的各种连接池的比较
- spring下的各种连接池的比较
- 计算机图形学领域国际期刊以及会议介绍
- Mac下Laravel的Homestead环境配置
- 窗——开了又关,关了又开(改编)
- 剑指offer-面试题8 旋转数组的最小数字
- Android下的音频通道配置文件mixer_paths.xml
- mysql 各种连接比较
- VMware Workstation cannot connect to the virtual machine 解决方案
- SQL系列之基本操作
- 数列查询算法
- 企业应用的趋势
- usb3.0开发指南:验证软件框架
- struts2 java.lang.ClassNotFoundException: org.apache.commons.lang.xwork.StringUtils
- 2014 蓝桥杯JavaB组省赛 分糖果
- python回溯法求解0-1背包问题的最优值