SQLite Union Operator

  Total Views : 39
  Comments : 0
 
  Keywords : SQLite union clause with example, SQLite union operator with order by example, SQLite union operator with select statement example, SQLite union query with example

Here we will learn sqlite union operator with example and how to use sqlite union operator with order by clause to combine the result of multiple select statements and show only unique records with example.

SQLite Union Operator

In SQLite UNION operator is used to combine the result sets of 2 or more SELECT statements and it removes duplicate rows between the various SELECT statements.

 

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

Syntax of SQLite Union Operator

Following is the syntax of SQLite UNION operator  to combine multiple select statement result sets and return unique records.

 

SELECT expression1, expression2,... expression_n

FROM tables

[WHERE conditions]

 

UNION

 

SELECT expression1, expression2,... expression_n

FROM tables

[WHERE conditions];

If you observe above SQLite UNION syntax we are combing multiple select statements using UNION 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 operator its 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 operator by default remove all duplicate rows from the result set and the column names from the first SELECT statement in the UNION operator are used as the column names for the result set.

SQLite UNION Operator Example

We will see how to use SQLite UNION 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 operator to combine multiple select statement result sets and return unique elements.

 

SELECT dept_id

FROM dept_master

 

UNION

 

SELECT dept_id

FROM emp_master

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

 

dept_id

----------

2

4

1

2

If you observe above example SQLite UNION operator removed duplicate rows from result set.

SQLite UNION with Order By

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

 

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

 

SELECT expression1, expression2,... expression_n

FROM tables1

[WHERE conditions]

 

UNION

 

SELECT expression1, expression2,... expression_n

FROM tables1

[WHERE conditions] ORDER BY expression1

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

 

SELECT dept_id

FROM dept_master

 

UNION

 

SELECT dept_id

FROM emp_master order by dept_id;

If you observe above SQLite UNION 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

2

3

4

This is how we can use SQLite UNION operator to combine multiple select statement result sets and to return only UNIQUE elements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User