CODEWITHSUNDEEP
mysql
Patricio Carvajal H.
Patricio Carvajal H.

Reputation: 361

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

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 :(

Upvotes: 2

Views: 1518

Answers (4)

AnouarZ
AnouarZ

Reputation: 1107

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
);

Upvotes: 9

ScaisEdge
ScaisEdge

Reputation: 133360

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
  )

Upvotes: 0

Vijesh
Vijesh

Reputation: 815

Try this query it works:

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

Upvotes: 0

YouneL
YouneL

Reputation: 8351

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)
)

Upvotes: 0

Related Questions