w3resource

SQLite UNION

Description

In SQLite, the UNION operator is used to combine the result from multiple SELECT statements into a single result set.

The default characteristic of UNION is, to remove the duplicate rows from the result. The DISTINCT keyword which is optional does not make any effect, because, by default, it specifies duplicate-row removal. But if we use the optional keyword ALL, the duplicate-row removal does not happen and the result set includes all matching rows from all the SELECT statements.

Syntax:

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

Pictorial presentation of UNION operator

The UNION operator returns result from both queries after eliminating the duplicate rows.

sqlite union operator image

Sample Tables

employees:


job_history:


Example:

If we want to display the present and previous details of jobs of all employees once the following sqlite statement can be used.

Select employee_id, job_id
FROM employees
UNION
Select employee_id,job_id
FROM job_history;

Relational Algebra Expression:

Relational Algebra Expression: SQLite UNION.

Relational Algebra Tree:

Relational Algebra Tree: SQLite UNION.

Sample Output:

employee_id  job_id
-----------  ----------
100          AD_PRES
101          AC_ACCOUNT
101          AC_MGR
101          AD_VP
102          AD_VP
102          IT_PROG
103          IT_PROG
104          IT_PROG
105          IT_PROG
106          IT_PROG
107          IT_PROG
108          FI_MGR
109          FI_ACCOUNT
110          FI_ACCOUNT
111          FI_ACCOUNT
112          FI_ACCOUNT
113          FI_ACCOUNT
114          PU_MAN
114          ST_CLERK
115          PU_CLERK
116          PU_CLERK
117          PU_CLERK
118          PU_CLERK
119          PU_CLERK
120          ST_MAN
121          ST_MAN
122          ST_CLERK
122          ST_MAN
123          ST_MAN
124          ST_MAN
125          ST_CLERK
126          ST_CLERK
127          ST_CLERK
128          ST_CLERK
129          ST_CLERK
130          ST_CLERK
131          ST_CLERK
132          ST_CLERK
133          ST_CLERK
134          ST_CLERK
135          ST_CLERK
136          ST_CLERK
137          ST_CLERK
138          ST_CLERK
139          ST_CLERK
140          ST_CLERK
141          ST_CLERK
142          ST_CLERK
143          ST_CLERK
144          ST_CLERK
145          SA_MAN
146          SA_MAN
147          SA_MAN
148          SA_MAN
149          SA_MAN
150          SA_REP
151          SA_REP
152          SA_REP
153          SA_REP
154          SA_REP
155          SA_REP
156          SA_REP
157          SA_REP
158          SA_REP
159          SA_REP
160          SA_REP
161          SA_REP
162          SA_REP
163          SA_REP
164          SA_REP
165          SA_REP
166          SA_REP
167          SA_REP
168          SA_REP
169          SA_REP
170          SA_REP
171          SA_REP
172          SA_REP
173          SA_REP
174          SA_REP
175          SA_REP
176          SA_MAN
176          SA_REP
177          SA_REP
178          SA_REP
179          SA_REP
180          SH_CLERK
181          SH_CLERK
182          SH_CLERK
183          SH_CLERK
184          SH_CLERK
185          SH_CLERK
186          SH_CLERK
187          SH_CLERK
188          SH_CLERK
189          SH_CLERK
190          SH_CLERK
191          SH_CLERK
192          SH_CLERK
193          SH_CLERK
194          SH_CLERK
195          SH_CLERK
196          SH_CLERK
197          SH_CLERK
198          SH_CLERK
199          SH_CLERK
200          AC_ACCOUNT
200          AD_ASST
201          MK_MAN
201          MK_REP
202          MK_REP
203          HR_REP
204          PR_REP
205          AC_MGR
206          AC_ACCOUNT

Pictorial presentation of output

sqlite union operator image

Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table, so it comes once in the output but the other employee_id and job_id are different in both the tables, so they come each.

SQLite UNION ALL

The UNION ALL operator does not eliminate duplicate selected rows and returns all rows.

Pictorial presentation of UNION ALL operator

The UNION ALL operator returns all the rows from both the queries and no duplication elimination happens.

sqlite union operator image

SQLite UNION vs UNION ALL

In SQLite the UNION operator returns the combined result from multiple SELECT statements into a single result set but exclude the duplicate rows where as the UNION ALL operator avoids the elimination of duplicate selected rows and returns all rows.

See the example below.

Example:

If we want to display the present and previous details of jobs of all employees, and they may appear more than once, the following sqlite statement can be used.

Select employee_id, job_id,department_id
FROM employees
UNION ALL
Select employee_id,job_id,department_id
FROM job_history;

Relational Algebra Expression:

Relational Algebra Expression: SQLite UNION ALL.

Relational Algebra Tree:

Relational Algebra Tree: SQLite UNION ALL.

Sample Output:

employee_id  job_id      department_id
-----------  ----------  -------------
100          AD_PRES     90
101          AD_VP       90
102          AD_VP       90
103          IT_PROG     60
104          IT_PROG     60
105          IT_PROG     60
106          IT_PROG     60
107          IT_PROG     60
108          FI_MGR      100
109          FI_ACCOUNT  100
110          FI_ACCOUNT  100
111          FI_ACCOUNT  100
112          FI_ACCOUNT  100
113          FI_ACCOUNT  100
114          PU_MAN      30
115          PU_CLERK    30
116          PU_CLERK    30
117          PU_CLERK    30
118          PU_CLERK    30
119          PU_CLERK    30
120          ST_MAN      50
121          ST_MAN      50
122          ST_MAN      50
123          ST_MAN      50
124          ST_MAN      50
125          ST_CLERK    50
126          ST_CLERK    50
127          ST_CLERK    50
128          ST_CLERK    50
129          ST_CLERK    50
130          ST_CLERK    50
131          ST_CLERK    50
132          ST_CLERK    50
133          ST_CLERK    50
134          ST_CLERK    50
135          ST_CLERK    50
136          ST_CLERK    50
137          ST_CLERK    50
138          ST_CLERK    50
139          ST_CLERK    50
140          ST_CLERK    50
141          ST_CLERK    50
142          ST_CLERK    50
143          ST_CLERK    50
144          ST_CLERK    50
145          SA_MAN      80
146          SA_MAN      80
147          SA_MAN      80
148          SA_MAN      80
149          SA_MAN      80
150          SA_REP      80
151          SA_REP      80
152          SA_REP      80
153          SA_REP      80
154          SA_REP      80
155          SA_REP      80
156          SA_REP      80
157          SA_REP      80
158          SA_REP      80
159          SA_REP      80
160          SA_REP      80
161          SA_REP      80
162          SA_REP      80
163          SA_REP      80
164          SA_REP      80
165          SA_REP      80
166          SA_REP      80
167          SA_REP      80
168          SA_REP      80
169          SA_REP      80
170          SA_REP      80
171          SA_REP      80
172          SA_REP      80
173          SA_REP      80
174          SA_REP      80
175          SA_REP      80
176          SA_REP      80
177          SA_REP      80
178          SA_REP
179          SA_REP      80
180          SH_CLERK    50
181          SH_CLERK    50
182          SH_CLERK    50
183          SH_CLERK    50
184          SH_CLERK    50
185          SH_CLERK    50
186          SH_CLERK    50
187          SH_CLERK    50
188          SH_CLERK    50
189          SH_CLERK    50
190          SH_CLERK    50
191          SH_CLERK    50
192          SH_CLERK    50
193          SH_CLERK    50
194          SH_CLERK    50
195          SH_CLERK    50
196          SH_CLERK    50
197          SH_CLERK    50
198          SH_CLERK    50
199          SH_CLERK    50
200          AD_ASST     10
201          MK_MAN      20
202          MK_REP      20
203          HR_REP      40
204          PR_REP      70
205          AC_MGR      110
206          AC_ACCOUNT  110
102          IT_PROG     60
101          AC_ACCOUNT  110
101          AC_MGR      110
201          MK_REP      20
114          ST_CLERK    50
122          ST_CLERK    50
200          AD_ASST     90
176          SA_REP      80
176          SA_MAN      80
200          AC_ACCOUNT  90

Pictorial presentation of output

sqlite union operator image

Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table and they all have appeared in the result set. Here UNION ALL have to avoid the elimination of duplicate rows.

SQLite UNION DISTINCT

The DISTINCT clause with UNION produced nothing extra as the simple UNION done. From the UNION operator, we know that all rows will be displayed from both the queries except the duplicate are once.

Example:

If we want to display the present and previous details of jobs of all employees once the following sqlite statement can be used.

SELECT DISTINCT employee_id, job_id
FROM employees
UNION
SELECT DISTINCT employee_id,job_id
FROM job_history;

Output:

The result will be same as the UNION operator do.

SQLite UNION ORDER BY

The ORDER BY clause with UNION arrange the rows in the result set in a specific order. The default order is ascending. The ORDER BY only used at the very end of the statement.

Example:

If we want to display the present and previous details of jobs of all employees once the following sqlite statement can be used.

SELECT  employee_id, job_id
FROM employees
UNION
SELECT  employee_id,job_id
FROM job_history
ORDER BY employee_id;

Output:

The result will be same as the UNION operator do.

employee_id  job_id
-----------  ----------
100          AD_PRES
101          AC_ACCOUNT
101          AC_MGR
101          AD_VP
102          AD_VP
102          IT_PROG
103          IT_PROG
104          IT_PROG
105          IT_PROG
106          IT_PROG
107          IT_PROG
108          FI_MGR
109          FI_ACCOUNT
110          FI_ACCOUNT
111          FI_ACCOUNT
112          FI_ACCOUNT
113          FI_ACCOUNT
114          PU_MAN
114          ST_CLERK
115          PU_CLERK
116          PU_CLERK
117          PU_CLERK
118          PU_CLERK
119          PU_CLERK
120          ST_MAN
121          ST_MAN
122          ST_CLERK
122          ST_MAN
123          ST_MAN
124          ST_MAN
125          ST_CLERK
126          ST_CLERK
127          ST_CLERK
128          ST_CLERK
129          ST_CLERK
130          ST_CLERK
131          ST_CLERK
132          ST_CLERK
133          ST_CLERK
134          ST_CLERK
135          ST_CLERK
136          ST_CLERK
137          ST_CLERK
138          ST_CLERK
139          ST_CLERK
140          ST_CLERK
141          ST_CLERK
142          ST_CLERK
143          ST_CLERK
144          ST_CLERK
145          SA_MAN
146          SA_MAN
147          SA_MAN
148          SA_MAN
149          SA_MAN
150          SA_REP
151          SA_REP
152          SA_REP
153          SA_REP
154          SA_REP
155          SA_REP
156          SA_REP
157          SA_REP
158          SA_REP
159          SA_REP
160          SA_REP
161          SA_REP
162          SA_REP
163          SA_REP
164          SA_REP
165          SA_REP
166          SA_REP
167          SA_REP
168          SA_REP
169          SA_REP
170          SA_REP
171          SA_REP
172          SA_REP
173          SA_REP
174          SA_REP
175          SA_REP
176          SA_MAN
176          SA_REP
177          SA_REP
178          SA_REP
179          SA_REP
180          SH_CLERK
181          SH_CLERK
182          SH_CLERK
183          SH_CLERK
184          SH_CLERK
185          SH_CLERK
186          SH_CLERK
187          SH_CLERK
188          SH_CLERK
189          SH_CLERK
190          SH_CLERK
191          SH_CLERK
192          SH_CLERK
193          SH_CLERK
194          SH_CLERK
195          SH_CLERK
196          SH_CLERK
197          SH_CLERK
198          SH_CLERK
199          SH_CLERK
200          AC_ACCOUNT
200          AD_ASST
201          MK_MAN
201          MK_REP
202          MK_REP
203          HR_REP
204          PR_REP
205          AC_MGR
206          AC_ACCOUNT

Previous: Delete
Next: Collating Sequences



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sqlite/sqlite-union.php