How to easily get the unmatched condition in mysql

Posted by leivli on Stack Overflow See other posts from Stack Overflow or by leivli
Published on 2010-06-12T12:34:29Z Indexed on 2010/06/12 12:42 UTC
Read the original article Hit count: 164

Filed under:

I have a "server" table which has a column named 'SN' in mysql, when do query to retrive servers with some sns from 'sn1' to 'sn10000', we can:

select * from server where sn in ('sn1','sn2','sn3',...'sn10000');

If there is only one sn in 'sn1'-'sn10000' which not exists in database, then the query above will retrive 9999 rows of result.

The question is how can I easily get which one in 'sn1'-'sn10000' is not exists in database except the additional work, such as handling the result with shell script etc.

I have an ugly sql like below can use:

select * from (select 'sn1' as sn 
        union select 'sn2' 
        union select 'sn3'
        ....
        union select 'sn10000') as SN 
where not exists (select id from server where server.sn=SN.sn);

Is Anyone has other better methods? Thanks.

© Stack Overflow or respective owner

Related posts about mysql