What is better in IN()? Sub-query or list of values

If search or update rows and use where statement IN() - is better to select values with sub-query or define values or there is no efficiency difference between this? For example:

SELECT a FROM b WHERE c IN( SELECT c FROM x )

OR

SELECT a FROM b WHERE c IN(1,2,3,4,5,6,...hundreds values...)

If you have the values in a table and the column is indexed, I would expect it to be more efficient than using a list of values. Note that there are several ways to so this - especially because this type of queries (WHERE c IN (SELECT c FROM x)) is not fully optimized in some, not so old, MySQL versions:

SELECT a FROM b WHERE c IN ( SELECT c FROM x ) ;

You could rewrite (if x (c) has no NULL values) as:

SELECT a FROM b WHERE EXISTS ( SELECT 1 FROM x WHERE x.c = b.c ) ;

or (if x (c) has unique values) as:

SELECT b.a FROM b JOIN x ON x.c = b.c ;

So, now you have 4 versions to test. Actual behaviour of course depends on several more factors (besides query, indexing, versions) like table sizes, values distributions, mysql settings, memory available, other queries running, moon phase, disk efficiency, etc.

In all, the best thing to do is test in your environment, all the available options and choose what works best for you.

StackExchange Database Administrators Q#64426, answer score: 8