![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - INTERSECT
In the set theory, the intersection of two sets is a collection of values that are common to both sets. In other words, the intersection of two sets is a set of elements that exist in both sets. Let us consider the below tables as an example to get a better understanding −
![Intersect](https://www.tutorialspoint.com/sql/images/intersect.jpg)
If we perform the intersection operation on both sets using the INTERSECT operator, it returns the common rows which are 5 and 8. This operator removes the duplicate rows from the final result set.
INTERSECT in SQL
The INTERSECT operator in SQL is used to retrieve the records that are identical/common between the result sets of two SELECT (tables) statements.
In real-time scenarios, there will be a huge number of tables in a database that contains information. The user may find it challenging to gather common information from various tables. So we use the INTERSECT operator to accomplish that. It helps to retrieve the common data from various tables.
Syntax
To retrieve identical records from two different tables, we use the following syntax −
SELECT column1, column2,…, columnN FROM table1, table2,…, tableN INTERSECT SELECT column1, column2,…, columnN FROM table1, table2,…, tableN
Note − There are some mandatory rules for INTERSECT operations such as the number of columns, data types, and other columns must be the same in both SELECT statements for the INTERSECT operator to work correctly.
Example
First of all, let us create a table named “STUDENTS” using the following query −
SQL> CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, HOBBY VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY(ID) );
Once the table is created, let us insert some values to the table using the query below −
SQL> INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, 'Vijay', 'Cricket', 18); INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(2, 'Varun', 'Football', 26); INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(3, 'Surya', 'Cricket', 19); INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(4, 'Karthik', 'Cricket', 25); INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(5, 'Sunny', 'Football', 26); INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(6, 'Dev', 'Cricket', 23);
Let us verify whether the table “STUDENTS” is created or not using the following query −
SQL> SELECT * FROM STUDENTS;
As we can see in the below output, the table has been created in the database.
+-----+----------+--------------+-------+ | ID | NAME | HOBBY | AGE | +-----+----------+--------------+-------+ | 1 | Vijay | Cricket | 18 | | 2 | Varun | Football | 26 | | 3 | Surya | Cricket | 19 | | 4 | Karthik | Cricket | 25 | | 5 | Sunny | Football | 26 | | 6 | Dev | Cricket | 23 | +-----+----------+--------------+-------+
Let us create another table “ASSOCIATES” using the following query −
SQL> CREATE TABLE ASSOCIATES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, SUBJECT VARCHAR(20) NOT NULL, AGE INT NOT NULL, HOBBY VARCHAR(20) NOT NULL, PRIMARY KEY(ID) );
Let’s insert some values into the table using the following query −
SQL> INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1, 'Naina', 'Maths', 24, 'Cricket'); INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(2, 'Varun', 'Physics', 26, 'Football'); INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(3, 'Dev', 'Maths', 23, 'Cricket'); INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(4, 'Priya', 'Physics', 25, 'Cricket'); INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(5, 'Aditya', 'Chemistry', 21, 'Cricket'); INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(6, 'Kalyan', 'Maths', 30, 'Football');
Let us verify whether the table “ASSOCIATES” is created or not using the following query −
SQL> SELECT * FROM ASSOCIATES;
As we can see in the below output, the table has been created in the database.
+-----+----------+---------------+-------+----------+ | ID | NAME | SUBJECT | AGE | HOBBY | +-----+----------+---------------+-------+----------+ | 1 | Naina | Mathematics | 24 | Cricket | | 2 | Varun | Physics | 26 | Football | | 3 | Dev | Mathematics | 23 | Cricket | | 4 | Priya | Physics | 25 | Cricket | | 5 | Adithya | Chemistry | 21 | Cricket | | 6 | Kalyan | Mathematics | 30 | Football | +-----+----------+--------------+-------+-----------+
Now, we are trying to retrieve the common records from both the tables using the following query −
SQL> SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES
Output
When we execute the above query, the output is obtained as follows −
+-----------+-------+----------+ | NAME | AGE | HOBBY | +-----------+-------+----------+ | Dev | 23 | Cricket | | Varun | 26 | Football | +-----------+-------+----------+
INTERSECT with BETWEEN operator
As we discussed in the initial syntax, we can also use the INTERSECT operator along with conditional operators.
We can use the INTERSECT operator with the BETWEEN operator in SQL to find rows that fall within a specified range.
Example
Now, let us retrieve the records that are common in both tables. In addition; we are retrieving the records who are aged between 25 and 30 using the following query.
SQL> SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE AGE BETWEEN 25 AND 30 INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES WHERE AGE BETWEEN 20 AND 30
Output
The output for the above query is produced as given below −
+-----------+-------+----------+ | NAME | AGE | HOBBY | +-----------+-------+----------+ | Varun | 26 | Football | +-----------+-------+----------+
INTERSECT with IN operator
We can also use the INTERSECT operator with the IN operator in SQL to find the common rows that have the specified values. The IN operator is used to filter a result set based on a list of specified values.
Example
Here, we are trying to retrieve the common records from both tables. In addition; we are using the IN operator to retrieve the records whose hobby is ‘Cricket’.
SQL> SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE HOBBY IN('Cricket') INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES WHERE HOBBY IN('Cricket')
Output
When we execute the above query, the output is obtained as follows −
+-----------+-------+----------+ | NAME | AGE | HOBBY | +-----------+-------+----------+ | Dev | 26 | Cricket | +-----------+-------+----------+
INTERSECT with LIKE operator
The LIKE operator is used to perform pattern matching on a string. The INTERSECT operator can also be used with the LIKE operator in SQL to find the common rows that matches with the specified pattern.
Example
Let us use the wildcard ‘%’ with the LIKE operator to retrieve the names which starts with ‘v’ from the common names of both tables.
SQL> SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE NAME LIKE 'v%' INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES WHERE NAME LIKE 'v%'
Output
The output for the above query is produced as given below −
+-----------+-------+----------+ | NAME | AGE | HOBBY | +-----------+-------+----------+ | Varun | 26 | Football | +-----------+-------+----------+