CODEWITHSUNDEEP
mysql
silverkid
silverkid

Reputation: 9563

how to get the table of missing rows in mysql

i have two mysql tables

tableA

colA1   colA2
1       whatever
2       whatever
3       whatever
4       whatever
5       whatever
6       whatever

second table is basically derived from tableA but has some rows deleted

tableB

colB1    colB2
1       whatever
2       whatever
4       whatever
6       whatever

how can i write an query to obtain the table of missing rows from the above two tables

i.e

colC1   colC2
3      whatever
5      whatever

Upvotes: 6

Views: 6356

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166376

SELECT t1.*
FROM TableA t1 LEFT JOIN
     TableB t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Upvotes: 10

Pavunkumar
Pavunkumar

Reputation: 5335

select * from  tableA where colA1 not in ( select colA1 from tableB   ) ;

Upvotes: 0

Pascal MARTIN
Pascal MARTIN

Reputation: 400952

What about something like this :

select *
from tableA
where not exists (
    select 1
    from tableB
    where tableB.colB1 = tableA.coldA1
)


i.e. you select the data from tableA for which there is no equivalent data in tableB.

Upvotes: 1

Related Questions