R7—左右内全连接详解
在SQL查询中，经常会用到左连接、右连接、内连接、全连接，那么在R中如何实现这些功能，今天来讲一讲！
SQL回顾
原理
# 连接可分为以下几类：
内连接。（典型的连接运算，使用像 = 或 <> 之类的比较运算符）。包括相等连接和自然连接。
内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。
外连接。外连接可以是左向外连接、右向外连接或完整外部连接。
在FROM子句中指定外连接时，可以由下列几组关键字中的一组指定：
LEFT JOIN 或 LEFT OUTER JOIN。
左向外连接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN。
完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。
示例分析。
注：a.id和parent_id之间有关系！
| a表 | b表 |
|
id name |
id job parent_id |
内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
|
id name id job parent_id 1 张3 1 23 1 |
左连接
select a.*,b.* from a left join b on a.id=b.parent_id
|
id name id job parent_id 1 张3 1 23 1 |
右连接
select a.*,b.* from a right join b on a.id=b.parent_id
|
id name id job parent_id 1 张3 1 23 1 |
完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
|
id name id job parent_id 1 张3 1 23 1 |
总结
LEFT JOIN 会从左表(a表)那里返回所有的记录，即使在右表(b表)中没有匹配的行。
RIGHT OUTER JOIN 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配。
FULL OUTER JOIN 全连接, 返回左右表中的所有记录。
R实现
R里面实现连接的函数最基本的是base包中的merge函数（专门针对数据框，对应于SQL中的表），操作非常简单，下面做一下简要分析！
merge函数语法
# Merge Two Data Frames：Merge two data frames by common columns or row names, or do other versions of database join operations。
# Usage
merge(x, y, ...) ## Default S3 method:
merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all= FALSE, all.x = all,all.y = all,sort = TRUE, suffixes = c(".x",".y"),incomparables= NULL,...) ## S3 method for class 'data.frame'(蓝色高亮为常用参数
# Arguments（参数说明）
x,y：数据框 | by,by.x,by.y：指定用于合并连接(merge)的列 | all,all.x,all.y：指定连接的方式 | sort：逻辑值,输出结果按 by指定的列排序吗？ | suffixes： | incomparables：
# details（细节说明）
默认下，merge函数会以内连接(即自然连接：data frames are merged on the columns with names they both have)的方式合并数据框。by.x和by.y指定用于连接的列。all系列指定连接的方式：all=FALSE指定自然连接(默认), all.x=TRUE指定左连接, all.y=TRUE指定右连接, all=TRUE指定全连接！
# 示例
注：surname和name有关系！
| authors | books |
|
surname nationality deceased 1 Tukey US yes 2 Venables Australia no 3 Tierney US no 4 Ripley UK no 5 McNeil Australia no
|
name title other.author 1 Tukey Exploratory Data Analysis <NA> 2 Venables Modern Applied Statistics ... Ripley 3 Tierney LISP-STAT <NA> 4 Ripley Spatial Statistics <NA> 5 Ripley Stochastic Simulation <NA> 6 McNeil Interactive Data Analysis <NA> 7 R Core An Introduction to R Venables & Smith |
# 内连接：指定参数all=FALSE,并按by.x排序（sort=TRUE指定）
|
> merge(x = authors,y = books,by.x = 'surname',by.y='name',all=FALSE,sort = TRUE ) surname nationality deceased title other.author 1 McNeil Australia no Interactive Data Analysis <NA> 2 Ripley UK no Spatial Statistics <NA> 3 Ripley UK no Stochastic Simulation <NA> 4 Tierney US no LISP-STAT <NA> 5 Tukey US yes Exploratory Data Analysis <NA> 6 Venables Australia no Modern Applied Statistics ... Ripley
|
#左连接：以books为左表,author为右表进行左连接
|
> merge(x = books,y = authors, by.x = 'name',by.y='surname',all.x=TRUE,sort = TRUE ) name title other.author nationality deceased 1 McNeil Interactive Data Analysis <NA> Australia no 2 R Core An Introduction to R Venables & Smith <NA> <NA> 3 Ripley Spatial Statistics <NA> UK no 4 Ripley Stochastic Simulation <NA> UK no 5 Tierney LISP-STAT <NA> US no 6 Tukey Exploratory Data Analysis <NA> US yes 7 Venables Modern Applied Statistics ... Ripley Australia no
|
#右连接：右连接和左连接类似，直接略过！
#全连接：两个表中的公共连接字段都返回,未匹配到的返回为NA！
| > merge(x = books,y = authors, by.x = 'name',by.y='surname',all=TRUE,sort = TRUE ) name title other.author nationality deceased 1 McNeil Interactive Data Analysis <NA> Australia no 2 R Core An Introduction to R Venables & Smith <NA> <NA> 3 Ripley Spatial Statistics <NA> UK no 4 Ripley Stochastic Simulation <NA> UK no 5 Tierney LISP-STAT <NA> US no 6 Tukey Exploratory Data Analysis <NA> US yes 7 Venables Modern Applied Statistics ... Ripley Australia no |
#扩展：incomparable参数的使用
merge函数不仅可以进行通常的SQL连接, 还可以执行行连接,返回两个表中某两列对应行相等时的行观测值，这个可以通过by=参数来指定（其实，取的是交集）！
| x表 | y表 |
k1 k2 data
1 NA 1 1
2 NA NA 2
3 3 NA 3
4
4 4
4 55 5 5 |
k1 k2 data 1 NA NA 1 2 2 NA 2 3 NA 3 3 4 4 4 4 55 5 5 |
# 匹配x和y表中k1、k2字段下值对应相等时的观测值（它的原理应该是：先取k1、k2向量的交集,返回交集对应的行号，然后再根据行号从两个表中取观测值！）
> merge(x, y, by = c("k1","k2")) #NA’s match
k1 k2 data.x data.y
1 4 4 4 4
2 5 5 5 5
3 NA NA 2 1
|
# 只匹配k1，即两个表中，返回k1对应的公共字段对应对应行号的观测值！
> merge(x, y, by = "k1") k1 k2.x data.x k2.y data.y 1 4 4 4 4 4 2 5 5 5 5 5 3 NA 1 1 NA 1 4 NA 1 1 3 3 5 NA NA 2 NA 1 6 NA NA 2 3 3 |
# 使用incomparable参数，可以看到：incomparable=NA去掉了所有包含NA的观测值！
> merge(x, y, by = "k2", incomparables = NA) k2 k1.x data.x k1.y data.y 1 4 4 4 4 4 2 5 5 5 5 5 |
补充：
数学中集合论中的交并补集在R中怎么实现呢？这里简要补充一下！
union：并集，intercept：交集，setdiff：补集，setequal：比较两个集合是否相等
例子
| > (x <- c(sort(sample(1:20, 9)), NA)) ： [1] 4 5 6 10 12 15 17 19 20 NA > (y <- c(sort(sample(3:23, 7)), NA)) ： [1] 3 4 7 9 12 20 22 NA > union(x, y) ： [1] 4 5 6 10 12 15 17 19 20 NA 3 7 9 22 > intersect(x, y) ： [1] 4 12 20 NA > setdiff(x, y) ： [1] 5 6 10 15 17 19 > setdiff(y, x) ： [1] 3 7 9 22 > setequal(x, y) ： [1] FALSE |
