SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。
UNION 的基本语法如下：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">SELECT column1 </span><span class="pun">[,</span><span class="pln"> column2 </span><span class="pun">]</span><span class="pln"> FROM table1 </span><span class="pun">[,</span><span class="pln"> table2 </span><span class="pun">]</span> <span class="pun">[</span><span class="pln">WHERE condition</span><span class="pun">]</span><span class="pln"> UNION SELECT column1 </span><span class="pun">[,</span><span class="pln"> column2 </span><span class="pun">]</span><span class="pln"> FROM table1 </span><span class="pun">[,</span><span class="pln"> table2 </span><span class="pun">]</span> <span class="pun">[</span><span class="pln">WHERE condition</span><span class="pun">]</span> |
这里给定的条件根据需要可以是任何表达式。
假设有下面两个表，（1）COMPANY 表如下所示：
|
1 2 3 4 5 6 7 8 9 10 |
<span class="pln">sqlite</span><span class="pun">></span> <span class="kwd">select</span> <span class="pun">*</span> <span class="kwd">from</span><span class="pln"> COMPANY</span><span class="pun">;</span><span class="pln"> ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">--------------------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
（2）另一个表是 DEPARTMENT，如下所示：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID DEPT EMP_ID </span><span class="pun">----------</span> <span class="pun">--------------------</span> <span class="pun">----------</span> <span class="lit">1</span><span class="pln"> IT </span><span class="typ">Billing</span> <span class="lit">1</span> <span class="lit">2</span> <span class="typ">Engineering</span> <span class="lit">2</span> <span class="lit">3</span> <span class="typ">Finance</span> <span class="lit">7</span> <span class="lit">4</span> <span class="typ">Engineering</span> <span class="lit">3</span> <span class="lit">5</span> <span class="typ">Finance</span> <span class="lit">4</span> <span class="lit">6</span> <span class="typ">Engineering</span> <span class="lit">5</span> <span class="lit">7</span> <span class="typ">Finance</span> <span class="lit">6</span> |
现在，让我们使用 SELECT 语句及 UNION 子句来连接两个表，如下所示：
|
1 2 3 4 5 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT EMP_ID</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY</span><span class="pun">.</span><span class="pln">ID </span><span class="pun">=</span><span class="pln"> DEPARTMENT</span><span class="pun">.</span><span class="pln">EMP_ID UNION SELECT EMP_ID</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY</span><span class="pun">.</span><span class="pln">ID </span><span class="pun">=</span><span class="pln"> DEPARTMENT</span><span class="pun">.</span><span class="pln">EMP_ID</span><span class="pun">;</span> |
这将产生以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">EMP_ID NAME DEPT </span><span class="pun">----------</span> <span class="pun">--------------------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span><span class="pln"> IT </span><span class="typ">Billing</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="typ">Engineerin</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="typ">Engineerin</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="typ">Finance</span> <span class="lit">5</span> <span class="typ">David</span> <span class="typ">Engineerin</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="typ">Finance</span> <span class="lit">7</span> <span class="typ">James</span> <span class="typ">Finance</span> |
UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
适用于 UNION 的规则同样适用于 UNION ALL 运算符。
UNION ALL 的基本语法如下：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">SELECT column1 </span><span class="pun">[,</span><span class="pln"> column2 </span><span class="pun">]</span><span class="pln"> FROM table1 </span><span class="pun">[,</span><span class="pln"> table2 </span><span class="pun">]</span> <span class="pun">[</span><span class="pln">WHERE condition</span><span class="pun">]</span><span class="pln"> UNION ALL SELECT column1 </span><span class="pun">[,</span><span class="pln"> column2 </span><span class="pun">]</span><span class="pln"> FROM table1 </span><span class="pun">[,</span><span class="pln"> table2 </span><span class="pun">]</span> <span class="pun">[</span><span class="pln">WHERE condition</span><span class="pun">]</span> |
这里给定的条件根据需要可以是任何表达式。
现在，让我们使用 SELECT 语句及 UNION ALL 子句来连接两个表，如下所示：
|
1 2 3 4 5 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT EMP_ID</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY</span><span class="pun">.</span><span class="pln">ID </span><span class="pun">=</span><span class="pln"> DEPARTMENT</span><span class="pun">.</span><span class="pln">EMP_ID UNION ALL SELECT EMP_ID</span><span class="pun">,</span><span class="pln"> NAME</span><span class="pun">,</span><span class="pln"> DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY</span><span class="pun">.</span><span class="pln">ID </span><span class="pun">=</span><span class="pln"> DEPARTMENT</span><span class="pun">.</span><span class="pln">EMP_ID</span><span class="pun">;</span> |
这将产生以下结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span class="pln">EMP_ID NAME DEPT </span><span class="pun">----------</span> <span class="pun">--------------------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span><span class="pln"> IT </span><span class="typ">Billing</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="typ">Engineerin</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="typ">Engineerin</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="typ">Finance</span> <span class="lit">5</span> <span class="typ">David</span> <span class="typ">Engineerin</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="typ">Finance</span> <span class="lit">7</span> <span class="typ">James</span> <span class="typ">Finance</span> <span class="lit">1</span> <span class="typ">Paul</span><span class="pln"> IT </span><span class="typ">Billing</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="typ">Engineerin</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="typ">Engineerin</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="typ">Finance</span> <span class="lit">5</span> <span class="typ">David</span> <span class="typ">Engineerin</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="typ">Finance</span> <span class="lit">7</span> <span class="typ">James</span> <span class="typ">Finance from:<a href="http://www.runoob.com/sqlite/sqlite-unions-clause.html">http://www.runoob.com/sqlite/sqlite-unions-clause.html</a></span> |