SQLite Intersect Operator

  Total Views : 36
  Comments : 0
 
  Keywords : SQLite intersect clause with example, SQLite intersect in select statement with example, How to use sqlite intersect operator to exclude some of rows with example

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 Intersect operator is used 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 exists 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 same numer of fields.

 

Following is the pictorial representation of SQLite Intersect operator.

 

sqlite intersect operator example diagram

 

If you observe above SQLite Intersect operator diagram it will return the records which are in 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 above Intersect operator syntax we defined some of properties those are

 

columns - It may be no. of column or expression that you want as result.

 

table-list - It may be list of table from which you want result.

 

Where Condition - Its optional. We can use this condition based on our requirement 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 dept_master table which are exists in emp_master table like 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 Intersect operator will use all the columns of first Select statement to compare values with 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 match with dept_id, dept_name column values of table2.

 

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

 

NEXT

SQLite Joins  
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User