EXCEPT Operator in MYSQL

In MySQL, the EXCEPT operator does not exist. Instead, you can achieve similar functionality using the NOT IN or LEFT JOIN approach. Here's how you can do it:

Suppose you have two tables: table1 and table2, both with the same structure. You want to retrieve all distinct rows from table1 that are not present in table2.

Using the NOT IN approach:

SELECT column1, column2, ...

FROM table1

WHERE (column1, column2, ...) NOT IN (

    SELECT column1, column2, ...

    FROM table2

);

Using the LEFT JOIN approach:

SELECT t1.column1, t1.column2, ...

FROM table1 t1

LEFT JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2

WHERE t2.column1 IS NULL;

In both cases:

- SELECT column1, column2, ... specifies the columns you want to retrieve.

- FROM table1 specifies the first table from which to retrieve the data.

- In the NOT IN approach, (column1, column2, ...) represents a tuple of values from table1.

- In the LEFT JOIN approach, the LEFT JOIN ensures that all rows from table1 are included, and the WHERE clause filters out rows where no match is found in table2.

Here's a concrete example using the NOT IN approach:

SELECT employee_id, first_name, last_name

FROM employees

WHERE (employee_id, first_name, last_name) NOT IN (

    SELECT employee_id, first_name, last_name

    FROM former_employees

);

And here's the same example using the LEFT JOIN approach:

SELECT e.employee_id, e.first_name, e.last_name

FROM employees e

LEFT JOIN former_employees fe 

    ON e.employee_id = fe.employee_id 

    AND e.first_name = fe.first_name 

    AND e.last_name = fe.last_name

WHERE fe.employee_id IS NULL;

These queries will return all distinct rows from employees that are not present in former_employees.