MySQL: Select from table A which does not exist in table B

Question

I am trying to compose a SELECT statement for MySQL which select from table A what does not exist in table B. Most of examples use only one field as key. In my case, I have 3 fields.

TableA => a.fieldA, a.fieldB, a.fieldC
1,1,1
1,2,1
1,3,1
1,4,1
1,5,1

TableB => b.fieldA, b.fieldB, b.fieldC
1,1,1
1,3,1
1,4,1

So I only want the fields from TableA that does not exist in TableB

Result
1,2,1
1,5,1

I tried LEFT JOIN, but have no luck

SELECT a.fieldA, a.fieldB, a.fieldC
FROM TableA a 

LEFT JOIN TableB b ON (
a.fieldA = b.fieldA 
AND a.fieldB = b.fieldB 
AND a.fieldC = b.fieldC) 

WHERE a.fieldA = 1 
AND a.fieldC = 1

I also tried NOT IN, but didn't know how to do that with 3 fields.

I know this must be simple, but I am blind. bad day :(

You are almost there.. Just add `AND b.fieldA IS NULL` to the WHERE clause. — Paul Spiegel, Oct 30 '17 at 16:39

AnouarZ · Accepted Answer · 2017-10-30T17:42:42.003

9

Using NOT IN :

SELECT a.fieldA, a.fieldB, a.fieldC
FROM tableA a 
WHERE (a.fieldA, a.fieldB, a.fieldC) NOT IN (
SELECT b.fieldA, b.fieldB, b.fieldC
FROM  tableB b)

Using LEFT JOIN :

SELECT a.fieldA, a.fieldB, a.fieldC
FROM tableA a 

LEFT JOIN tableB b ON (
a.fieldA = b.fieldA 
AND a.fieldB = b.fieldB 
AND a.fieldC = b.fieldC) 

WHERE b.fieldA IS NULL

EDIT

I just Find out in a similar quetion another way using NOT EXISTS

SELECT a.fieldA, a.fieldB, a.fieldC
FROM tableA a 
WHERE NOT EXISTS (
SELECT 1
FROM  tableB b 
WHERE a.fieldA = b.fieldA 
AND a.fieldB = b.fieldB 
AND a.fieldC = b.fieldC
);

edited Oct 30 '17 at 17:42

answered Oct 30 '17 at 16:55

AnouarZ

1,087
8
23

NOT IN was like clear water for me, works perfect and I understand it perfectly. Thank you very much! – Patricio Carvajal H. Oct 30 '17 at 17:48
@PatricioCarvajalH. Glad it helps, You are so welcome :). – AnouarZ Oct 30 '17 at 17:52

score 0 · Answer 2 · answered Oct 30 '17 at 16:37

0

You can try to concat fields and then compare them all at once

SELECT a.fieldA, a.fieldB, a.fieldC
FROM TableA a 
LEFT JOIN TableB b ON (
    CONCAT(a.fieldA,a.fieldB,a.fieldC) <> CONCAT(b.fieldA,b.fieldB,b.fieldC)
)

answered Oct 30 '17 at 16:37

YouneL

8,152
2
28
50

This is almost a cross join. – Paul Spiegel Oct 30 '17 at 16:41

score 0 · Answer 3 · answered Oct 30 '17 at 16:54

0

Try this query it works:

select * from TableA where (fieldA,fieldB,fieldC) not in (select * from TableB);

answered Oct 30 '17 at 16:54

Vijesh

795
3
9
23

ScaisEdge · Answer 4 · 2017-10-30T17:05:28.523

Using the left join the column of the left related table that don't match return null values so just filter left table columns where is null

  SELECT a.fieldA, a.fieldB, a.fieldC
  FROM TableA a 
  LEFT JOIN TableB b ON 
    a.fieldA = b.fieldA 
      AND a.fieldB = b.fieldB 
        AND a.fieldC = b.fieldC
  WHERE a.fieldA = 1 
  AND a.fieldC = 1
  AND ( b.fieldC is null  OR b.fieldA is null OR a.fieldB is null)

or you can use NOT IN for same condition

  SELECT a.fieldA, a.fieldB, a.fieldC
  FROM TableA a 
  WHERE a.fieldA = 1 
  AND a.fieldC = 1
  AND (a.fieldA, a.fieldB, a.fieldC) NOT IN (
    SELECT b.fieldA, b.fieldB, b.fieldC
    FROM TableB b
    WHERE b.fieldA = 1 
    AND b.fieldC = 1
  )

MySQL: Select from table A which does not exist in table B

4 Answers4