SQLite Intersect Operator

SQLite is a server-less database engine written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is escaping complex database engines like MYSQL. It has become one of the most popular database engines as we use it in Television, Mobile Phones, Web browsers, and many more. It is written simply so that it can be embedded into other applications. In this article, we will learn about the SQLite Except operator, how it works, and the functionality of it.

SQLite Intersect Operator

The SQLite INTERSECT operator returns the common or intersection of two or more datasets. If the row exists in both data sets, then that will be included in the INTERSECT results. So, if the record does not exist in one of the datasets then it is not going to be considered in the results.

• The functionality of the SQLite operator is the same as the intersection in mathematics.
• The number of expressions or columns must be the same in both tables.
• Even the data types must be the same for the expressions that we are going to fetch.

Syntax:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Syntax Explanation: Multiple SELECT statements followed by the table names from which we are going to fetch the data, Where condition which is optional and here comes the INTERSECT operator applied on the datasets.

Visual Representation of Intersect Operator

SQLite Intersect Operator

Output:

SQLite Intersect Operator

Explanation: In the above image we can observe how the SQLite Intersect works. Here T1 and T2 are two tables and the intersect operator is used to retrieve the common rows.

Example of Intersect Operator in SQLite

Here we are going to use two tables Students_1 and students_2. Both the tables look like as shown below, each table consists of 4 rows with 4 columns. Below is the Students_1 table with 4 collumns and they are Id, name, fees and father_name.

Students_1

Here we can find the students_2 table below with the 3 columns and they are Id, name and fees

students_2

Example 1: Intersection Operator With Single Expression

Here we are going to fetch the single expression or the column from the tables. In this example we are going to fetch the NAME column from the tables.

Syntax:

SELECT expression1
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1
FROM tables
[WHERE conditions];

Query:

Let’s Identify the names of students who are present in both the Students_1 and students_2 tables

SELECT NAME FROM Students_1
INTERSECT 
SELECT NAME FROM students_2;

Output:

SQLite Single Intersection Operator

Explanation: Here we are going to fetch the name from both the tables Students_1 and students_2 by using the intersect operator inbetween two select statements. Now the name is fetched from both the tables and as it is a common column it is fetched.

Example 2: Intersection Operator With Multiple Expression

Here we are going to fetch the multiple expression or the columns from the tables. In this example we are going to fetch the name and fee column from the two tables.

Syntax:

SELECT expression1, expression2
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2
FROM tables
[WHERE conditions];

Query:

Let’s Identify students present in both Students_1 and students_2 based on matching names. We will Calculate below:

1. Total fees: Sum of fees for each common student across both tables.
2. Average fees: Average fee per student among those found in both tables.

SELECT name,fees FROM Students_1
INTERSECT
SELECT name,fees FROM students_2;

Output:

SQLite Multiple Intersection Operator

Explanation: Here we are going to fetch the name, fees from both the tables Students_1 and students_2. Now the name is fetched from both the tables and as they are common columns, they are fetched.

Example 3: Intersect Operator With Order By Clause

As we know that Intersect operator is used to retrieve the common data from the two tables and moreover we have learnt order by in our previous articles. Order by is used to arrange the result set in the specified order as written in the query.

Syntax:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
ORDER BY ASC/DESC;

Explanation: Here we are going to use two tables as we have two select statements and we are going to specify the tables from which we are going to fetch the data and we are going to intersect the two tables using the intersect operator. Finally the result set is arranged either by ascending or descending order.

Query:

Let’s Identify and rank (highest first) students paying in both Students_1 and students_2, based on combined fees from both tables.

SELECT name,fees FROM Students_1
INTERSECT
SELECT name,fees  FROM students_2
ORDER BY fees desc;

Output:

Intersect Operator With Order By

Explanation: Here we are going to fetch name, fees from the two tables and we are going to intersect them using the intersect operator as those are the common columns they are fetched and as we have four rows they are fetched in the descending order.

Conclusion

By the end of this article you will get to know, what does it mean by SQLite Intersect operator, how does it work. SQLite Intersect operator works same as the intersection that is in the mathematics and it retrieves the common records from the tables. In order to perform that, the expressions must have the similar data type with the equal number of records and even the number of columns must be equal inorder to fetch the output. It helps in retrieving the common columns from two tables at a time without fetching them separately.