MySQL query's NOT IN() condition fails to accommodate a column containing comma-separated values

Question

I have a query

SELECT * FROM `photo` p where 4 not in (2,4,5)

from the query it is not show anything because there is 4. but why if we query

SELECT * FROM `photo` p where 4 not in (user_view)

table photo:

photo ID | user_view
1        | 2,3,4,5

it is still show the record. Why is the code not running well if we use NOT IN on the database field?

How to make the same function with

SELECT * FROM `photo` p where 4 not in (2,4,5)

I want to show the record only if the photo is haven't see by user. So it will show uniquely by user who don't have an ID in user_view.

the problem is your database structure. you should normalise it — user557846
– user557846, Commented Feb 15, 2015 at 21:31

kellanburket · Accepted Answer · 2015-02-15 21:39:11Z

1

If the column user_view is a comma delimited list like it seems to be, you can use the built-in FIND_IN_SET function:

 SELECT * FROM `photo` p where FIND_IN_SET(4, `user_view`) != 0

This will check if the input '4' exists as one of the items in the user_view column and return the index number if found and 0 if not.

answered Feb 15, 2015 at 21:39

kellanburket

13k3 gold badges53 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

spencer7593 · Accepted Answer · 2015-02-15 21:41:27Z

A column storing a comma separated list is a single value. It's just a single string value that happens to contain comma characters.

Those commas are not considered to be part of the SQL text.

When you do this is a:

  foo NOT IN ( 2 , 4 , 5 )

MySQL sees that as three separate values, the commas are part of the SQL text; that's equivalent to

  ( foo <> 2 AND foo <> 4 AND foo <> 5 )

When you do this:

  foo NOT IN ( '2,4,5' )

That's equivalent to:

  foo <> '2,4,5'

(In MySQL, in a numeric context, the string literal is going to evaluate as a numeric value of 2.)

If you are asking how to "fix" this without changing your database (and storing comma separated lists is a SQL anti-pattern)

You could do this:

SELECT p.* FROM `photo` p 
 WHERE NOT FIND_IN_SET(4,p.user_view)

Or, you could do this:

SELECT p.* FROM `photo` p 
  WHERE CONCAT(',',p.user_view,',') NOT LIKE '%,4,%'