INTERSECT

(show you how to use the INTERSECT operator to find common rows of two or more queries.)

inTRODUCTION

In MySQL, the INTERSECT operator acts like a data sieve, helping you find rows that are common between the results of two or more SELECT statements. Here's a breakdown of its basic syntax in plain language:

INTERSECT in Action:

Imagine you have two lists:

• List 1: Contains your favorite movies.
• List 2: Contains movies your friend recommends.

You want to find movies that you both enjoy (present in both lists). INTERSECT helps you identify these matches.

Basic INTERSECT Syntax:

SELECT ... FROM table1
INTERSECT
SELECT ... FROM table2;

• SELECT ...: This represents the individual SELECT statements that retrieve data.
• FROM table1, table2: These are the tables from which data is fetched.
• INTERSECT: This keyword is the core of the operation. It compares the results of the two SELECT statements.

Key Points:

• Finding Matches: INTERSECT returns rows that are present in both table1 and table2 based on a comparison of all columns involved (similar to a set intersection).
• Matching Columns (Strict): Unlike EXCEPT, INTERSECT requires a stricter match. The SELECT statements must have the same number of columns in the same order, and the data types of corresponding columns must be exactly the same (not just compatible).
• Duplicate Removal (Default): INTERSECT eliminates duplicate rows in the final result set by default (similar to UNION DISTINCT).
• Keeping Duplicates (Optional): If you want to include all matching rows, including duplicates, use INTERSECT ALL instead of just INTERSECT.

Example:

Suppose you have tables for products and categories:

To find product names that belong to a specific category (present in both products and categories with matching category IDs):

SELECT 
     product_name 
FROM  
     products
INTERSECT
SELECT 
     category_name 
FROM 
     categories
WHERE 
     category_id = 10; 

This query will identify product names that are categorized as "category_id 10" in both the products and categories tables.