SQLite Intersect Operator

Here we will learn SQLite Intersect operator with example and how to use SQLite Intersect operator to get only matching rows from two or more select statements with examples. 

SQLite Intersect Operator

In SQLite, an Intersect operator is useful to get only matching rows from two or more select statements. Suppose if we use Intersect operator with two SQLite Select statements then it will return only the records which exist in both Select statement result sets.

 

Generally, we use SQLite Intersect operator with Select statements and all the Select statements which are using with Intersect operator must have the same number of fields.

 

Following is the pictorial representation of SQLite Intersect operator.

 

sqlite intersect operator example diagram

 

If you observe the above SQLite Intersect operator diagram it will return the records which are in a color shaded area that means Intersect operator will return only the matching records from both Select statements.

Syntax of SQLite Intersect Operator

Following is the syntax of using SQLite Intersect operator with select statement.

 

SELECT column1, column2,..., columnn

FROM table-list

[WHERE condition]

 

INTERSECT

 

SELECT column1, column2,..., columnn

FROM table-list

[WHERE condition];

If you observe above SQLite Intersect operator syntax we defined two Select statements with Intersect operator. Here SQLite Intersect operator will return only matching rows from both Select statements. 

 

In the above Intersect operator syntax, we defined some of the properties that are

 

  • columns - It may be no. of column or expression that you want as a result. 
  • table-list - It may be a list of the table from which you want results. 
  • Where Condition - Its optional. We can use this condition based on our requirements in Select statements.

SQLite Intersect Operator Example

We will see how to use SQLite Intersect operator with SELECT statement for that first create one table called dept_master as follows.

 

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');

Same way create emp_master table and insert some records like as shown below.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER references dept_master);

 

INSERT INTO emp_master

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

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

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

(4,'Jagruti','Viras', 9500,12);

Now we will see how to use SQLite Intersect operator with single expressions in Select statements for that write SQLite query like as shown following.

 

SELECT dept_id

FROM dept_master

 

INTERSECT

 

SELECT dept_id

FROM emp_master

When we run above sqlite Intersect operator query it will return dept_id from the dept_master table which exists in the emp_master table as shown below.

 

dept_id

----------

1

2

3

SQLite Intersect with Multiple Columns

In case if we have multiple columns in SQLite Select statement then the Intersect operator will use all the columns of the first Select statement to compare values with the second Select statement.

 

Following is the example of using SQLite Intersect operator with multiple columns in Select statement. 

 

SELECT dept_id, dept_name

FROM table1

 

INTERSECT

 

SELECT dept_id, dept_name

FROM table2

Here above SQLite Intersect operator will return records of table1 which are not matched with dept_id, dept_name column values of table2.

 

This is how we can use the SQLite Intersect operator to get records from Select statements based on our requirements.

Previous
 
Next