CREATE TABLE `store_loan` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `local_name` varchar(64) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

insert  into `store_loan`(`id`,`local_name`) values 

(1,'Cash Advance'),

(2,'Cash Advance'),

(3,'Rollover'),

(4,'AL Installment'),

(5,'AL Installment'),

(6,'AL Installment'),

(7,'AL Installment'),

(8,'AL Installment'),

(9,'AL Installment'),

(10,'Internet Payday'),

(11,'Rollover - Internet Payday'),

(12,'AL Monthly Installment'),

(13,'AL Semi-Monthly Installment');

## Make sure @@Optimizer_switch semijoin=on is set

## current settings in my env is: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off

##

## I would expect both queries below to return the same result set since the sub-queries in query 1 returns the same data s the IN() clause in query 2

#Query 1

SELECT SQL_NO_CACHE sl.id 

FROM store_loan sl

WHERE (

	sl.id IN (SELECT A.id FROM store_loan AS A WHERE A.local_name IN (SELECT B.local_name FROM store_loan AS B WHERE B.id IN (0,4,12,13,1,10,3,11)))  

	OR 

	(sl.id IN (0,4,12,13,1,10,3,11))

);

+----+

| id |

+----+

|  1 |

|  3 |

|  4 |

| 10 |

| 11 |

| 12 |

| 13 |

+----+

7 rows in set (0.01 sec)

#Query 2

SELECT SQL_NO_CACHE sl.id 

FROM store_loan sl

WHERE (

 	sl.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13)

	OR 

	(sl.id IN (0,4,12,13,1,10,3,11))

);

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  5 |

|  6 |

|  7 |

|  8 |

|  9 |

| 10 |

| 11 |

| 12 |

| 13 |

+----+

13 rows in set (0.00 sec)