how to get the table of missing rows in mysql

开发者_JAVA百科

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

---

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

---

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.

---

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