2

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) =..
vppy
  • 367
  • 2
  • 8
  • Possible duplicate of [Mysql: Select rows from a table that are not in another](https://stackoverflow.com/questions/11767565/mysql-select-rows-from-a-table-that-are-not-in-another) – trincot Dec 15 '18 at 15:56

2 Answers2

5

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
2

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)
MickL
  • 25
  • 4