SQL INTERSECT Operator
FREE
In SQL, the INTERSECT operator combines the result sets of two or more SELECT statements, returning only the rows that appear in all result sets. This can be useful for finding common rows between data sets or performing set operations in SQL. For example, if you have two tables with similar data and want to find the rows that are common to both tables, you can use INTERSECT to combine the results of two SELECT statements that retrieve the data from each table.
To use the INTERSECT in SQL, certain conditions need to be met.
- The number of columns and their order from both SELECT statements should be the same.
- The data types from both SELECT statements should be compatible.
Syntax
The expression refers to columns you must extract from the table through SELECT statements. The WHERE statement is optional and can be used to filter the result further.
Example
Suppose we have been given two tables below.
To create this table in MySQL, you can use the below-given code:
The Code of the given table is given below:
Now, we are asked tond countries that import and export. So this meansThisuire a SELECT statement from both tables.
Now, we need to add the intersect operator, and we will have our answers. So, the final Query would be.
The output would of the above query be:
INTERSECT with BETWEEN Operator
We can use Intersect in SQL between operators to specify some columns with specific values in the range. Let's Understand it better using below example-
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Example
Output
| Name |
|---|
| India |
| China |
| Japan |
| France |
Explanation: The above SQL query selects the names of countries from the CountryExporting table where the ID falls between 1 and 5, and also between 2 and 6. The INTERSECT operator combines the results of both SELECT statements, returning only the names that appear in both result sets. Hence, the output of the above query will be common in both conditional queries, which will be the names of countries with IDs 2,3,4,5.
INTERSECT with IN Operator
The IN operator is used to select some column that is present in the mentioned list using the In operator. We can use the Intersect in sql with In in different scenarios like the below example:
Turn Learning into Career Growth
Example
Output
| Name |
|---|
| India |
Explanation: In the above SQL query selects the names of countries that export both 'Wheat' and either 'Planes' or 'Medicines', as well as 'Wheat' and 'Electronics'. It uses the INTERSECT operator to find the common results between the two SELECT statements. The output of the above query will be item shipping common in both the query that will be names of the countries that ship Wheat as the item.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
INTERSECT with LIKE Operator
We can use the Like operator with Intersect in SQL to find common rows that match a given pattern.
Example
Output
| Name |
|---|
| China |
Explanation: In the above example, the output of the first subquery will be a row with id 3, and for the second subquery, the output will be rows with id 1,3 and 5. Hence, The output of the above query will be itemshipping common in both queries, which will be the names of countries that ship Electornics as the item.
Conclusion
- The Intersect operator in SQL is applied on two SELECT statements. It returns all rows that are common from both SELECT statements.
- To apply the Intersect operator in SQL, the number of columns returned by both SELECT statements should be the same, and their data types should be compatible.
- Intersect in sql works on the row while AND operator works on columns.
- Intersect operator can be combined with other SQL operators to get data as per-user convenience.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more