sparksql : 数据库表连接的简单解释

表的连接分成好几种类型。

• 内连接（inner join）
• 外连接（outer join）
• 左连接（left join）
• 右连接（right join）
• 全连接（full join）
• LEFT ANTI JOIN

• 只返回两张表匹配的记录，这叫内连接（inner join）。
• 返回匹配的记录，以及表 A 多余的记录，这叫左连接（left join）。
• 返回匹配的记录，以及表 B 多余的记录，这叫右连接（right join）。
• 返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全连接（full join）

 

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

这四种连接，又可以分成两大类：内连接（inner join）表示只包含匹配的记录，外连接（outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。

这四种连接的 SQL 语句如下。

SELECT * FROM A  
INNER JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
LEFT JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
RIGHT JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
FULL JOIN B ON A.book_id=B.book_id;

上面的 SQL 语句还可以加上where条件从句，对记录进行筛选，比如只返回表 A 里面不匹配表 B 的记录。

SELECT * FROM A
LEFT JOIN B
ON A.book_id=B.book_id
WHERE B.id IS null;

另一个例子，返回表 A 或表 B 所有不匹配的记录。

SELECT * FROM A
FULL JOIN B
ON A.book_id=B.book_id
WHERE A.id IS null OR B.id IS null;

此外，还存在一种特殊的连接，叫做"交叉连接"（cross join），指的是表 A 和表 B 不存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张包含 n x m 条记录的新表（见下图）

left semi join 

以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录

相当于in或者exists，left semi join只会取左表的字段，右边的字段只用来做判断，不会取出来

SELECT a.id,
a.name 
FROM lxw1234_a a 
LEFT SEMI JOIN lxw1234_b b 
ON (a.id = b.id);
 
--执行结果：
1       zhangsan
2       lisi
 
--等价于：
SELECT a.id,
a.name 
FROM lxw1234_a a 
WHERE a.id IN (SELECT id FROM lxw1234_b);
 
 
--也等价于：
SELECT a.id,
a.name 
FROM lxw1234_a a 
join lxw1234_b b 
ON (a.id = b.id);
 
--也等价于：
SELECT a.id,
a.name 
FROM lxw1234_a a 
WHERE EXISTS (SELECT 1 FROM lxw1234_b b WHERE a.id = b.id);

left anti join

含义：left anti join 是 not in/not exists 子查询的一种更高效的实现

相当于not in或者not exists，left anti join只会取左表的字段，右边的字段只用来做判断，不会取出来

select *
from   t1
left anti join t2
on     t1.id = t2.id
;
相当于

select *
from   t1
where  t1.id not in (select id from t2)
;

select *
from   t1
where  not exists (select 1 from t2 whre t2.id = t1.id)
;


t1
id	name
1	张三
2	李四
3	王五

t2
id	name
2	李四
3	王五
4	赵六

结果：

id	name
1	张三

LEFT ANTI JOIN

Anti Join的结果集是left join时，以左表为基准，当右表中不存在可以匹配关联条件的记录时，输出左表记录

SELECT * FROM employees
+-------+------+
|   name|salary|
+-------+------+
|Michael|  3000|
|   Andy|  4500|
| Justin|  3500|
|  Berta|  4000|
+-------+------+
SELECT * FROM people
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
|  21|   Lily|
+----+-------+



SELECT * FROM employees left anti join people on people.name = employees.name
结果：

+-----+------+
| name|salary|
+-----+------+
|Berta|  4000|
+-----+------+