开发者

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   ) ; 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜