Understanding INTERSECT and EXCEPT Support in MySQL Versions

Course: MySQL Training

Topic: Understanding INTERSECT and EXCEPT Support in MySQL Versions

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to INTERSECT and EXCEPT in MySQL

MySQL provides several SQL set operators that combine the results of multiple SELECT queries. The most familiar operator is UNION, which combines rows from multiple query results. INTERSECT and EXCEPT provide two additional ways to compare query results and retrieve specific records.

INTERSECT returns rows that appear in both query results, whereas EXCEPT returns rows that appear in the first query result but not in the second. These operators are useful for comparing datasets, identifying common records, finding differences between lists, and performing data validation.

MySQL added official support for INTERSECT and EXCEPT in MySQL 8.0.31. They are not supported as native set operators in older versions such as MySQL 5.7 and MySQL 8.0.30. MySQL official documentation.

1.1 Learning Objectives

• Understand SQL set operations and their purpose.
• Learn the version in which MySQL introduced INTERSECT and EXCEPT.
• Understand the syntax and working of INTERSECT.
• Understand the syntax and working of EXCEPT.
• Differentiate DISTINCT and ALL behavior.
• Compare INTERSECT and EXCEPT with UNION, JOIN, IN, and NOT EXISTS.
• Learn alternative query techniques for older MySQL versions.
• Understand query limitations, performance considerations, and common errors.

2. Understanding SQL Set Operations

A set operation combines the results of two or more query expressions into a single result. Each query participating in a set operation must return the same number of columns, and corresponding columns must have compatible data types.

2.1 Main SQL Set Operators

2.2 Simple Conceptual Example

Suppose two queries return the following sets of student IDs:

Query A: 101, 102, 103, 104

Query B: 103, 104, 105, 106

3. MySQL Version Support for INTERSECT and EXCEPT

Version compatibility is important because a query that works in a newer MySQL release may produce a syntax error in an older installation.

3.1 Version Compatibility Table

Important: Check the actual MySQL server version before using these operators. A graphical database tool or SQL editor does not determine server support; the connected server's version matters.

3.2 How to Check the Installed Version

SELECT VERSION();

Another option is to inspect the version variable:

SHOW VARIABLES LIKE 'version';

If the server reports a version earlier than 8.0.31, use the alternative approaches covered later in these notes.

3.3 Official MySQL Documentation

• Set Operations with UNION, INTERSECT, and EXCEPT
• INTERSECT Clause
• EXCEPT Clause

4. What Is the INTERSECT Operator?

INTERSECT is a SQL set operator that returns rows common to the result sets of two or more queries. By default, it removes duplicate rows from the final result.

It is useful when a business requirement involves finding records that satisfy two different query criteria simultaneously, even when those criteria are expressed in separate SELECT statements.

4.1 Basic Syntax

SELECT column1, column2
FROM table_a
WHERE condition_a

INTERSECT

SELECT column1, column2
FROM table_b
WHERE condition_b;

The column counts must match, and corresponding columns must have compatible data types.

4.2 Example: Finding Students Enrolled in Two Courses

Suppose a college stores enrollment information in separate tables for SQL and Python courses.

CREATE TABLE sql_students (
    student_id INT,
    student_name VARCHAR(100)
);

CREATE TABLE python_students (
    student_id INT,
    student_name VARCHAR(100)
);

INSERT INTO sql_students VALUES
(101, 'Aarav'),
(102, 'Priya'),
(103, 'Rahul'),
(104, 'Neha');

INSERT INTO python_students VALUES
(102, 'Priya'),
(103, 'Rahul'),
(105, 'Vikram');

To find students whose ID and name appear in both tables, run the following query on MySQL 8.0.31 or later:

SELECT student_id, student_name
FROM sql_students

INTERSECT

SELECT student_id, student_name
FROM python_students;

Expected result:

The result contains only the rows common to both query results. The database does not guarantee the displayed row order unless an appropriate ORDER BY clause is used.

4.3 INTERSECT with Filtering Conditions

Each query can have its own filtering conditions before the results are intersected.

SELECT customer_id
FROM orders
WHERE total_amount > 10000

INTERSECT

SELECT customer_id
FROM orders
WHERE order_date >= '2026-01-01';

This query finds customer IDs that appear in orders worth more than 10,000 and also appear in orders dated on or after January 1, 2026. Because both queries select from the same table, a customer may qualify through different orders. This query compares the sets of customer IDs, not necessarily the same individual order.

5. Understanding INTERSECT DISTINCT and INTERSECT ALL

MySQL supports both INTERSECT DISTINCT and INTERSECT ALL. If neither keyword is specified, DISTINCT behavior is used.

5.1 INTERSECT DISTINCT

INTERSECT DISTINCT returns common rows and removes duplicate copies from the result.

SELECT student_name
FROM sql_students

INTERSECT DISTINCT

SELECT student_name
FROM python_students;

This returns each matching student name once, even if duplicate names appear in either source result.

5.2 INTERSECT ALL

INTERSECT ALL preserves duplicates according to the number of occurrences shared by the two result sets. For each distinct row, the number of copies returned is the smaller of the occurrence counts in the left and right inputs.

Consider these query results:

Result A: Priya, Priya, Rahul

Result B: Priya, Priya, Priya, Rahul, Rahul

SELECT student_name FROM result_a

INTERSECT ALL

SELECT student_name FROM result_b;

The conceptual result contains two copies of Priya and one copy of Rahul, because those are the shared occurrence counts.

5.3 DISTINCT vs ALL

6. What Is the EXCEPT Operator?

EXCEPT is a SQL set operator that returns rows found in the first query result but absent from the second query result. It is useful for identifying missing records, unmatched entries, and differences between two datasets.

The order of the queries matters. A EXCEPT B is not generally the same as B EXCEPT A.

6.1 Basic Syntax

SELECT column1, column2
FROM table_a
WHERE condition_a

EXCEPT

SELECT column1, column2
FROM table_b
WHERE condition_b;

6.2 Example: Students Enrolled in SQL but Not Python

SELECT student_id, student_name
FROM sql_students

EXCEPT

SELECT student_id, student_name
FROM python_students;

Expected result:

The result contains students present in the SQL enrollment table whose complete selected rows do not appear in the Python enrollment table.

6.3 Reverse the EXCEPT Operation

SELECT student_id, student_name
FROM python_students

EXCEPT

SELECT student_id, student_name
FROM sql_students;

This query returns students present in the Python enrollment table but absent from the SQL enrollment table. In the sample data, that is Vikram, student ID 105.

7. Understanding EXCEPT DISTINCT and EXCEPT ALL

MySQL supports EXCEPT DISTINCT and EXCEPT ALL. DISTINCT is the default behavior.

7.1 EXCEPT DISTINCT

EXCEPT DISTINCT returns unique rows from the first result that do not appear in the second result.

SELECT student_name
FROM sql_students

EXCEPT DISTINCT

SELECT student_name
FROM python_students;

Duplicate copies in the final result are removed.

7.2 EXCEPT ALL

EXCEPT ALL performs multiset subtraction. For each distinct row, it returns the number of copies in the first result minus the number of copies in the second, with a minimum of zero.

For example:

Result A: Priya, Priya, Priya, Rahul

Result B: Priya, Rahul

Using EXCEPT ALL produces two copies of Priya and no copies of Rahul.

SELECT student_name FROM result_a

EXCEPT ALL

SELECT student_name FROM result_b;

7.3 DISTINCT vs ALL

8. Important Rules for INTERSECT and EXCEPT

8.1 Same Number of Columns

Both query expressions must return the same number of columns.

Incorrect example:

SELECT student_id, student_name
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students;

This query is invalid because the first SELECT returns two columns while the second returns one.

Correct example:

SELECT student_id
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students;

8.2 Compatible Data Types

Corresponding columns must have compatible data types. For example, a student ID represented as an integer can be compared with another integer ID. If the selected columns use different types, explicitly convert them when appropriate and ensure the comparison represents the intended business rule.

8.3 Column Position Matters

Set operators compare columns by their position in the SELECT list, not by their aliases.

SELECT student_id, student_name
FROM sql_students

INTERSECT

SELECT student_id, student_name
FROM python_students;

In this example, the first column is compared with the first column, and the second column is compared with the second column.

8.4 Column Names in the Final Result

The final result generally uses the column names from the first query expression. Use aliases in the first SELECT when clearer output headings are needed.

SELECT student_id AS common_student_id
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students;

8.5 Duplicate Removal by Default

Both INTERSECT and EXCEPT use DISTINCT behavior by default. Specify ALL when duplicate occurrence counts must be preserved.

8.6 Result Ordering

Set operations do not guarantee a particular output order. Apply ORDER BY to the final combined result when a specific order is required.

SELECT student_id, student_name
FROM sql_students

INTERSECT

SELECT student_id, student_name
FROM python_students
ORDER BY student_id;

9. Comparing UNION, INTERSECT, and EXCEPT

These operators solve different problems, even though all of them combine query results.

10. Using INTERSECT with Real-World Business Requirements

INTERSECT is useful when a business needs to identify records satisfying criteria represented by separate query results.

10.1 Customers Who Purchased Two Product Categories

Suppose an e-commerce system contains an orders table and an order_items table. The order items table records the products associated with each order.

SELECT o.customer_id
FROM orders AS o
INNER JOIN order_items AS oi
    ON oi.order_id = o.order_id
INNER JOIN products AS p
    ON p.product_id = oi.product_id
WHERE p.category_name = 'Electronics'

INTERSECT

SELECT o.customer_id
FROM orders AS o
INNER JOIN order_items AS oi
    ON oi.order_id = o.order_id
INNER JOIN products AS p
    ON p.product_id = oi.product_id
WHERE p.category_name = 'Books';

This example assumes the category name is available from the joined product data; if categories are stored in a separate table, join that table instead. The result contains customers who purchased Electronics and Books, possibly in different orders.

10.2 Employees Assigned to Two Projects

SELECT employee_id
FROM project_assignments
WHERE project_id = 10

INTERSECT

SELECT employee_id
FROM project_assignments
WHERE project_id = 20;

This query identifies employees assigned to both project 10 and project 20.

10.3 Products Available in Two Warehouses

SELECT product_id
FROM inventory
WHERE warehouse_id = 1
  AND stock_quantity > 0

INTERSECT

SELECT product_id
FROM inventory
WHERE warehouse_id = 2
  AND stock_quantity > 0;

This query identifies products that have positive stock in both warehouses.

11. Using EXCEPT with Real-World Business Requirements

EXCEPT is particularly useful for identifying records that are present in one dataset but absent from another.

11.1 Customers Who Registered but Never Ordered

SELECT customer_id
FROM customers

EXCEPT

SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL;

This query returns customer IDs from the customers table that do not appear among the non-NULL customer IDs in the orders table.

11.2 Products Never Included in an Order

SELECT product_id
FROM products

EXCEPT

SELECT product_id
FROM order_items
WHERE product_id IS NOT NULL;

This query identifies product IDs that are not present in the order-item data.

11.3 Employees Not Assigned to a Project

SELECT employee_id
FROM employees

EXCEPT

SELECT employee_id
FROM project_assignments
WHERE employee_id IS NOT NULL;

This query finds employees whose IDs do not appear in the project assignments table.

11.4 Records Missing from a New Dataset

During data migration, a team may want to identify customer IDs present in a source table but missing from the migrated table.

SELECT customer_id
FROM source_customers

EXCEPT

SELECT customer_id
FROM migrated_customers;

The result can help identify records that may have been omitted during migration. For a complete validation, compare the required columns as well as identifiers, because a matching ID alone does not prove that every field migrated correctly.

12. INTERSECT and EXCEPT Compared with JOINs

Set operations compare complete rows from query results, while joins combine rows from related tables based on a join condition. They can sometimes solve similar problems, but their behavior is not identical.

12.1 Finding Common Student IDs with INTERSECT

SELECT student_id
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students;

12.2 Finding Common Student IDs with INNER JOIN

SELECT DISTINCT s.student_id
FROM sql_students AS s
INNER JOIN python_students AS p
    ON p.student_id = s.student_id;

Both queries can identify common non-NULL student IDs in the example. The JOIN version explicitly uses equality to match IDs, while INTERSECT compares the complete selected rows according to set-operation semantics.

12.3 Important Difference

If you select both student ID and student name, INTERSECT compares both columns. If a student's ID is the same but the name differs between tables, the complete rows may not match. A JOIN on student ID, however, can match the IDs and return both names for comparison.

SELECT s.student_id,
       s.student_name AS sql_name,
       p.student_name AS python_name
FROM sql_students AS s
INNER JOIN python_students AS p
    ON p.student_id = s.student_id
WHERE s.student_name <> p.student_name;

This query identifies IDs present in both tables where the stored names differ, provided the names are non-NULL. If NULL names are possible, use a NULL-safe comparison such as NOT (s.student_name <=> p.student_name).

13. Alternatives for Older MySQL Versions

MySQL versions before 8.0.31 do not support INTERSECT and EXCEPT natively. You can often express the same business requirement with INNER JOIN, EXISTS, NOT EXISTS, or other query patterns.

13.1 Replacing INTERSECT with INNER JOIN

To find common student IDs:

SELECT DISTINCT s.student_id
FROM sql_students AS s
INNER JOIN python_students AS p
    ON p.student_id = s.student_id;

This approach is suitable when the requirement is to find IDs that exist in both tables. It does not automatically reproduce every duplicate-handling behavior of INTERSECT ALL.

13.2 Replacing INTERSECT with EXISTS

SELECT DISTINCT s.student_id
FROM sql_students AS s
WHERE EXISTS (
    SELECT 1
    FROM python_students AS p
    WHERE p.student_id = s.student_id
);

This query returns distinct non-NULL IDs from the SQL list that have a matching ID in the Python list. Equality does not match NULL to NULL, so NULL handling should be considered if nullable identifiers are possible.

13.3 Replacing EXCEPT with NOT EXISTS

SELECT DISTINCT s.student_id
FROM sql_students AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM python_students AS p
    WHERE p.student_id = s.student_id
);

This query finds distinct IDs in the SQL table for which no matching ID exists in the Python table.

13.4 Replacing EXCEPT with LEFT JOIN

SELECT DISTINCT s.student_id
FROM sql_students AS s
LEFT JOIN python_students AS p
    ON p.student_id = s.student_id
WHERE p.student_id IS NULL;

This is a common anti-join pattern. It returns IDs from the first table without a matching row in the second. It assumes p.student_id is non-NULL for actual matching rows, which is normally true for a student identifier.

13.5 Comparison of Alternatives

14. Understanding NULL Values in Set Operations

NULL represents a missing or unknown value. Set operations compare complete rows using set-operation semantics, which differ from ordinary equality expressions such as column = NULL.

14.1 NULL in INTERSECT

If the same row containing NULL in corresponding positions appears in both query results, the rows can be treated as duplicates or common rows by the set operation.

SELECT NULL AS value
INTERSECT
SELECT NULL AS value;

This returns one row containing NULL under the default DISTINCT behavior.

14.2 NULL in EXCEPT

SELECT NULL AS value
EXCEPT
SELECT NULL AS value;

This returns no rows because the NULL-containing row from the first result also appears in the second result.

14.3 NULL in Alternative JOIN Queries

When replacing a set operation with a JOIN or EXISTS, remember that ordinary equality does not match NULL values. If nullable values must match each other, use a suitable NULL-safe comparison, such as MySQL's <=> operator, where appropriate.

SELECT a.value
FROM table_a AS a
INNER JOIN table_b AS b
    ON a.value <=> b.value;

Use NULL-safe comparisons deliberately: they change the matching rules compared with ordinary equality.

15. Operator Precedence and Parentheses

MySQL gives INTERSECT higher precedence than UNION and EXCEPT. As a result, an expression containing several set operators may be evaluated in an order that is not immediately obvious.

15.1 Example

SELECT student_id FROM list_a
UNION
SELECT student_id FROM list_b
INTERSECT
SELECT student_id FROM list_c;

The INTERSECT operation is evaluated before UNION. The expression therefore behaves like:

SELECT student_id FROM list_a
UNION
(
    SELECT student_id FROM list_b
    INTERSECT
    SELECT student_id FROM list_c
);

15.2 Make Complex Expressions Explicit

When a query combines multiple set operators, use parentheses to show the intended grouping and improve readability.

(
    SELECT student_id FROM list_a
    UNION
    SELECT student_id FROM list_b
)
EXCEPT
SELECT student_id FROM list_c;

This query combines the first two lists and then removes IDs appearing in the third list.

16. ORDER BY and LIMIT with Set Operations

Use ORDER BY to sort the final combined result. A final LIMIT can restrict how many rows are returned after the set operation.

16.1 Sort the Final INTERSECT Result

SELECT student_id
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students
ORDER BY student_id;

16.2 Limit the Final Result

SELECT student_id
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students
ORDER BY student_id
LIMIT 10;

This returns at most ten matching IDs after sorting.

16.3 Using ORDER BY or LIMIT Inside Individual Query Blocks

If you need to apply ordering or limiting before a set operation, use parenthesized query expressions where supported by the installed MySQL version. A final ORDER BY and LIMIT apply to the combined result, while properly grouped inner clauses can limit individual inputs.

17. Performance Considerations

Set operations can be useful and concise, but performance depends on the number of rows, duplicate handling, data types, indexes, and the query plan. A shorter query is not necessarily faster than an equivalent JOIN or subquery.

17.1 Select Only Required Columns

Avoid selecting every column if only an identifier is required. Comparing fewer columns can reduce the amount of data that must be processed.

-- Avoid when only the customer ID is needed
SELECT *
FROM current_customers
INTERSECT
SELECT *
FROM previous_customers;

-- More focused
SELECT customer_id
FROM current_customers
INTERSECT
SELECT customer_id
FROM previous_customers;

These queries do not necessarily return equivalent results because the first compares every selected column while the second compares only customer IDs. Choose the query that reflects the actual requirement.

17.2 Index Join and Filter Columns

When using JOIN or EXISTS alternatives, indexes on matching identifiers can improve lookups.

CREATE INDEX idx_sql_students_student_id
ON sql_students (student_id);

CREATE INDEX idx_python_students_student_id
ON python_students (student_id);

Check the existing indexes before creating new ones. If student IDs are primary keys, additional indexes on the same columns may be unnecessary.

17.3 Use EXPLAIN

EXPLAIN
SELECT s.student_id
FROM sql_students AS s
WHERE EXISTS (
    SELECT 1
    FROM python_students AS p
    WHERE p.student_id = s.student_id
);

EXPLAIN helps you inspect the execution plan, including possible indexes, chosen access methods, and estimated row counts. Use the plan to guide optimization rather than assuming a particular operator is always faster.

17.4 Performance Best Practices

• Filter unnecessary rows as early as practical.
• Select only the columns needed for the comparison.
• Ensure corresponding columns use appropriate and compatible data types.
• Index columns used by JOIN or EXISTS alternatives when justified by query patterns.
• Use DISTINCT only when duplicate elimination is required.
• Test with realistic data volumes.
• Compare execution plans for equivalent query formulations.

18. Common Errors and Troubleshooting

19. Step-by-Step Method for Choosing a Set Operation

1. Identify the source result sets: Determine which queries produce the records to compare.
2. Define the requirement: Decide whether you need all combined rows, common rows, or rows missing from one result.
3. Choose the operator: Use UNION to combine, INTERSECT to find common rows, or EXCEPT to subtract the second result from the first.
4. Verify version compatibility: Confirm the server is MySQL 8.0.31 or later before using native INTERSECT or EXCEPT.
5. Align the columns: Ensure the SELECT statements return the same number of columns in the correct order.
6. Check data types: Confirm corresponding columns have compatible types.
7. Choose duplicate behavior: Use the default DISTINCT behavior or specify ALL when duplicate counts matter.
8. Handle NULL values: Review whether nullable columns affect comparisons or alternative query patterns.
9. Validate the results: Test with small examples and confirm the output matches the business requirement.
10. Optimize if needed: Use EXPLAIN and appropriate indexes for performance analysis.

20. Practice Exercises

Use the sql_students and python_students tables created earlier to complete the following exercises.

Exercise 1: Common Students

Write a query to find student IDs present in both course tables using INTERSECT.

Exercise 2: SQL-Only Students

Write a query to find students present in the SQL list but not in the Python list.

Exercise 3: Python-Only Students

Reverse the EXCEPT operation to find students present in the Python list but not in the SQL list.

Exercise 4: DISTINCT Behavior

Write a query that explicitly uses INTERSECT DISTINCT to find common student IDs.

Exercise 5: ALL Behavior

Create two test result sets containing duplicate values and demonstrate how INTERSECT ALL handles shared duplicate counts.

Exercise 6: Find Customers on Both Lists

Find customer IDs present in both a current customer list and a newsletter subscriber list.

Exercise 7: Identify Missing Customers

Find customer IDs that appear in a source customer table but not in a migrated customer table.

Exercise 8: Compare Employee Assignments

Find employees assigned to both project 10 and project 20.

Exercise 9: Older Version Alternative

Rewrite an INTERSECT query using INNER JOIN or EXISTS so that it works in MySQL 5.7.

Exercise 10: NULL Handling

Compare the results of a set operation with an ordinary JOIN when the compared columns contain NULL values.

21. Practice Exercise Solutions

Solution 1: Common Students

SELECT student_id
FROM sql_students

INTERSECT

SELECT student_id
FROM python_students;

Solution 2: SQL-Only Students

SELECT student_id
FROM sql_students

EXCEPT

SELECT student_id
FROM python_students;

Solution 3: Python-Only Students

SELECT student_id
FROM python_students

EXCEPT

SELECT student_id
FROM sql_students;

Solution 4: INTERSECT DISTINCT

SELECT student_id
FROM sql_students

INTERSECT DISTINCT

SELECT student_id
FROM python_students;

Solution 5: INTERSECT ALL Example

SELECT 'Priya' AS student_name
UNION ALL
SELECT 'Priya'
UNION ALL
SELECT 'Rahul'

INTERSECT ALL

SELECT 'Priya' AS student_name
UNION ALL
SELECT 'Priya'
UNION ALL
SELECT 'Priya'
UNION ALL
SELECT 'Rahul';

For practice, use parenthesized query expressions to make the intended input sets explicit. The expected multiset intersection contains two copies of Priya and one copy of Rahul. When testing set-operation precedence, parentheses are especially important because INTERSECT has higher precedence than UNION.

Solution 6: Customers on Both Lists

SELECT customer_id
FROM current_customers

INTERSECT

SELECT customer_id
FROM newsletter_subscribers;

Solution 7: Customers Missing from the Migration

SELECT customer_id
FROM source_customers

EXCEPT

SELECT customer_id
FROM migrated_customers;

Solution 8: Employees on Both Projects

SELECT employee_id
FROM project_assignments
WHERE project_id = 10

INTERSECT

SELECT employee_id
FROM project_assignments
WHERE project_id = 20;

Solution 9: Older MySQL Alternative

SELECT DISTINCT s.student_id
FROM sql_students AS s
INNER JOIN python_students AS p
    ON p.student_id = s.student_id;

Solution 10: NULL-Safe Matching

SELECT a.value
FROM table_a AS a
INNER JOIN table_b AS b
    ON a.value <=> b.value;

The NULL-safe equality operator allows NULL to match NULL. Use this approach only when that behavior matches the intended comparison rule.

22. Mini Project: Dataset Comparison and Validation

Project objective: Compare two datasets to identify common records, records missing from a destination, and records that appear only in the destination.

22.1 Project Scenario

A company migrates customer data from an old database to a new database. The development team must check whether all customer IDs have been transferred correctly.

22.2 Find Customers Successfully Present in Both Tables

SELECT customer_id
FROM old_customers

INTERSECT

SELECT customer_id
FROM new_customers;

22.3 Find Customers Missing from the New Table

SELECT customer_id
FROM old_customers

EXCEPT

SELECT customer_id
FROM new_customers;

22.4 Find Customers Existing Only in the New Table

SELECT customer_id
FROM new_customers

EXCEPT

SELECT customer_id
FROM old_customers;

22.5 Project Tasks

• Create sample source and destination customer tables.
• Insert matching records, missing records, and additional records.
• Run INTERSECT to identify common customer IDs.
• Run EXCEPT in both directions to identify differences.
• Test the behavior of duplicate IDs using ALL where appropriate.
• Compare the results with JOIN and NOT EXISTS alternatives.
• Write a short report explaining any missing or unexpected records.

22.6 Expected Learning Outcomes

• Understand native set-operation support in MySQL.
• Identify common rows and differences between datasets.
• Validate records during data migration.
• Handle duplicates and NULL values correctly.
• Write compatible alternatives for older MySQL releases.

23. Interview Questions and Answers

Q1. What is INTERSECT in MySQL?

Answer: INTERSECT returns rows common to the results of two or more query expressions. It removes duplicate rows by default.

Q2. What is EXCEPT in MySQL?

Answer: EXCEPT returns rows from the first query result that do not appear in the second query result.

Q3. Which MySQL version introduced INTERSECT and EXCEPT?

Answer: MySQL 8.0.31 introduced official support for both operators.

Q4. Are INTERSECT and EXCEPT supported in MySQL 5.7?

Answer: No. Use alternatives such as INNER JOIN, EXISTS, NOT EXISTS, or LEFT JOIN anti-joins.

Q5. What is the difference between INTERSECT and UNION?

Answer: UNION combines rows from both query results, whereas INTERSECT returns only rows common to both results.

Q6. What is the difference between INTERSECT and EXCEPT?

Answer: INTERSECT returns common rows. EXCEPT returns rows from the first result that are absent from the second.

Q7. What does INTERSECT DISTINCT do?

Answer: It returns common rows while removing duplicates. This is the default behavior when the modifier is omitted.

Q8. What does INTERSECT ALL do?

Answer: It preserves duplicate rows up to the number of occurrences shared by both inputs.

Q9. What does EXCEPT ALL do?

Answer: It subtracts the occurrence count of each row in the second result from its count in the first result, returning only the remaining copies.

Q10. Do both SELECT statements need the same number of columns?

Answer: Yes. The query expressions must return the same number of columns, and corresponding columns must have compatible data types.

Q11. Does the order of the queries matter in INTERSECT?

Answer: Swapping the inputs produces the same set of rows, although output order is not guaranteed. Duplicate counts under INTERSECT ALL are also symmetric.

Q12. Does the order of the queries matter in EXCEPT?

Answer: Yes. A EXCEPT B and B EXCEPT A can return different results.

Q13. How can INTERSECT be replaced in older MySQL versions?

Answer: Use INNER JOIN or EXISTS with appropriate filtering and duplicate handling.

Q14. How can EXCEPT be replaced in older MySQL versions?

Answer: Use NOT EXISTS or a LEFT JOIN anti-join to find rows without matching records.

Q15. How do you check the MySQL server version?

Answer: Run SELECT VERSION();.

Q16. Does INTERSECT guarantee a sorted result?

Answer: No. Use ORDER BY when a specific order is required.

Q17. Can INTERSECT compare different column names?

Answer: Yes, the column names do not need to be identical, but the selected columns must appear in the intended corresponding positions and have compatible data types.

Q18. Why might an INTERSECT query return fewer rows than expected?

Answer: It may be comparing more columns than intended, eliminating duplicates by default, or using input filters that exclude expected records.

Q19. How can you find customers who exist in one table but not another?

Answer: Use EXCEPT in MySQL 8.0.31 or later, or use NOT EXISTS in older versions.

Q20. Are set operations always faster than JOINs?

Answer: No. Performance depends on data size, indexes, duplicate handling, query structure, and the execution plan.

24. Quick Revision Checklist

• INTERSECT finds common rows between query results.
• EXCEPT finds rows in the first result that are absent from the second.
• Native INTERSECT and EXCEPT support began in MySQL 8.0.31.
• Older MySQL versions can use JOIN, EXISTS, or NOT EXISTS alternatives.
• Both query expressions must return the same number of columns.
• Corresponding columns must have compatible data types.
• Column position matters when comparing query results.
• DISTINCT is the default duplicate behavior.
• ALL preserves shared or remaining duplicate counts.
• EXCEPT is directional; reversing its inputs can change the result.
• Use ORDER BY to sort the final output.
• Consider NULL behavior when rewriting set operations as joins.
• Use EXPLAIN to investigate performance.
• Validate set-operation results using representative sample data.

25. Best Practices

• Check the installed MySQL version before writing queries with INTERSECT or EXCEPT.
• Select only the columns that define the intended comparison.
• Use aliases to make result columns easy to understand.
• Use DISTINCT when unique results are required and ALL when duplicate counts matter.
• Remember that EXCEPT depends on the order of its input queries.
• Use parentheses to make complex combinations of set operators easier to read.
• Check NULL handling when replacing set operations with JOIN or EXISTS expressions.
• For older versions, choose an alternative that matches the required duplicate and NULL semantics.
• Use indexes where appropriate for JOIN and subquery alternatives.
• Validate migration and reconciliation results before making changes to production data.

26. Conclusion

INTERSECT and EXCEPT are valuable SQL set operators for comparing query results. INTERSECT finds rows common to multiple result sets, while EXCEPT identifies rows that exist in the first result but not in the second.

MySQL introduced native support for both operators in version 8.0.31. Their default DISTINCT behavior removes duplicate rows, while the ALL modifier allows duplicate occurrence counts to be preserved or subtracted. Understanding these differences helps developers write accurate queries for reporting, dataset comparison, data migration, and database validation.

For older MySQL versions, INNER JOIN, EXISTS, NOT EXISTS, and LEFT JOIN anti-joins provide practical alternatives. Choosing the right approach requires careful attention to selected columns, compatible data types, duplicate handling, NULL behavior, and query performance.

27. Learning Resources

• Explore the MySQL Training Course at JustAcademy
• Register for a Course Demo
• MySQL Documentation: Set Operations
• MySQL Documentation: INTERSECT
• MySQL Documentation: EXCEPT
• MySQL Blog: INTERSECT and EXCEPT in MySQL 8.0