2

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

closed as off-topic by hot2use, McNets, Erik Darling, LowlyDBA, Mr.Brownstone Jan 18 '18 at 16:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – hot2use, McNets, Erik Darling, LowlyDBA, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

6

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)

Not the answer you're looking for? Browse other questions tagged or ask your own question.