SELECT id, subid
FROM main_table
WHERE (id NOT IN ( SELECT id FROM log_table ))
and (subid not in( SELECT subid FROM log_table))
FROM main_table
WHERE (id NOT IN ( SELECT id FROM log_table ))
and (subid not in( SELECT subid FROM log_table))
a.id=b.id and a.subid=b.subid and b.id is null
1 1 1 1
1 2 2 2
2 1
2 2
NOT IN ( SELECT right('0000000000',cast(id as char),10)+ right('0000000000',cast(subid as char),10) FROM log_table )
SELECT id, subid FROM main_table WHERE id+subid
NOT IN ( SELECT id+subid FROM log_table )
SELECT id, subid FROM main_table WHERE cast(id as varchar(10))+subid
NOT IN ( SELECT cast(id as varchar(10))+subid FROM log_table )
嘻嘻!写得玩:
SELECT id, subid FROM main_table WHERE cast(id as varchar(10))+subid
NOT IN ( SELECT cast(id as varchar(10))+subid FROM log_table )
这样写是有可能会出现问题的。
如
Id=45 subid=12时
可以组成
4,512
45,12
451,2
SELECT id, subid FROM main_table WHERE cast(id as char(10))+subid
NOT IN ( SELECT cast(id as char(10))+subid FROM log_table )
Id=45 subid=12时
可以组成
4 ,512
45 ,12
451,2
FROM main_table
WHERE main_table.id != log_table.id AND
main_table.subid != log_table.subid
这样比较简洁明了,你也可以用IN来写SELECT main_table.id, main_table.subid
FROM main_table
WHERE main_table.id NOT IN
(SELECT log_table.id
FROM log_table
WHERE main_table.subid NOT IN
(SELECT log_table.subid
FROM log_table));
where not exists(select * from b)