MySql, CONCAT and NOT IN, string values

Question

I have such query but it doesn't select anything, but it should. So query

SELECT * 
FROM _custom_access_call 
WHERE CONCAT(type, name) NOT IN ('string1', 'string2', 'string3')

I manually add to table entry with null and '1sfgsg' values but it wasn't selected. Why? I need to select all entries that concat values is not in array. Help to deal with it.

Federico Razzoli · Accepted Answer · 2013-12-08 13:37:20Z

4

If one of the values is NULL, then CONCAT() will return NULL. And NULL NOT IN (...) is always NULL. Also NULL IN (...) is always NULL. If you want to use NULL you should explicitally handle it. In this specific case, CONCAT_WS() helps, because it never returns NULL.

SELECT * 
FROM _custom_access_call 
WHERE CONCAT_WS('', type, name) NOT IN ('string1', 'string2', 'string3');

Also, note that this query cannot use any index.

answered Dec 8, 2013 at 13:37

Federico Razzoli

5,5411 gold badge23 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

MySql, CONCAT and NOT IN, string values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related