CODEWITHSUNDEEP
mysqlsql-like
Vladimir Shebuniayeu
Vladimir Shebuniayeu

Reputation: 344

MySQL searching by LIKE and AND

I have such SQL query

SELECT * FROM fl_entities WHERE  name LIKE '%FTSE%' AND source != 'ABC';

and didn't get results. But when i used

 SELECT * FROM fl_entities WHERE  name LIKE '%FTSE%' AND source is NULL;

I got the result

+----+----------+-----------+--------+--------+
| id | name     | core_name | type   | source |
+----+----------+-----------+--------+--------+
| 31 | FTSE     | FTSE      | factor | NULL   |
+----+----------+-----------+--------+--------+

What is wrong with first query? Please help.

Upvotes: 1

Views: 37

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

When comparing a column to some value (even with a negation) then null values are always excluded. If you want them in your results you need to specify that explicitely, like

SELECT * FROM fl_entities WHERE  name LIKE '%FTSE%' 
AND (source != 'ABC' OR source is null)

A little demo can be found here.

Upvotes: 1

Niagaradad
Niagaradad

Reputation: 463

NULLS are not equal to anything so you can't compare them by = or != comparisons you have to use IS NULL or IS NOT NULL.

Upvotes: 1

Related Questions