Comparison of two different sets of data in a database and finding entries unique to one set is one of the common operations done on databases. The EXCEPT operator in SQL compares the two result sets returned and returns only the rows that are found in one but not the other.

Moreover, MySQL, one of the most popular relational database management systems, does not support the EXCEPT operator directly. Not to say that a MySQL user can’t do anything differently; it provides alternative ways to get the same results from MySQL, such as using LEFT JOIN or NOT IN.

What is the EXCEPT Operator

The EXCEPT operator in SQL is used for comparing two result sets and returning the rows available in the first result set and not in the second. It does what one might think of as DIFF between two datasets. For this reason, it could be relevant for purposes of data validation, cleaning, and synchronization. This construct is in wide use in SQL for precisely this purpose, but MySQL does not have native support for it. Instead, other methods can be applied to achieve similar results and thus help MYSQL users, for example, LEFT JOIN or NOT IN. Being aware of these alternatives allows a MYSQL user to compare complex data and realize additional potential for database management.

Syntax of MySQL EXCEPT Operator

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

where,

• SELECT column1, column2: This part of the query selects specific columns
• EXCEPT: This can be achieved using the EXCEPT operator to find the difference between two SELECT statements.

MySQL Equivalent of the EXCEPT Operator

Since MySQL doesn’t support an EXCEPT operator directly connected, we may do the same by use of other kinds of SQL constructs. The most common method applies by making use of LEFT JOIN or NOT IN clauses.

Using LEFT JOIN

As the name suggests, the LEFT JOIN approach joins two tables before filtering out the rows which turn up alike in the second table.

SELECT a.*
FROM table1 a
LEFT JOIN table2 b ON a.id = b.id
WHERE b.id IS NULL;

Using NOT IN

The NOT IN clause can also be used to filter out the matching rows.

SELECT *
FROM table1
WHERE id NOT IN (SELECT id FROM table2);

Example of MySQL EXCEPT Operator

Here is an example of MySQL EXCEPT Operator:

Creating Tables

-- Create employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Create managers table
CREATE TABLE managers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Insert Data into Tables

-- Insert data into employees table
INSERT INTO employees (id, name) VALUES 
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Insert data into managers table
INSERT INTO managers (id, name) VALUES
(1, 'Alice'),
(3, 'Charlie');

Output

Employees:

-- Using LEFT JOIN to find employees who are not managers
SELECT e.id, e.name
FROM employees e
LEFT JOIN managers m ON e.id = m.id
WHERE m.id IS NULL;

SELECT id, name
FROM employees
WHERE id NOT IN (SELECT id FROM managers);

SELECT id, name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM managers m WHERE e.id = m.id);

-- Create orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE
);

-- Create shipped_orders table
CREATE TABLE shipped_orders (
    order_id INT PRIMARY KEY,
    ship_date DATE
);

-- Insert data into orders table
INSERT INTO orders (order_id, customer_name, order_date) VALUES
(101, 'Alice', '2024-07-01'),
(102, 'Bob', '2024-07-02'),
(103, 'Charlie', '2024-07-03'),
(104, 'David', '2024-07-04');

-- Insert data into shipped_orders table
INSERT INTO shipped_orders (order_id, ship_date) VALUES
(101, '2024-07-05'),
(103, '2024-07-06');

SELECT o.order_id, o.customer_name, o.order_date
FROM orders o
LEFT JOIN shipped_orders s ON o.order_id = s.order_id
WHERE s.order_id IS NULL;

SELECT order_id, customer_name, order_date
FROM orders
WHERE order_id NOT IN (SELECT order_id FROM shipped_orders);

SELECT order_id, customer_name, order_date
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM shipped_orders s WHERE o.order_id = s.order_id);