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
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 ) ;
精彩评论