SQL INTERSECT

SQL INTERSECT

The INTERSECT operator in SQL returns the common rows (intersection) between two or more SELECT queries. The output includes only rows that appear in both result sets.

Why Use SQL INTERSECT?

• To find common data between multiple tables or queries.
• To identify overlapping datasets in complex queries.
• Useful in scenarios where you need matching rows from different datasets.

Syntax

SELECT column1, column2, ...
FROM table1
WHERE condition
INTERSECT
SELECT column1, column2, ...
FROM table2
WHERE condition;

Key Points

1. Same Number of Columns: Both SELECT statements must return the same number of columns.
2. Compatible Data Types: The corresponding columns in the SELECT statements must have similar data types.
3. Duplicates Removed: INTERSECT eliminates duplicates in the final result set by default.

Example Dataset

Students Table

StudentID	Name	Course
1	John Smith	Math
2	Alice Lee	Science
3	Bob Brown	History
4	Emma Davis	Math

Enrolled Table

StudentID	Name	Course
2	Alice Lee	Science
3	Bob Brown	History
4	Emma Davis	Math
5	Harry Jones	Art

Examples

1. Basic INTERSECT

Find students who are listed in both the Students and Enrolled tables.

SELECT StudentID, Name, Course
FROM Students
INTERSECT
SELECT StudentID, Name, Course
FROM Enrolled;

Result:

StudentID	Name	Course
2	Alice Lee	Science
3	Bob Brown	History
4	Emma Davis	Math

2. INTERSECT with Conditions

Find students enrolled in Math who appear in both tables.

SELECT StudentID, Name, Course
FROM Students
WHERE Course = 'Math'
INTERSECT
SELECT StudentID, Name, Course
FROM Enrolled
WHERE Course = 'Math';

Result:

StudentID	Name	Course
4	Emma Davis	Math

3. INTERSECT on Partial Columns

You can intersect specific columns (e.g., only StudentID):

SELECT StudentID
FROM Students
INTERSECT
SELECT StudentID
FROM Enrolled;

Result:

StudentID
2
3
4

Equivalent Query Without INTERSECT

You can achieve the same result using a JOIN:

SELECT s.StudentID, s.Name, s.Course
FROM Students s
INNER JOIN Enrolled e
ON s.StudentID = e.StudentID
   AND s.Name = e.Name
   AND s.Course = e.Course;

While INTERSECT simplifies the syntax, this approach might be required in databases that don’t support INTERSECT.

Difference Between INTERSECT and Other Set Operators

Feature	INTERSECT	UNION	EXCEPT (MINUS)
Purpose	Common rows	Combine results	Rows in the first query but not in the second
Removes Duplicates	Yes	Yes	Yes
Order of Execution	Not relevant	Not relevant	First query only

Database Support

• Supported by:
  • SQL Server
  • PostgreSQL
  • Oracle
• Not natively supported in MySQL (can use JOIN as a workaround).

Common Use Cases

1. Identifying Overlapping Data:

   • Example: Find customers who purchased from both Store A and Store B.

2. Consistency Checks:

   • Example: Verify that entries in two datasets are identical.

3. Data Cleanup:

   • Example: Identify records duplicated across tables.

Performance Considerations

1. Indexes: Ensure indexed columns are used in the queries for faster execution.
2. Duplicates: Removing duplicates incurs additional overhead; consider using JOIN for larger datasets.
3. Query Optimization: Use filtering conditions (WHERE) to limit the data before applying INTERSECT.

Conclusion

The SQL INTERSECT operator simplifies queries where you need to find common rows between datasets. While it requires compatible columns and data types, its simplicity and clarity make it a valuable tool for data analysis.