java 联合查询,联合查询 使用方法

联合查询

Ø  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

posted on 2009-06-17 15:28 鲁胜迪 阅读(126) 评论(0)  编辑  收藏