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.
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.
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.
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
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
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.