[Resolved] select IN question

csousley

This query is failing after IN:

select * from current where (story_status = 1 and story_page IN 'A1, A2, A3')

PHP 4.3.6 / MySQL 3.23.49 / Apache 1.3.29 / Mac OS X 10.3.4

I've tried a few different things but can't seem to get it right, thanks.

-Cy

nemonoman

Read the manual:

SELECT *
FROM current
WHERE story_status = 1
AND story_page IN ('A1', 'A2', 'A3')

csousley

First off, thank you.

Second, I checked the manual and only found stuff on optimizing subqueries and basic select info. I went back and have since found the page I needed.

http://dev.mysql.com/doc/mysql/en/EXPLAIN.html

Anyway, thanks again. At least you didn't give me the 'RTFM' thing...

nemonoman

From section on comparison operators:

expr IN (value,...)
Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion:
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1
The number of values in the IN list is only limited by the max_allowed_packet value. From 4.1 (to comply with the SQL-99 standard), IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.