Adam Adam - 5 months ago 9
MySQL Question

Return the opposite results of LEFT OUTER JOIN

If I would have 2 tables

Table A Table B
ID, ABC ABC
1, A A
2, B A
3, C C
4, D E
5, E F


How do I get this result

Table Result
2, B
4, D


The following query needs to be adjusted, but I dont know how

SELECT A.*
FROM A
LEFT OUTER JOIN B
ON A.abc = B.abc


SQLFIDDLE: http://sqlfiddle.com/#!9/11093

Answer

You are very close. Just add a where clause:

SELECT A.*
FROM A LEFT OUTER JOIN
     B
     ON A.abc = B.abc
WHERE B.abc IS NULL;

A more traditional approach uses NOT EXISTS:

select a.*
from a
where not exists (select 1 from b where b.abc = a.abc);

Here is a SQL Fiddle illustrating that the first works.

Comments