Previous

SQL INTERSECT

The INTERSECT operator in SQL is used to return the common rows between two SELECT queries. It retrieves the rows that are present in both result sets. Similar to the UNION operator, the INTERSECT operator combines results from multiple SELECT statements, but only the rows that appear in both queries will be returned.

 

Syntax of SQL INTERSECT

SELECT column1, column2, ...
FROM table1
WHERE condition
INTERSECT
SELECT column1, column2, ...
FROM table2
WHERE condition;
  • SELECT statements: The individual queries whose results you want to intersect.
  • INTERSECT: The operator used to find the common rows between the two result sets.
  • columns: Both SELECT statements must have the same number of columns, and the corresponding columns must have compatible data types.

 

Key Points

  • The INTERSECT operator returns only the common rows between two result sets.
  • INTERSECT removes duplicates by default. Each row returned will be unique.
  • The number of columns and their data types must be the same in both queries.
  • It can be used with conditions on both queries to refine the result set.

 

Example of SQL INTERSECT

Consider the following two tables:

Table 1: Employees (Department A)

employee_id name department
1 John HR
2 Alice HR
3 Bob HR

Table 2: Employees (Department B)

employee_id name department
2 Alice HR
3 Bob HR
4 Sarah IT

Using the INTERSECT operator, we can find employees who appear in both tables:

SELECT name, department
FROM employees_1
INTERSECT
SELECT name, department
FROM employees_2;

Output:

name department
Alice HR
Bob HR

Explanation:

  • The result set includes only the rows that are common to both employees_1 and employees_2. Alice and Bob are the only employees present in both tables.

 

SQL INTERSECT with Conditions

You can also apply conditions to both SELECT queries when using INTERSECT. For example, you might want to find employees who are in both tables and belong to a specific department:

SELECT name, department
FROM employees_1
WHERE department = 'HR'
INTERSECT
SELECT name, department
FROM employees_2
WHERE department = 'HR';

Output:

name department
Alice HR
Bob HR

 

Summary

The INTERSECT operator in SQL returns the rows that are common to both SELECT queries. It ensures that only the matching rows from both queries are returned. The queries must have the same number of columns, and the data types of the columns should be compatible. INTERSECT eliminates duplicates from the result, so only unique common rows are displayed.

Previous