android sqlite union,SQLite基础学习

说明：

SQLite 的UNOIN 子句/运算符用于合并两个或多个SELECT语句的结果，不返回重复的行。

若用UNION，每个SELECT被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但可以的不同的长度。

假设有如下表student：

ID NAME AGE ADDRESS

---------- ---------- ---------- ----------

1 xiaoming 18 shenzhen

2 xiaohua 18 beijing

3 mingming 19 shanghai

4 xiaogang 20 hangzhou

5 honghong 17 shenzhen

6 liangliang 21 wuhan

7 tingting 23 shenzhen

和表2 region：

ID ADDRESS

---------- ----------

1 shenzhen

2 beijing

3 shanghai

UNION语法：

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

实例：

sqlite>select region.ID, NAME from student inner join region on student.ADDRESS=region.ADDRESS

union

select region.ID, NAME from student left outer join region on student.ADDRESS=region.ADDRESS;

ID NAME

---------- ----------

liangliang

xiaogang

1 honghong

1 tingting

1 xiaoming

2 xiaohua

3 mingming

UNION ALL语法：

UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。适用于 UNION 的规则同样适用于 UNION ALL 运算符。

语法如下：

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

实例：

sqlite>select region.ID, NAME from student inner join region on student.ADDRESS=region.ADDRESS

union all

select region.ID, NAME from student left outer join region on student.ADDRESS=region.ADDRESS;

ID NAME

---------- ----------

1 honghong

1 tingting

1 xiaoming

2 xiaohua

3 mingming

1 xiaoming

2 xiaohua

3 mingming

xiaogang

1 honghong

liangliang

1 tingting

1. 请实现UNION ALL的例子，并与UNION的例子进行比较。