假設有如下兩張表:
A:
id | age |
---|---|
1 | 11 |
2 | 12 |
3 | 13 |
4 | 14 |
B:
id | name |
---|---|
2 | aaa |
3 | bbb |
4 | ccc |
5 | ddd |
內連線 (inner join)
只有一種應用場景,inner join或join,等同於inner join
select a.,b. from a inner join(join) b on a.id = b.id
結果如下:
| id | age | id | name |
| 2 | 12 | 2 | aaa |
| 3 | 13 | 3 | bbb |
| 4 | 14 | 4 | ccc |
外連線
1.left join 或者 left outer join
select a.,b. from a left join b on a.id = b.id
結果如下:
| id | age | id | name |
| 1 | 11 | null | null |
| 2 | 12 | 2 | aaa |
| 3 | 13 | 3 | bbb |
| 4 | 14 | 4 | ccc |
2.left join + where b.column is null
select a.,b. from a left join b on a.id = b.id where b.id is null
結果如下:
| id | age | id | name |
| 1 | 11 | null | null |
3.right join 或者right out join
select a.,b. from a right join b on a.id = b.id
| id | age | id | name |
| 2 | 12 | 2 | aaa |
| 3 | 13 | 3 | bbb |
| 4 | 14 | 4 | ccc |
| null | null | 5 | ddd |
4.left join + where b.column is null
select a.,b. from a right join b on a.id = b.id where a.id is null
| id | age | id | name |
| null | null | 5l | ddd |
5.left join union right join (可用full join 但mysql不支援)
select a.id aid,a.age,b.id bid,b.name from a
left join b
on a.id = b.id
union
select a.id aid,a.age,b.id bid,b.name from a
right join b
on a.id = b.id
| aid | age | bid | name |
| 1 | 11 | null | null |
| 2 | 12 | 2 | aaa |
| 3 | 13 | 3 | bbb |
| 4 | 14 | 4 | ccc |
| null | null | 5 | ddd |
6.(left join + is null) union (right join + is null)
select a.id aid,a.age,b.id bid,b.name from a
left join b
on a.id = b.id
where b.id is null
union
select a.id aid,a.age,b.id bid,b.name from a
right join b
on a.id = b.id
where a.id is null
| aid | age | bid | name |
| 1 | 11 | null | null |
| null | null | 5 | ddd |
交叉連線 (cross join)
實際應用中還有這樣一種情形,想得到A,B記錄的排列組合,即笛卡兒積,這個就不好用集合和元素來表示了。需要用到cross join。
笛卡爾積:笛卡爾乘積是指在數學中,兩個集合X和Y的笛卡尓積(Cartesian product),又稱直積,表示為X × Y,第一個物件是X的成員而第二個物件是Y的所有可能有序對的其中一個成員。
select a.id aid,a.age,b.id bid,b.name from tablea a
cross join tableb b
| aid | age | bid | name |
| 1 | 11 | 2 | aaa |
| 2 | 12 | 2 | aaa |
| 3 | 13 | 2 | aaa |
| 4 | 14 | 2 | aaa |
| 1 | 11 | 3 | bbb |
| 2 | 12 | 3 | bbb |
| 3 | 13 | 3 | bbb |
| 4 | 14 | 3 | bbb |
| 1 | 11 | 4 | ccc |
| 2 | 12 | 4 | ccc |
| 3 | 13 | 4 | ccc |
| 4 | 14 | 4 | ccc |
| 1 | 11 | 5 | ddd |
| 2 | 12 | 5 | ddd |
| 3 | 13 | 5 | ddd |
| 4 | 14 | 5 | ddd |
還可以指定where條件
select a.id aid,a.age,b.id bid,b.name from a
cross join b
where a.id = b.id
aid | age | bid | name |
---|---|---|---|
2 | 12 | 2 | aaa |
3 | 13 | 3 | bbb |
4 | 14 | 4 | ccc |
注意事項:
-
一般cross join後面加上where條件,但是用cross join+on也是被解釋為cross join+where;
-
一般內連線都需要加上on限定條件,如內連線;如果不加會被解釋為交叉連線;
-
如果連線表格使用的是逗號,會被解釋為交叉連線;