SQL INTERSECT Operator - Scaler Topics

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.

intersect-operation-of-two-queries

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. example-table-1

To create this table in MySQL, you can use the below-given code:

example-table-2

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:

output-table-1

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-

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:

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.

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.