表的连接分成好几种类型。
- 内连接(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|
+-----+------+