关于 Join
文章目录

关于 Join

Join = Inner Join
Left Join = Left Outer Join

关于 Join 的细节

Left Join 实际上是根据 qs 来判断的, 而下方的判断却是针对 s, 因此这里应该写成 join(也就是 inner join)

left join 就已经代表 left outer join 了

关于使用 Join 代替 Exists

对于 exists, 可以使用inner join来代替
对于 not exists, 可以使用left join followed by checking to make sure the column in the joined table is null.

Examples

*逗号表示单纯 JOIN 的意思, 也就是使用了 Inner Join
*

*Note: There’s no FULL OUTER JOIN in MySQL.
*

Suppose you have two tables, with a single column each, and data as follows:

1
2
3
4
5
6
A    B
------
1 3
2 4
3 5
4 6

Note 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.

1
2
3
4
5
6
7
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 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

1
2
3
4
5
6
7
8
9
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 | null
2 | null
3 | 3
4 | 4

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.

1
2
3
4
5
6
7
8
9
10
select * from a FULL OUTER JOIN b on a.a = b.b;

a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5