SQLite UNION ALL Operator

  Total Views : 34
  Comments : 0
 
  Keywords : SQLite union all order by in select statement with example, SQLite union all clause with example, How to use sqlite union all query to combine multiple select statements with example

Here we will learn how to use SQLite UNION All operator with example and how to use SQLite UNION ALL operator to combine and return multiple select statement result sets including duplicate values with example. 

SQLite UNION ALL Operator

In SQLite UNION ALL operator is used to combine the result sets of 2 or more SELECT statements and it will return all the rows including duplicates.

 

The SELECT statements which we used with UNION ALL operator must have the same number of fields in the result sets with similar data types.

Syntax of SQLite Union ALL Operator

Following is the syntax of SQLite UNION ALL operator  to combine multiple select statement result sets and return all records including duplicates.

 

SELECT expression1, expression2,... expression_n

FROM tables

[WHERE conditions]

 

UNION ALL

 

SELECT expression1, expression2,... expression_n

FROM tables

[WHERE conditions];

If you observe above SQLite UNION ALL syntax we are combing multiple select statements using UNION ALL operator and we used some properties those are

 

expression1, expression2, ... expression_n - The columns or calculations that you wish to retrieve.

 

tables - The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

 

WHERE conditions - Optional. The conditions that must be met for the records to be selected.

 

While using SQLite UNION ALL operator it’s important to note that all the SELECT statements must contain same number of columns or expressions with the same data type and that columns order also must be same.

 

The SQLite UNION ALL operator will use column names from the first SELECT statement as the column names for the result set.

SQLite UNION ALL Operator Example

We will see how to use SQLite UNION ALL operator with example. For this example, we need create two tables dept_master and emp_master and need to insert some data for that use following queries.

 

To create and insert some data in dept_master table execute following query.

 

CREATE TABLE dept_master

(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT);

 

INSERT INTO dept_master(dept_name)

VALUES('Admin'),

('Sales'),

('Quality Control');

('Marketing');

Once we create and insert data execute following query to see the data of dept_master table.

 

sqlite> SELECT * FROM dep_master;

 

dept_id     dept_name

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

1           Admin

2           Sales

3           Quality Control

4           Marketing

Same way execute following queries to create and insert some data in emp_master table.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

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

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

Now write SQLite query like as shown following to use UNION ALL operator to combine multiple select statement result sets and return all the records.

 

SELECT dept_id

FROM dept_master

 

UNION ALL

 

SELECT dept_id

FROM emp_master

Now we will run and see the output of SQLite UNION ALL operator example that will be like as shown below.

 

dept_id

----------

 

4

1

2

2

1

1

2

3

3

2

3

3

If you observe above example SQLite UNION ALL operator returned all the records from both the select statements including duplicate records. 

SQLite UNION ALL with Order By

Generally, the SQLite UNION ALL operator will combine multiple select statement result sets and return all the records including dulicates. So if we want to use Order By clause with UNION ALL operator we need define Order By at the end of last Select statement.

 

Following is the syntax of using SQLite Union ALL with Order By in Select statement.

 

SELECT expression1, expression2,... expression_n

FROM tables1

[WHERE conditions]

 

UNION ALL

 

SELECT expression1, expression2,... expression_n

FROM tables1

[WHERE conditions] ORDER BY expression1

Now we will see the example of using SQLite UNION ALL operator with Order By in select statement.

 

SELECT dept_id

FROM dept_master

 

UNION ALL

 

SELECT dept_id

FROM emp_master order by dept_id;

If you observe above SQLite UNION ALL operator query we used Order By clause at the end of select statement. Now we will run and see the output that will be like as shown below.

 

dept_id

----------

1

1

1

2

2

2

2

3

3

3

3

4

If you observe above result it included all the duplicate dept_id of both the tables.

 

This is how we can use SQLite UNION ALL operator to combine multiple select statement result sets and to return ALL the records including duplicate ones.

 

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User