MySQL query using 'in' operator: why different results w/ quotes?

Question

I've tracked down a weird MySQL problem to the two different ways I was performing a query. When you boil everything down, this way returns more results: ``` SELECT DISTINCT FROM WHERE promo_detail_store_id in (8214, 8217, 4952, 8194, ...) ``` This change to the WHERE clause produces a subset of those results: ``` WHERE promo_detail_store_id in ('8214, 8217, 4952, 8194, ...') ``` (promo_detail_store_id is defined as a BIGINT in a MyISAM table.) Originally that list of store_ids was much longer, and I started cutting it shorter and shorter thinking maybe there was some weird limits on the length of a string. But no, it holds for quite small strings/lists too. Clearly something is going on behind the scenes involving type coercion and maybe how the 'in' operator works. Can someone enlighten me?

Accepted Answer

`WHERE promo_detail_store_id in (8214, 8217, 4952, 8194, ...)`

means

```
WHERE promo_detail_store_id = 8214 
OR  promo_detail_store_id = 8217
OR promo_detail_store_id = 4952 
OR promo_detail_store_id = 8194
OR ... 
```

```
WHERE promo_detail_store_id in ('8214, 8217, 4952, 8194, ...')
```

means

```
 WHERE promo_detail_store_id = '8214, 8217, 4952, 8194, ...'
```

`'8214, 8217, 4952, 8194, ...'` will cast to number to be `8214`, so it will be

```
WHERE promo_detail_store_id = 8214
```

MySQL query using 'in' operator: why different results w/ quotes?

Solution

Problem