sql之left join、right join、inner join的区别

union、union all的区别跳转https://www.cnblogs.com/logon/p/3748020.html

SQL JOINS:



 

Please refer the link : https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins 

 

如图：



left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行

举例如下： 

--------------------------------------------

表A记录如下：

aID　　　　　aNum

1　　　　　a20050111

2　　　　　a20050112

3　　　　　a20050113

4　　　　　a20050114

5　　　　　a20050115

表B记录如下:

bID　　　　　bName

1　　　　　2006032401

2　　　　　2006032402

3　　　　　2006032403

4　　　　　2006032404

8　　　　　2006032408

--------------------------------------------

1.left join

sql语句如下: 

select * from A

left join B 

on A.aID = B.bID

结果如下:

aID　　　　　aNum　　　　　bID　　　　　bName

1　　　　　a20050111　　　　1　　　　　2006032401

2　　　　　a20050112　　　　2　　　　　2006032402

3　　　　　a20050113　　　　3　　　　　2006032403

4　　　　　a20050114　　　　4　　　　　2006032404

5　　　　　a20050115　　　　NULL　　　　　NULL

（所影响的行数为 5 行）

结果说明:

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).

B表记录不足的地方均为NULL.

--------------------------------------------

2.right join

sql语句如下: 

select * from A

right join B 

on A.aID = B.bID

结果如下:

aID　　　　　aNum　　　　　bID　　　　　bName

1　　　　　a20050111　　　　1　　　　　2006032401

2　　　　　a20050112　　　　2　　　　　2006032402

3　　　　　a20050113　　　　3　　　　　2006032403

4　　　　　a20050114　　　　4　　　　　2006032404

NULL　　　　　NULL　　　　　8　　　　　2006032408

（所影响的行数为 5 行）

结果说明:

仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

--------------------------------------------

3.inner join

sql语句如下: 

select * from A

innerjoin B 

on A.aID = B.bID

结果如下:

aID　　　　　aNum　　　　　bID　　　　　bName

1　　　　　a20050111　　　　1　　　　　2006032401

2　　　　　a20050112　　　　2　　　　　2006032402

3　　　　　a20050113　　　　3　　　　　2006032403

4　　　　　a20050114　　　　4　　　　　2006032404

结果说明:

很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

--------------------------------------------

注: 

LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

原文：http://www.cnblogs.com/pcjim/articles/799302.html

inner join(等值连接) 只返回两个表中联结字段相等的行



left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

 

INNER JOIN 语法：

 

INNER JOIN 连接两个数据表的用法：



SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

 

INNER JOIN 连接三个数据表的用法：



SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

 

INNER JOIN 连接四个数据表的用法：



SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN

表4 ON Member.字段号=表4.字段号

 

INNER JOIN 连接五个数据表的用法：



SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

 

连接六个数据表的用法：略，与上述联接方法类似，大家举一反三吧：）

 

注意事项：

在输入字母过程中，一定要用英文半角标点符号，单词之间留一半角空格；

在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是“数字”数据类型，而多个表中的相同字段必须是主键，而且是“自动编号”数据类型。否则，很难联接成功。

代码嵌套快速方法：如，想连接五个表，则只要在连接四个表的代码上加一个前后括号（前括号加在FROM的后面，后括号加在代码的末尾即可），然后在后括号后面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可，这样就可以无限联接数据表了：）

1.理论

只要两个表的公共字段有匹配值，就将这两个表中的记录组合起来。

个人理解：以一个共同的字段求两个表中符合要求的交集，并将每个表符合要求的记录以共同的字段为牵引合并起来。

语法

select * FROM table1 INNER JOIN table2 ON table1 . field1 compopr table2 . field2

INNER JOIN 操作包含以下部分：

 

部分
			
说明

			 
		
table1, table2
			
要组合其中的记录的表的名称。
		
field1，field2
			
要联接的字段的名称。如果它们不是数字，则这些字段的数据类型必须相同，并且包含同类数据，但是，它们不必具有相同的名称。
		
compopr

			 
			
任何关系比较运算符：“=”、“<”、“>”、“<=”、“>=”或者“<>”。
		
     

说明

可以在任何 FROM 子句中使用 INNER JOIN 操作。这是最常用的联接类型。只要两个表的公共字段上存在相匹配的值，Inner 联接就会组合这些表中的记录。

可以将 INNER JOIN 用于 Departments 及 Employees 表，以选择出每个部门的所有雇员。而要选择所有部分（即使某些部门中并没有被分配雇员）或者所有雇员（即使某些雇员没有分配到任何部门），则可以通过 LEFT JOIN 或者 RIGHT JOIN 操作来创建外部联接。

如果试图联接包含备注或 OLE 对象数据的字段，将发生错误。

可以联接任何两个相似类型的数字字段。例如，可以联接自动编号和长整型字段，因为它们均是相似类型。然而，不能联接单精度型和双精度型类型字段。

下例展示了如何通过 CategoryID 字段联接 Categories 和 Products 表：

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID;

在前面的示例中，CategoryID 是被联接字段，但是它不包含在查询输出中，因为它不包含在 SELECT 语句中。若要包含被联接字段，请在 SELECT 语句中包含该字段名，在本例中是指 Categories.CategoryID。

也可以在 JOIN 语句中链接多个 ON 子句，请使用如下语法：

SELECT fields

FROM table1 INNER JOIN table2

ON table1.field1 compopr table2.field1 AND

ON table1.field2 compopr table2.field2 OR

ON table1.field3 compopr table2.field3;

也可以通过如下语法嵌套 JOIN 语句：

SELECT fields

FROM table1 INNER JOIN

(table2 INNER JOIN [( ]table3

[INNER JOIN [( ]tablex [INNER JOIN ...)]

ON table3.field3 compopr tablex.fieldx)]

ON table2.field2 compopr table3.field3)

ON table1.field1 compopr table2.field2;

LEFT JOIN 或 RIGHT JOIN 可以嵌套在 INNER JOIN 之中，但是 INNER JOIN 不能嵌套于 LEFT JOIN 或 RIGHT JOIN 之中。


2.操作实例

表A记录如下：

aID               aNum

1                  a20050111

2                  a20050112

3                  a20050113

4                  a20050114

5                  a20050115

表B记录如下:

bID               bName

1                   2006032401

2                  2006032402

3                  2006032403

4                  2006032404

8                  2006032408


实验如下:

1.left join

sql语句如下:

select * from A

left join B

on A.aID = B.bID

结果如下:

aID               aNum                          bID                  bName

1                   a20050111                1                      2006032401

2                   a20050112                2                     2006032402

3                   a20050113                3                     2006032403

4                   a20050114                4                     2006032404

5                   a20050115                NULL              NULL

（所影响的行数为 5 行）

结果说明:

               left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).

B表记录不足的地方均为NULL.

2.right join

sql语句如下:

select * from A

right join B

on A.aID = B.bID

结果如下:

aID               aNum                          bID                  bName

1                   a20050111                1                      2006032401

2                   a20050112                2                     2006032402

3                   a20050113                3                     2006032403

4                   a20050114                4                     2006032404

NULL           NULL                          8                     2006032408

（所影响的行数为 5 行）
结果说明:

        仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.


3.inner join

sql语句如下:

select * from A

innerjoin B

on A.aID = B.bID

结果如下:

aID               aNum                          bID                  bName

1                   a20050111                1                      2006032401

2                   a20050112                2                     2006032402

3                   a20050113                3                     2006032403

4                   a20050114                4                     2006032404

结果说明:

        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.  还有就是inner join 可以结合where语句来使用 如：   select * from A innerjoin B on A.aID = B.bID where b.bname='2006032401' 这样的话 就只会放回一条数据了

 

多少都是爱，请多多支持作者



 

作者：lianghc

连接：http://blog.csdn.net/zutsoft/article/details/51498026

merge  通过键拼接列

pandas提供了一个类似于关系数据库的连接(join)操作的方法merage,可以根据一个或多个键将不同DataFrame中的行连接起来

语法如下：

merge(left, right, how='inner', on=None, left_on=None, right_on=None,
      left_index=False, right_index=False, sort=True,
      suffixes=('_x', '_y'), copy=True, indicator=False)

用于通过一个或多个键将两个数据集的行连接起来，类似于 SQL 中的 JOIN。

该函数的典型应用场景是：针对同一个主键存在两张包含不同字段的表，现在我们想把他们整合到一张表里。在此典型情况下，结果集的行数并没有增加，列数则为两个元数据的列数和减去连接键的数量。

on=None 用于显示指定列名（键名），如果该列在两个对象上的列名不同，则可以通过 left_on=None, right_on=None 来分别指定。或者想直接使用行索引作为连接键的话，就将 left_index=False, right_index=False 设为 True。
	
how='inner' 参数指的是当左右两个对象中存在不重合的键时，取结果的方式：inner 代表交集；outer 代表并集；left 和 right 分别为取一边。
	
suffixes=('_x','_y') 指的是当左右对象中存在除连接键外的同名列时，结果集中的区分方式，可以各加一个小尾巴。
	
对于多对多连接，结果采用的是行的笛卡尔积。
参数说明：

left与right：两个不同的DataFrame

how：指的是合并(连接)的方式有inner(内连接),left(左外连接),right(右外连接),outer(全外连接);默认为inner

on : 指的是用于连接的列索引名称。必须存在右右两个DataFrame对象中，如果没有指定且其他参数也未指定则以两个DataFrame的列名交集做为连接键

left_on：左则DataFrame中用作连接键的列名;这个参数中左右列名不相同，但代表的含义相同时非常有用。

right_on：右则DataFrame中用作 连接键的列名

left_index：使用左则DataFrame中的行索引做为连接键

right_index：使用右则DataFrame中的行索引做为连接键

sort：默认为True，将合并的数据进行排序。在大多数情况下设置为False可以提高性能

suffixes：字符串值组成的元组，用于指定当左右DataFrame存在相同列名时在列名后面附加的后缀名称，默认为('_x','_y')

copy：默认为True,总是将数据复制到数据结构中；大多数情况下设置为False可以提高性能

indicator：在 0.17.0中还增加了一个显示合并数据中来源情况；如只来自己于左边(left_only)、两者(both)

 

merge一些特性示例：

1.默认以重叠的列名当做连接键。

 

In [16]: df1=DataFrame({'key':['a','b','b'],'data1':range(3)})

In [17]: df2=DataFrame({'key':['a','b','c'],'data2':range(3)})

In [18]: pd.merge(df1,df2)   #没有指定连接键，默认用重叠列名，没有指定连接方式
Out[18]: 
   data1 key  data2
0      0   a      0
1      1   b      1
2      2   b      1

 

 

 

2.默认做inner连接（取key的交集），连接方式还有（left,right,outer)，制定连接方式加参数：how=''

 

In [19]: pd.merge(df2,df1)
Out[19]: 
   data2 key  data1
0      0   a      0
1      1   b      1
2      1   b      2                   #默认内连接，可以看见c没有连接上。

In [20]: pd.merge(df2,df1,how='left')    #通过how，指定连接方式
Out[20]: 
   data2 key  data1
0      0   a      0
1      1   b      1
2      1   b      2
3      2   c    NaN

 

 

 

3.多键连接时将连接键组成列表传入，例：pd.merge(df1,df2,on=['key1','key2']

 

In [23]: right=DataFrame({'key1':['foo','foo','bar','bar'],
    ...:     'key2':['one','one','one','two'],
    ...:     'lval':[4,5,6,7]})

In [24]: left=DataFrame({'key1':['foo','foo','bar'],
    ...:     'key2':['one','two','one'],
    ...:     'lval':[1,2,3]})

In [25]: right=DataFrame({'key1':['foo','foo','bar','bar'],
    ...:     'key2':['one','one','one','two'],
    ...:     'lval':[4,5,6,7]})

In [26]: pd.merge(left,right,on=['key1','key2'],how='outer')  #传出数组
Out[26]: 
  key1 key2  lval_x  lval_y
0  foo  one       1       4
1  foo  one       1       5
2  foo  two       2     NaN
3  bar  one       3       6
4  bar  two     NaN       7

 

 

 

4.如果两个对象的列名不同，可以分别指定，例：pd.merge(df1,df2,left_on='lkey',right_on='rkey')

 

In [31]: df3=DataFrame({'key3':['foo','foo','bar','bar'], #将上面的right的key 改了名字
    ...:     'key4':['one','one','one','two'],
    ...:     'lval':[4,5,6,7]})

In [32]: pd.merge(left,df3,left_on='key1',right_on='key3')  #键名不同的连接
Out[32]:  
  key1 key2  lval_x key3 key4  lval_y
0  foo  one       1  foo  one       4
1  foo  one       1  foo  one       5
2  foo  two       2  foo  one       4
3  foo  two       2  foo  one       5
4  bar  one       3  bar  one       6
5  bar  one       3  bar  two       7

 

 

 

5.以索引当做连接键，使用参数left_index=true,right_index=True （最好使用join）

join 拼接列，主要用于索引上的合并



join方法提供了一个简便的方法用于将两个DataFrame中的不同的列索引合并成为一个DataFrame

join(self, other, on=None, how='left', lsuffix='', rsuffix='',sort=False):

其中参数的意义与merge方法基本相同,只是join方法默认为左外连接how=left

1.默认按索引合并，可以合并相同或相似的索引，不管他们有没有重叠列。

2.可以连接多个DataFrame

3.可以连接除索引外的其他列

4.连接方式用参数how控制

5.通过lsuffix='', rsuffix='' 区分相同列名的列

concat  可以沿着一条轴将多个对象堆叠到一起

concat方法相当于数据库中的全连接(UNION ALL),可以指定按某个轴进行连接,也可以指定连接的方式join(outer,inner 只有这两种)。与数据库不同的时concat不会去重，要达到去重的效果可以使用drop_duplicates方法

 concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
           keys=None, levels=None, names=None, verify_integrity=False, copy=True):

轴向连接 pd.concat() 就是单纯地把两个表拼在一起，这个过程也被称作连接（concatenation）、绑定（binding）或堆叠（stacking）。因此可以想见，这个函数的关键参数应该是 axis，用于指定连接的轴向。

在默认的 axis=0 情况下，pd.concat([obj1,obj2]) 函数的效果与 obj1.append(obj2) 是相同的；

而在 axis=1 的情况下，pd.concat([df1,df2],axis=1) 的效果与 pd.merge(df1,df2,left_index=True,right_index=True,how='outer') 是相同的。

可以理解为 concat 函数使用索引作为“连接键”。

本函数的全部参数为：

pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False)。

objs 就是需要连接的对象集合，一般是列表或字典；

axis=0 是连接轴向join='outer' 参数作用于当另一条轴的 index 不重叠的时候，只有 'inner' 和 'outer' 可选（顺带展示 ignore_index=True 的用法）

 

concat 一些特点：

1.作用于Series时，如果在axis=0时，类似union。axis=1 时，组成一个DataFrame，索引是union后的，列是类似join后的结果。

2.通过参数join_axes=[] 指定自定义索引。

3.通过参数keys=[] 创建层次化索引

4.通过参数ignore_index=True 重建索引。

 

In [5]: df1=DataFrame(np.random.randn(3,4),columns=['a','b','c','d'])

In [6]: df2=DataFrame(np.random.randn(2,3),columns=['b','d','a'])

In [7]: pd.concat([df1,df2])
Out[7]: 
          a         b         c         d
0 -0.848557 -1.163877 -0.306148 -1.163944
1  1.358759  1.159369 -0.532110  2.183934
2  0.532117  0.788350  0.703752 -2.620643
0 -0.316156 -0.707832       NaN -0.416589
1  0.406830  1.345932       NaN -1.874817

In [8]: pd.concat([df1,df2],ignore_index=True)
Out[8]: 
          a         b         c         d
0 -0.848557 -1.163877 -0.306148 -1.163944
1  1.358759  1.159369 -0.532110  2.183934
2  0.532117  0.788350  0.703752 -2.620643
3 -0.316156 -0.707832       NaN -0.416589
4  0.406830  1.345932       NaN -1.874817

 

 

 

 

 

 

 

一、概念

1、左连接 left outer join

以左边表为准，逐条去右边表找相同字段，如果有多条会依次列出。

2、连接join

找出左右相同同的记录。

3、全连接 full outer  join

包括两个表的join结果，左边在右边中没找到的结果（NULL），右边在左边没找到的结果。

会对结果去重，返回并集。

 

二、实验

1、准备数据

create external table IF NOT EXISTS temp_testjoin_ta
(
label string,
qu string
)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;
ALTER TABLE temp_testjoin_ta ADD IF NOT EXISTS PARTITION (dt = '2014-08-08') location '/temp/jinlong10/testjoin/ta';

l1	q1
l1	q2


create external table IF NOT EXISTS temp_testjoin_tb
(
qu string,
inmyway string
)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;
ALTER TABLE temp_testjoin_tb ADD IF NOT EXISTS PARTITION (dt = '2014-08-08') location '/temp/jinlong10/testjoin/tb/';

q1	i1
q1	i1
q1	i2
q1	i3
q2	i1
q2	i2
q3	i10



2、join

select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu ;
l1	q1	q1	i1
l1	q1	q1	i1
l1	q1	q1	i2
l1	q1	q1	i3
l1	q2	q2	i1
l1	q2	q2	i2


select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu group by label,inmyway;
l1	i1
l1	i2
l1	i3



3、left outer join

select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta left outer join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu ;
l1	q1	q1	i1
l1	q1	q1	i1
l1	q1	q1	i2
l1	q1	q1	i3
l1	q2	q2	i1
l1	q2	q2	i2

select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta left outer join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu group by label,inmyway; 
l1	i1
l1	i2
l1	i3

 

3、full outer  join

select *  from 
(select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta  
full outer  join (select qu,inmyway from temp_testjoin_tb  where dt = '2014-08-08') tb 
on ta.qu = tb.qu  ; 

l1      q1      q1      i3
l1      q1      q1      i2
l1      q1      q1      i1
l1      q1      q1      i1
l1      q2      q2      i2
l1      q2      q2      i1
NULL    NULL    q3      i10