How to use Intersect & Except clause in SQL

If you’ve been working with SQL, you must have encountered the Intersect and Except clauses. These are essential tools for filtering data in SQL since they enable you to compare and contrast tables by selecting matching or non-matching rows. We will delve into their definitions, syntax, and a few practical examples to understand how to use these clauses effectively.

What is Intersect in SQL?

The Intersect clause is an SQL set operation that selects only the common rows of two SELECT statements. It compares two or more SELECT statements to identify the matching rows. For instance, if you have two tables, Table1, and Table2, and you want to find the matches between them, then use the Intersect clause. It is similar to the INNER JOIN syntax but does not return duplicate rows.

The syntax for the Intersect clause

SELECT column1, column2, ... FROM Table1
INTERSECT
SELECT column1, column2, ... FROM Table2

The syntax for the Intersect is relatively simple. You select your columns and your tables in the first SELECT statement. Then you use the INTERSECT operator to compare with another SELECT statement. The resulting query will have only the common rows between the two tables.

Practical example of Intersect

Let’s take an example of two tables, Sales1 and Sales2, with the same columns:

        Table: Sales1
        +-------------+
        | salesman_id |
        +-------------+
        | 101         |
        | 102         |
        | 104         |
        | 107         |
        +-------------+

        Table: Sales2
        +-------------+
        | salesman_id |
        +-------------+
        | 102         |
        | 104         |
        | 106         |
        +-------------+

If you wanted to find all the matching salesmen IDs between Sales1 and Sales2 tables, you would use this query:

SELECT salesman_id
FROM Sales1
INTERSECT
SELECT salesman_id
FROM Sales2;

The result will be:

        +-------------+
        | salesman_id |
        +-------------+
        | 102         |
        | 104         |
        +-------------+

You can see that the Intersect clause selects only the matching rows between the two tables.

What is Except in SQL?

The Except clause, also known as the Minus clause, is another SQL set operation that returns only the unique rows in the first SELECT statement that do not exist in the second SELECT statement. It is like subtracting one table from another since it returns only the rows in the first table that are not in the second table. You use the Except clause when you want to filter non-matching rows between two tables.

Syntax for the Except clause

SELECT column1, column2, ... FROM Table1
EXCEPT
SELECT column1, column2, ... FROM Table2

The syntax for the Except clause is similar to that of the Intersect clause. You select your columns and your tables in the first SELECT statement, then use the EXCEPT operator to compare it with another SELECT statement. The resulting query will have only the distinct non-matching rows between the two tables.

Practical example of Except

Let’s take an example of two tables, Customers and Orders, with the same columns:

        Table: Customers
        +--------+
        | name   |
        +--------+
        | Sam    |
        | Joe    |
        | Billy  |
        | Alice  |
        +--------+

        Table: Orders
        +--------+
        | name   |
        +--------+
        | Billy  |
        | Alice  |
        +--------+

If you wanted to find all the unique customers who have not made orders, you would use this query:

SELECT name
FROM Customers
EXCEPT
SELECT name
FROM Orders;

The result will be:

        +--------+
        | name   |
        +--------+
        | Sam    |
        | Joe    |
        +--------+

You can see that the Except clause selects only the non-matching rows between the two tables.

Frequently Asked Questions

Conclusion

SQL’s Intersect and Except clauses are powerful tools for filtering data from tables. They help you select matching or non-matching rows between two tables. You can use these clauses to compare and eliminate duplicate data or filter out unique data. Ensure you know your data and how it relates to the tables before using these clauses. With practice, using these clauses helps you understand your data better and get the most meaningful insights.

Useful links:

• Microsoft: Use EXCEPT and INTERSECT

📕 Related articles about MySQL

• MySQL Creating Table – Everything You Need to Know
• How to use Wildcard operators in SQL
• MySQL Disconnection from Server: Causes and Fixes
• MySQL ALTER DATABASE Statement: A Comprehensive Guide
• MySQL SELECT Statement
• Installing MySQL on Microsoft Windows