What is an equivalent of EXCEPT(in PostgreSQL) in MySQL? [closed]

Question

I want to find out unmatched records of two tables in MySQL.

Answer 1

You can use a NOT EXISTS predicate. Assume you want to mimic:

select a.c1, a.c2 from a
except
select b.c1, b.c2 from b

This can be expressed as:

select distinct a.c1, a.c2 
from a
where not exists (
    select 1 from b
    where b.c1 = a.c1
      and b.c2 = a.c2
)

Other options are to use a left join and check for null:

select distinct a.c1, a.c2 
from a 
left join b 
    on a.c1 = b.c1 
   and a.c2 = b.c2 
where b.c1 is null

Note that these differ from EXCEPT if the same tuple exists in both relations and contains null. Let a = {(1,1),(2,2),(1,null)} and b = {(1,1),(1,null)}

select a.c1, a.c2 from a 
except 
select b.c1, b.c2 from b

(2,2)

In a sense, EXCEPT considers null to be equal to null, whereas the predicate a.c2 = b.c2 evaluates to null if either a.c2 or b.c2 is null and as a consequence NOT EXISTS evaluates to false.

select distinct a.c1, a.c2 
from a 
where not exists (
    select b.c1, b.c2 from b where a.c1 = b.c1 and a.c2 = b.c2
)

(2,2),(1,null)

LEFT JOIN behaves similarly to NOT EXISTS:

select distinct a.c1, a.c2 
from a 
left join b 
    on a.c1 = b.c1 and a.c2 = b.c2 
where b.c1 is null

(2,2),(1,null)