SQLite Unions child clause

SQLite's UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

In order to use UNION, the number of columns selected in each SELECT must be the same, the same number of column expressions, the same data type, and make sure they have the same order, but they do not have to have the same length .

Syntax

The basic syntax of UNION is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

The conditions given here can be any expression as needed.

Example

Suppose there are the following two tables, (1) The COMPANY table is as follows:

sqlite> select * from COMPANY;
ID            AGE ------ ----------
1                                                                                                                                                                 through         through               through             through 15000.0
3                                                            65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000. 0
7 James 24 Houston 10000.0

(2) Another table is department, as shown below:

# ID DEPT EMP_ID
---------------------------------------------------------------- ----------- ----------
1 IT Billing 3
5 Finance 4
6 Engineering 5
7 Finance 6

Now, let us join the two tables using SELECT statement and UNION clause as shown below:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT

ON COMPANY.ID = DEPARTMENT.EMP_ID

UNION

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

This will produce the following results:

EMP_ID NAME NAME DEPT
-------------------------- ---- ----------
1 Paul IT Billing
2 Allen Engineerin
3 Engineerin
4 Mark Finance
7 Finance

UNION ALL clause

UNION ALL operator is used to combine the results of two SELECT statements, including duplicate rows.

The rules that apply to UNION also apply to the UNION ALL operator.

Syntax

The basic syntax of UNION ALL

is as follows:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

The conditions given here can be any expression as needed.

Example

Now, let us use the SELECT statement and UNION ALL clause to join the two tables, as shown below:

sqlite> SELECT EMP_ID, NAME , DEPT FROM COMPANY INNER JOIN DEPARTMENT

ON COMPANY.ID = DEPARTMENT.EMP_ID

UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

这将产生以下结果：

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineerin
3           Teddy                 Engineerin
4           Mark                  Finance
5           David                 Engineerin
6           Kim                   Finance
7           James                 Finance
1           Paul                  IT Billing
2           Allen                 Engineerin
3           Teddy                 Engineerin
4           Mark                  Finance
5           David                 Engineerin
6           Kim                   Finance
7           James                 Finance

← SQLite Joins

SQLite NULL value →

submit

SQLite Unions child clause

Syntax

Example

UNION ALL operator is used to combine the results of two SELECT statements, including duplicate rows.

Now, let us use the SELECT statement and UNION ALL clause to join the two tables, as shown below:

php.cn