多表查詢
假如有兩張表:
表A:
表B:
這里我們是通過(guò)相同列名departmentNumber來(lái)進(jìn)行條件查詢相關(guān)信息:
1.內(nèi)連接查詢:返回的結(jié)果只有滿足departmentNumber相等的結(jié)果
sql語(yǔ)句(有兩種方式):
```
-->select a.name, b.name from A as a, B as b where a.departmentNumber = b.departmentNumber
-->select a.name, b.name from A as a?inner join?B as b on a.departmentNumber = b.departmentNumber
```
查詢結(jié)果:
2.左外鏈接查詢:返回的結(jié)果只和左邊的表有關(guān)(這里假定A是左邊的表),右邊表如果沒(méi)有對(duì)應(yīng)值,就為NULL
sql語(yǔ)句:
```
-->select a.name, b.name from A as a?left outer join?B as b on a.departmentNumber= b.departmentNumber
```
返回結(jié)果:
3.右外鏈接查詢:返回的結(jié)果只和右邊的表有關(guān)(這里假定B是右表),那么如果左邊表是沒(méi)有對(duì)應(yīng)值,就為NULL
sql語(yǔ)句:
```
-->select a.name, b.name from A as a?right OUTER join?B as b on a.departmentNumber = b.departmentNumber
```
返回結(jié)果:
4.完全外鏈接查詢:想獲取兩個(gè)表所有信息,如果兩個(gè)表內(nèi)沒(méi)有對(duì)應(yīng)值的就用NULL表示
sql語(yǔ)句:
```
-->select a.name, b.name from B as b?full OUTER join?A as a on a.departmentNumber = b.departmentNumber
```
返回結(jié)果:
注意點(diǎn):在sqlite中是不支持右外連接和完全外鏈接的,但是可以通過(guò)以下方式來(lái)進(jìn)行連接:
1.sqlite中的右外連接:將左外連接的表?yè)Q個(gè)順序就可以了
```
-->select a.name, b.name from B as B?left outer join?A as A on a.departmentNumber = b.departmentNumber
```
返回結(jié)果:
2.sqlite中的完全外鏈接:用關(guān)鍵字union將兩句連接起來(lái)進(jìn)行合并
```
-->select a.name, b.name from B as b left OUTER A as a on a.departmentNumber = b.departmentNumber
UNION
select a.name, b.name from A as a left outer B as b on a.departmentNumber = b.departmentNumber
```
返回結(jié)果:
