How to easily get the unmatched condition in mysql
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.
Your query is perfectly fine for the intended use, but on MySQL the NOT IN and LEFT JOIN/IS NULL are more effecient that NOT EXISTS. Here are your alternatives:
SELECT * FROM ( SELECT 'sn1' as sn UNION ALL SELECT 'sn2' UNION ALL SELECT 'sn3' .... UNION ALL SELECT 'sn10000') as SN WHERE sn.sn NOT IN (SELECT s.id FROM SERVER s)
LEFT JOIN/IS NULL
SELECT s.id FROM SERVER s LEFT JOIN ( SELECT 'sn1' as sn UNION ALL SELECT 'sn2' UNION ALL SELECT 'sn3' .... UNION ALL SELECT 'sn10000') as SN ON SN.sn = s.id WHERE sn.sn IS NULL
You might notice I used
UNION ALL, rather than
UNION removes duplicates (which won't happen in your example), making it slower so
UNION ALL is a better choice.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]