SQL INTERSECT and EXCEPT Step by step Implementation and Top 10 Questions and Answers
 .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    Last Update: April 01, 2025      20 mins read      Difficulty-Level: beginner

SQL INTERSECT and EXCEPT: Detailed Explanation and Important Information

SQL (Structured Query Language) provides various set operations to perform data manipulations and analysis between two or more SELECT statements. Among these set operations, INTERSECT and EXCEPT are particularly useful for comparing dataset results and extracting meaningful insights.

Understanding SQL Set Operations

Before delving into INTERSECT and EXCEPT, it's important to understand basic set operations, which include UNION, INTERSECT, and EXCEPT. These operations work similarly to their counterparts in mathematics and are used to combine or compare the results of two or more SELECT queries.

  1. UNION: Combines the results of two or more SELECT statements into a single result set, eliminating duplicate rows.
  2. UNION ALL: Similar to UNION, but it includes all rows from both SELECT statements, including duplicates.
  3. INTERSECT: Returns only the rows that are common to both SELECT statements, eliminating duplicates.
  4. EXCEPT: Returns all rows from the first SELECT statement that are not present in the second SELECT statement, eliminating duplicates.

SQL INTERSECT

The INTERSECT operator is used to retrieve a distinct set of rows that exist in all the SELECT statements specified. Essentially, INTERSECT finds the intersection of two result sets, i.e., the rows that are present in both sets.

Syntax:

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
  • Columns and Data Types: The columns listed in each SELECT statement must be the same, and their data types must match.
  • Performance Considerations: INTERSECT can be slower than UNION due to the additional step of identifying common rows.
  • Examples:
    • Example 1: Find common employee IDs in both the Sales and Marketing departments. 
      SELECT employee_id
FROM Sales
INTERSECT
SELECT employee_id
FROM Marketing;
    • Example 2: Find common products sold in two different stores. 
      SELECT product_id
FROM Store1_Products
INTERSECT
SELECT product_id
FROM Store2_Products;

Key Points:

  • DISTINCT: INTERSECT automatically eliminates duplicate rows in its result set.
  • Order of Results: The INTERSECT operator does not guarantee any order for the returned rows unless an ORDER BY clause is explicitly used.
  • Use Cases: INTERSECT is ideal when you want to find common data between two tables and eliminate duplicates.

SQL EXCEPT

The EXCEPT operator, sometimes referred to as MINUS in other SQL dialects such as Oracle, is used to retrieve rows from the first SELECT statement that do not appear in the second SELECT statement. This operation is particularly useful for identifying differences between two result sets.

Syntax:

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;
  • Columns and Data Types: The columns listed in each SELECT statement must be the same, and their data types must match.
  • Performance Considerations: EXCEPT can also be slower than UNION due to the additional step of identifying unique rows in the first result set.
  • Examples:
    • Example 1: Find customer IDs present in the Sales table but not in the Marketing table. 
      SELECT customer_id
FROM Sales
EXCEPT
SELECT customer_id
FROM Marketing;
    • Example 2: Identify products available in Store1 but not in Store2. 
      SELECT product_id
FROM Store1_Products
EXCEPT
SELECT product_id
FROM Store2_Products;

Key Points:

  • DISTINCT: EXCEPT automatically eliminates duplicate rows in the result set.
  • Order of Results: The result order is determined by the first SELECT statement unless an ORDER BY clause is applied.
  • Use Cases: EXCEPT is useful for pinpointing unique elements in one dataset compared to another, such as identifying new or lost customers.

Additional Considerations

  • Set Operation Requirements: All set operations (INTERSECT, EXCEPT, UNION, UNION ALL) require that the SELECT statements have the same number of columns and that corresponding columns have compatible data types.
  • NULL Handling: NULL values are considered equal in set operations if both corresponding cells are NULL. However, NULLs do not match any non-NULL values.
  • Optimization Tips:
    • Use indexes on the columns involved in the set operation to improve query performance.
    • Consider using UNION with filtering conditions when the INTERSECT or EXCEPT operations might not be feasible or as efficient.

Practical Application

  1. Data Validation: Use INTERSECT and EXCEPT to validate data across different systems or sources, ensuring consistency.
  2. Auditing Changes: Track changes in data over time by comparing historical data with current data using INTERSECT to find what remains unchanged and EXCEPT to identify what has been added or removed.
  3. Customer Analysis: Identify overlapping customer bases between different product lines or marketing campaigns using INTERSECT, or find exclusive customers for a particular product using EXCEPT.

Conclusion

INTERSECT and EXCEPT are powerful SQL set operations that allow for detailed comparison between different datasets. They help in identifying commonalities and differences efficiently, making them invaluable for data analysis and auditing tasks. Understanding and effectively using these operators can greatly enhance your ability to work with complex data relationships in SQL. Always consider the dataset size and indexing strategies to optimize performance when employing these operations.




Examples, Set Route and Run the Application Then Data Flow: Step-by-Step Guide on SQL INTERSECT and EXCEPT

Introduction

Understanding how SQL INTERSECT and EXCEPT (or MINUS in some SQL dialects) work can immensely enhance your ability to manipulate and analyze data sets. These set operations are used to compare two or more result sets and return rows that are unique to each operation. INTERSECT returns rows that are common to both result sets, while EXCEPT (or MINUS) returns rows from the first result set that are not present in the second.

Before we dive into the examples, let's set a clear route for learning and running applications that demonstrate INTERSECT and EXCEPT. We'll start with an overview of these two operations, move on to example queries, then create and run a simple application, and finally trace the data flow through the process.

What Are SQL INTERSECT and EXCEPT?

  • INTERSECT: Returns all distinct rows that are common between two or more result sets.
  • EXCEPT (or MINUS): Returns all distinct rows from the first result set that do not exist in the second result set.

Both INTERSECT and EXCEPT require the result sets being compared to have the same number of columns and compatible data types.

Setting Up Your Environment

To work with INTERSECT and EXCEPT, you'll need:

  1. Database Management System (DBMS): MySQL, PostgreSQL, SQL Server, Oracle, SQLite, etc.
  2. SQL Client: MySQL Workbench, pgAdmin for PostgreSQL, SQL Server Management Studio, etc.
  3. Code Editor or Integrated Development Environment (IDE): Visual Studio Code, Eclipse, etc.

For this guide, let's assume you're using PostgreSQL, a powerful open-source relational database system.

Step 1: Create Sample Data

Let's start by creating sample tables that we can use in our examples.

-- Create table Students
CREATE TABLE Students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

-- Create table EnrolledStudents
CREATE TABLE EnrolledStudents (
    id SERIAL PRIMARY KEY,
    student_name VARCHAR(100),
    course_name VARCHAR(100)
);

Now, insert some sample data:

-- Insert sample data into Students
INSERT INTO Students (name, age) VALUES 
('Alice', 20),
('Bob', 22),
('Charlie', 24),
('David', 26),
('Eve', 28);

-- Insert sample data into EnrolledStudents
INSERT INTO EnrolledStudents (student_name, course_name) VALUES 
('Alice', 'Mathematics'),
('Bob', 'Physics'),
('Charlie', 'Chemistry'),
('Bob', 'Chemistry'),
('Eve', 'Mathematics');

Step 2: Writing Example Queries

After setting up the sample data, let's write some queries to understand INTERSECT and EXCEPT.

INTERSECT Example:

Find students who are both in the Students table and enrolled in any course in the EnrolledStudents table.

SELECT name FROM Students INTERSECT SELECT student_name FROM EnrolledStudents;

Expected output:

 name    
---------
 Alice   
 Bob     
 Eve     
 Charlie

EXCEPT Example:

Find students who are listed in the Students table but not enrolled in any courses.

SELECT name FROM Students EXCEPT SELECT student_name FROM EnrolledStudents;

Note: As per our example, Bob and Eve are enrolled, so there won't be any students listed here. To see results, let's add a new student:

INSERT INTO Students (name, age) VALUES ('Frank', 21);

Now, rerun the query:

SELECT name FROM Students EXCEPT SELECT student_name FROM EnrolledStudents;

Expected output:

 name  
-------
 Frank

Step 3: Creating a Simple Application (using Python)

We'll create a simple Python script using the psycopg2 library to interact with our PostgreSQL database.

First, install psycopg2 if you haven’t already:

pip install psycopg2

Now, create a Python script named main.py.

import psycopg2

# Connect to your local PostgreSQL server
conn = psycopg2.connect("dbname=test user=postgres password=your_password")

# Open a cursor to perform database operations
cur = conn.cursor()

def get_common_students():
    cur.execute("""
        SELECT name FROM Students INTERSECT SELECT student_name FROM EnrolledStudents;
    """)
    print("\nCommon Students:")
    for record in cur.fetchall():
        print(record[0])
        
def get_unenrolled_students():
    cur.execute("""
        SELECT name FROM Students EXCEPT SELECT student_name FROM EnrolledStudents;
    """)
    print("\nUnenrolled Students:")
    for record in cur.fetchall():
        print(record[0])

try:
    get_common_students()
    get_unenrolled_students()
finally:
    # Close communication with the database
    cur.close()
    conn.close()

Explanation:

  • conn establishes a connection to your database.
  • cur creates a cursor object, which is used to execute SQL commands.
  • Two functions, get_common_students and get_unenrolled_students, execute the respective SQL queries and print the results.

Running the Application:

Make sure your PostgreSQL server is running and the database (test in our case) is accessible. Then, run the Python script using:

python main.py

Step 4: Trace the Data Flow

Here are the steps and processes that occur inside your application:

  1. Connection: The Python script uses psycopg2.connect() to establish a connection to your PostgreSQL server. This includes specifying the database name, username, and password.

  2. Cursor Creation: A cursor (cur) is created using conn.cursor(). This cursor will be used to execute SQL commands and fetch results.

  3. Executing SQL Commands:

    • INTERSECT Query: When calling get_common_students(), the INTERSECT query is executed. This command compares the name column from Students with the student_name column from EnrolledStudents and only returns distinct names that appear in both tables.
    • EXCEPT Query: Similarly, when calling get_unenrolled_students(), the EXCEPT query is executed. It looks for names in Students that do not match any name in EnrolledStudents, returning distinct names from the first result set that are absent in the second.

  4. Fetching and Displaying Results:

    • cur.fetchall() retrieves all rows of a query result, returning them as a list of tuples. In our case, it returns the common and unenrolled student names.
    • The script prints these names line by line.

  5. Closing Resources:

    • After displaying the results, it’s essential to close the cursor using cur.close(), and then close the connection using conn.close(). This step releases database resources and ensures no memory leaks occur.

Practical Use Case

Scenario: You are managing a college administration system and want to generate reports to know which students are currently taking classes and which ones are not registered for any courses.

INTERSECT: Provides a list of students attending classes, helping instructors prepare class rosters. EXCEPT: Identifies students who are not enrolled, possibly needing to contact them for course registration or understanding reasons for non-participation.

Summary

In this comprehensive guide, we've taken an exploratory and practical approach to learning SQL INTERSECT and EXCEPT. We began with a theoretical understanding of both operations, proceeded to populate sample tables in a PostgreSQL database, wrote relevant SQL queries, developed a simple Python application utilizing those queries, and traced the complete data flow to grasp the underlying processes.

By following these steps, beginners can start leveraging INTERSECT and EXCEPT for efficient data analysis, comparison, and management tasks. Practice with similar scenarios and expand your knowledge base to master these powerful SQL set operations.




Top 10 Questions and Answers on SQL INTERSECT and EXCEPT

1. What are the INTERSECT and EXCEPT operators in SQL?

The INTERSECT and EXCEPT operators are used to compare two result sets from SQL queries. They return rows based on commonality or exclusivity between these result sets.

  • INTERSECT: Returns distinct rows that appear in both result sets of the two SELECT statements.
  • EXCEPT: Returns distinct rows from the first result set of SELECT statement that do not appear in the second result set. Some SQL dialects use MINUS instead of EXCEPT (e.g., Oracle).

Example:

SELECT customer_id FROM customers_east 
INTERSECT 
SELECT customer_id FROM customers_west;

This will return a list of customers who are present in both the customers_east and the customers_west tables.

2. How does INTERSECT work and what are its requirements?

INTERSECT is used to find common values between two or more result sets. Both result sets must have the same number and type of columns. The order of columns should be identical, and the data types should match for equivalent columns.

Example:

SELECT customer_id FROM orders WHERE order_date > '2023-01-01'
INTERSECT
SELECT customer_id FROM orders WHERE delivery_date < '2023-01-31';

This will return the list of customers who placed an order after January 1, 2023, and also had their order delivered before January 31, 2023.

Requirements:

  • Both queries must return the same number of columns.
  • Corresponding columns in each query must contain data of compatible types.
  • All matching rows across the two result sets are included only once in the final output.

3. How does EXCEPT (or MINUS) differ from INTERSECT?

While INTERSECT finds the rows common to two result sets, EXCEPT (or MINUS in some SQL dialects like Oracle) finds the rows that exist in the first result set but do not appear in the second result set.

Example with EXCEPT:

SELECT employee_id FROM employees_new_hire 
EXCEPT 
SELECT employee_id FROM employees_current;

This will return a list of new hires who are not yet listed in the current employees table.

Example with MINUS:

SELECT employee_id FROM employees_new_hire 
MINUS 
SELECT employee_id FROM employees_current;

The above Oracle SQL query will give the same result as the EXCEPT query.

Requirements:

  • Similar to INTERSECT, both queries must return the same number of columns.
  • Corresponding columns in each query must contain data of compatible types.

4. Can INTERSECT and EXCEPT be used with non-equal data types in columns?

No, INTERSECT and EXCEPT require that corresponding columns in both result sets have identical data types. If the data types are different, this can lead to an error or incorrect results depending on the SQL database engine.

Example: If one query returns INT for customer_id while another returns VARCHAR, an error occurs unless explicit conversion is performed using functions like CAST() or CONVERT().

Corrected Example:

SELECT customer_id FROM customers_east -- assuming VARCHAR
EXCEPT 
SELECT CAST(customer_id AS VARCHAR) FROM customers_west; -- assuming INT

5. How do you handle NULLs in INTERSECT and EXCEPT operations?

NULL values treated as equal by INTERSECT and EXCEPT. Only one NULL value is returned for each set of duplicate NULL values across the result sets.

Example:

SELECT name FROM users_europe WHERE country = 'Germany'
INTERSECT
SELECT name FROM users_asia WHERE country IS NULL;

If there is a NULL entry in the name column for entries where country is NULL in users_asia and the same NULL appears in users_europe, then this NULL will be included in the final result set.

6. What is the difference between INTERSECT and using subqueries or joins to find common elements?

Using INTERSECT is often simpler and more readable when looking for common data between two result sets. However, subqueries and joins offer greater flexibility and control over the results.

Using INTERSECT:

SELECT product_id FROM order_details_2022
INTERSECT
SELECT product_id FROM order_details_2023;

Finds products ordered in both 2022 and 2023.

Using a Subquery:

SELECT DISTINCT product_id 
FROM order_details_2022 
WHERE product_id IN (
    SELECT product_id FROM order_details_2023
);

Finds products ordered in both years but provides more options for customization.

Using a Join:

SELECT DISTINCT o2022.product_id 
FROM order_details_2022 o2022 
JOIN order_details_2023 o2023 ON o2022.product_id = o2023.product_id;

This also finds products ordered in both years but allows for more complex queries involving additional tables and conditions.

7. How do you use EXCEPT to find records in one table that are not in another?

EXCEPT is perfect for finding unique records in one table compared to another. Here’s an example:

SELECT employee_id FROM employees_all
EXCEPT
SELECT employee_id FROM employees_on_leave;

This will return all employee IDs from the employees_all table that do not exist in the employees_on_leave table.

Another use case is finding records that have been inserted or removed recently.

-- Find new records
SELECT * FROM customers_before_import
EXCEPT
SELECT * FROM customers_after_import;

-- Find removed records
SELECT * FROM customers_after_import
EXCEPT
SELECT * FROM customers_before_import;

8. Is it possible to combine INTERSECT and EXCEPT operations in a single query?

Yes, combining INTERSECT and EXCEPT in a single query can be done to solve more complex problems. You can chain multiple set operations together. Note that parentheses may be needed to clarify the order of operations.

Example: Find products sold in both stores A and B, but not in store C.

(SELECT product_id FROM sales_store_A
INTERSECT
SELECT product_id FROM sales_store_B)
EXCEPT
SELECT product_id FROM sales_store_C;

This nested operation ensures that only products common in both A and B but not in C are included.

9. What are the performance considerations for using INTERSECT and EXCEPT?

INTERSECT and EXCEPT can be less performant than their equivalent JOIN or subquery approaches due to how they operate internally. Databases need to sort and compare full result sets, which might become expensive if the data volume is large. Indexes on columns involved in the comparison can help improve performance.

Tips for performance:

  • Ensure columns used in set operations (INTERSECT and EXCEPT) are indexed.
  • Where feasible, limit the size of the datasets returned by the queries participating in set operations.
  • Consider using temporary tables and materialized views for intermediate results if the same large datasets are used in multiple set operations to reduce computation time.

10. When would you prefer using INTERSECT or EXCEPT over JOINs or subqueries?

INTERSECT and EXCEPT provide a concise way to extract common or uncommon elements between tables without needing to write more verbose code such as JOIN or IN subqueries. Here are scenarios where they might be preferred:

  • Simplicity: For straightforward cases of commonality or uniqueness testing between two result sets, these operators can simplify the code.
  • Clarity: When the query is meant to express the intent of comparing two result sets for common or exclusive elements, it can be clearer than using joins or subqueries.
  • Performance: They can sometimes be faster because they are optimized for the specific task of finding intersections or differences between sorted result sets.

Example Use Case: Compare product inventories between two warehouses.

SELECT * FROM warehouse_inventory_W1
INTERSECT
SELECT * FROM warehouse_inventory_W2;

This query quickly identifies which products are available in both warehouses.

In summary, while INTERSECT and EXCEPT can be powerful tools for specific data comparison tasks, understanding alternative methods like JOINs and subqueries is important for handling more complex scenarios and optimizing performance. Always consider the data characteristics and requirements of your query to choose the most efficient approach.