?連接查詢分類

1.自連接查詢，對同一個表進行連接操作
2.內連接查詢，【又分為：自然連接（就是等值連接去除重復的行，或者說select 后面選擇要表示的列，而不是使用"*",列出所有列）、等值連接（就是where后使用"="來做邏輯判斷）、不等值連接三種】

【內連接時，返回的結果集僅是符合查詢條件和連接條件的行。】
3.外連接查詢，【又分為：左外連接、右外連接、全外連接三種】

【采用外連接時，它返回到結果集合不僅包含符合連接條件的行，而且還包括的指定外連接中表的所有數據行。】
4.交叉連接查詢，也作無條件查詢。

【返回表的笛卡爾集】
5.聯合查詢?

【返回的不是交集而是并集】

?示例表

t1

id name? sales date

1?los?1500?2011-01-05 00:00:00.000
2?san?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?

t2

id? sales? date

1?250?2011-01-07 00:00:00.000
2?535?2011-01-10 00:00:00.000
3?320?2011-01-11 00:00:00.000
4?750?2011-01-12 00:00:00.000

多表連接

1，自連接查詢

語法：【select?表1.字段名1，表2.字段名2，...?from?表1，表2? where?連接條件】

select * from t1 ,t2 where t1.sales=t2.sales

id?name?sales?date?id?sales?date
2?san?250?2011-01-07 00:00:00.000?1?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000

2.內連接查詢

語法：【select?表1.字段名1，表2.字段名2，...? FROM?表1??join_type?表2?[ON (連接條件)]】?

inner join

等值連接

select * from t1 inner join? t2 on t1.sales=t2.sales

id?name?sales?date?id?sales?date
2?san?250?2011-01-07 00:00:00.000?1?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000

非等值連接

select * from t1? inner join? t2 on t1.sales!=t2.sales

id?name?sales?date?id?sales?date
1?los?1500?2011-01-05 00:00:00.000?1?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?1?250?2011-01-07 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?1?250?2011-01-07 00:00:00.000
1?los?1500?2011-01-05 00:00:00.000?2?535?2011-01-10 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?2?535?2011-01-10 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?2?535?2011-01-10 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?2?535?2011-01-10 00:00:00.000
1?los?1500?2011-01-05 00:00:00.000?3?320?2011-01-11 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?3?320?2011-01-11 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000
1?los?1500?2011-01-05 00:00:00.000?4?750?2011-01-12 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?4?750?2011-01-12 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?4?750?2011-01-12 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?4?750?2011-01-12 00:00:00.000

自然連接

select t1.sales,t1.date from t1? inner join? t2 on t1.sales=t2.sales

sales?date
250?2011-01-07 00:00:00.000
320?2011-01-08 00:00:00.000

left join

select * from t1 left join? t2 on t1.sales=t2.sales

id?name?sales?date?id?sales?date
1?los?1500?2011-01-05 00:00:00.000?NULL?NULL?NULL
2?san?250?2011-01-07 00:00:00.000?1?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?NULL?NULL?NULL

right join

select * from t1 right join? t2 on t1.sales=t2.sales

id?name?sales?date?id?sales?date
2?san?250?2011-01-07 00:00:00.000?1?250?2011-01-07 00:00:00.000
NULL?NULL?NULL?NULL?2?535?2011-01-10 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000
NULL?NULL?NULL?NULL?4?750?2011-01-12 00:00:00.000

full outer join

select * from t1 full outer join? t2 on t1.sales=t2.sales

1?los?1500?2011-01-05 00:00:00.000?NULL?NULL?NULL
2?san?250?2011-01-07 00:00:00.000?1?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?NULL?NULL?NULL
NULL?NULL?NULL?NULL?2?535?2011-01-10 00:00:00.000
NULL?NULL?NULL?NULL?4?750?2011-01-12 00:00:00.000

cross join

select * from t1 cross join? t2

id?name?sales?date?id?sales?date
1?los?1500?2011-01-05 00:00:00.000?1?250?2011-01-07 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?1?250?2011-01-07 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?1?250?2011-01-07 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?1?250?2011-01-07 00:00:00.000
1?los?1500?2011-01-05 00:00:00.000?2?535?2011-01-10 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?2?535?2011-01-10 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?2?535?2011-01-10 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?2?535?2011-01-10 00:00:00.000
1?los?1500?2011-01-05 00:00:00.000?3?320?2011-01-11 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?3?320?2011-01-11 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?3?320?2011-01-11 00:00:00.000
1?los?1500?2011-01-05 00:00:00.000?4?750?2011-01-12 00:00:00.000
2?san?250?2011-01-07 00:00:00.000?4?750?2011-01-12 00:00:00.000
3?los?320?2011-01-08 00:00:00.000?4?750?2011-01-12 00:00:00.000
4?boston?800?2011-01-08 00:00:00.000?4?750?2011-01-12 00:00:00.000

?

union 指令表示將多個表合并顯示，并不是連接顯示。union只是把結果集并集起來，而不是交集出來。

union的限制是多個表的列必須是相同的種類

union 并起來的結果集自動執行distincd，去除重復的列。

union all 則把重復列保留，完整顯示多個結果集的并集

下面看實例?

執行：select date from t1 union all? select date from t2

date
2011-01-05 00:00:00.000
2011-01-07 00:00:00.000
2011-01-08 00:00:00.000
2011-01-08 00:00:00.000
2011-01-07 00:00:00.000
2011-01-10 00:00:00.000
2011-01-11 00:00:00.000
2011-01-12 00:00:00.000?

可以看出完整的現實了8個行

執行：select date from t1 union?? select date from t2

date
2011-01-05 00:00:00.000
2011-01-07 00:00:00.000
2011-01-08 00:00:00.000
2011-01-10 00:00:00.000
2011-01-11 00:00:00.000
2011-01-12 00:00:00.000

只顯示6個行，有重復日期的7，8月份都只保留了一個唯一值

如果執行：select date,sales from t1 union?? select date,sales from t2

date?sales
2011-01-05 00:00:00.000?1500
2011-01-07 00:00:00.000?250
2011-01-08 00:00:00.000?320
2011-01-08 00:00:00.000?800
2011-01-10 00:00:00.000?535
2011-01-11 00:00:00.000?320
2011-01-12 00:00:00.000?750

可以看出7月份，sales為250的數據有重復，被去除了，只保留了一個

如果執行：select date,sales from t1 union all? select date,sales from t2

date sales

2011-01-05 00:00:00.000?1500
2011-01-07 00:00:00.000?250
2011-01-08 00:00:00.000?320
2011-01-08 00:00:00.000?800
2011-01-07 00:00:00.000?250
2011-01-10 00:00:00.000?535
2011-01-11 00:00:00.000?320
2011-01-12 00:00:00.000?750

轉載于:https://www.cnblogs.com/fslnet/archive/2011/12/29/2306132.html

總結

以上是生活随笔為你收集整理的数据库的几种联结，union，union all ，inner jion ，left jion，right jion ，cross jion的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。