How to easily get the unmatched condition in mysql


Problem :

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.



Solution :

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:

NOT IN

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 - UNION removes duplicates (which won't happen in your example), making it slower so UNION ALL is a better choice.


Mysql Tutorials

Mysql Howto..

How to display php echo the same as you see in mysql

How to update MySql Database from threading in java [closed]

How to update a MySQL Database using a PHP form using MVC

How to do in Mysql one query to get username and same query to get another username from same users table?

MySQL duplicates — how to specify when two records actually AREN'T duplicates?

how to insert date in mysql table

how to format date field in MYSQL

How to interpret the output of MySQL EXPLAIN?

How to calculate when 75% of task was complete in MySQL

How to archive tables in MySQL InnoDB

How to get a MySQL field's content with PHP?

How to change the column name while generating model in rails

how to handle \ in query mysql

How does MySQL traverse a composite B-tree index for IN() AND IN() searches

How to make SQL script file compatible with both MYSQL and MSSQL?

How to declare variable in MySql?

how to put a create a order a message system using php and mysql

How to select distinct rows for avg aggragate function in mysql when using joins?

How do I reduce repetitive code when inserting the same metadata into identical columns over multiple tables?

How to get a highest field value in a table in MySQL?

How to deserialize in MySQL the Polygon data for fetching latitude/longitude?

How to use mysql group by query

How to seperate last name and first names into two new columns in mysql?

How to display column attribute values as column in mysql

How to convert rows data into column data in mysql by avoiding Null Values

How do I fix django.db.utils.IntegrityError: duplicate key value violates unique constraint?

How to search numbers in php mysql query?

Improving a site that is showing max_user_connections

How can I insert multiple rows into one table, then into another table using an auto incremented ID? mysql

Mysql ,how to add two new columns and insert data at the same time?