CODEWITHSUNDEEP
mysqlsql
vppy
vppy

Reputation: 367

How to choose values from A table which are absent in B table?

I have 2 tables in mySQL DB: A & B.

a, b, c columns.

Table A: 

 a(1) = 1 
 a(2) = 2
 a(3) = 3

Table B: 

 a(1) = 1 
 a(2) = 2

So, we could see that in B table there is no row with a = 3. How could I request DB to find it?

So response (one row) could looks like: 

a(1) = 3 
b(1) =..
c(1) =..

Upvotes: 2

Views: 78

Answers (2)

MickL
MickL

Reputation: 25

You can also give up joins and use WHERE and nested SELECT: suppose TabA holds values of 1,2,3 in subsequent rows of column ValA and TabB holds values of 1,2 in subsequent rows of column ValB and you want only a row containing value of 3 from TabA you can do this without joins:

SELECT  Val_A
FROM TabA  
WHERE Val_A NOT IN (SELECT Val_B FROM TabB)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

One option uses EXISTS:

SELECT a.a
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.a = a.a);

Another option would be to do an anti-join:

SELECT a.a
FROM TableA a
LEFT JOIN TableB b
    ON a.a = b.a
WHERE b.a IS NULL;

Upvotes: 5

Related Questions