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.

score 4 · Accepted Answer · answered Dec 08 '13 at 13:37

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.

MySql, CONCAT and NOT IN, string values

1 Answers1