联合查询
Ø Union :集合的并,不包括重复行
select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables;
Ø union all:集合的并,包括重复行
select field1, field2, .field_n from tables UNION ALL select field1, field2, field_n from tables;
Ø intersect :集合的交,不包括重复行
select field1, field2, field_n from tables INTERSECT select field1, field2, field_n from tables;
Ø minus:集合的差,不包括重复行
select field1, field2, field_n from tables MINUS select field1, field2, field_n from tables;
实例:
Id
Name
score
1
Aaron
78
2
Bill
76
3
Cindy
89
4
Damon
90
5
Ella
73
6
Frado
61
7
Gill
99
8
Hellen
56
9
Ivan
93
10
Jay
90
select * from student where id < 4 union select * from student where id > 2 and id < 6 的查询结果:
Id
Name
score
1
Aaron
78
2
Bill
76
3
Cindy
89
4
Damon
90
5
Ella
73
select * from student where id < 4 union All select * from student where id > 2 and id < 6的查询结果:
Id
Name
score
1
Aaron
78
2
Bill
76
3
Cindy
89
3
Cindy
89
4
Damon
90
5
Ella
73
select * from student where id < 4 intersect select * from student where id > 2 and id < 6的查询结果:
Id
Name
score
3
Cindy
89
select * from student where id < 4 minus select * from student where id > 2 and id < 6的查询结果:
Id
Name
score
1
Aaron
78
2
Bill
76
4
Damon
90
5
Ella
73
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/houlinghouling/archive/2009/01/09/3741174.aspx