0

I have a simple query (actual query will be more complicated. It is a dynamic query)

SELECT * FROM Employee WHERE @firstName IS NULL OR firstName LIKE @firstName

@firstName will be passed as

  • NULL
  • '%name%'

When @firstName passed as '%name%'. The query will work fine.

When @firstName passed as NULL. The query will be:

SELECT * FROM Employee WHERE NULL IS NULL OR firstName LIKE NULL

// This query is valid syntax on MSSQL and return all employees
// because NULL IS NULL evaluated as TRUE
// And firstName LIKE NULL evaluated as FALSE

My question is

Is firstName LIKE NULL valid on all SQL databases? and will it always be evaluated as FALSE?

I have checked this LIKE syntax https://www.w3schools.com/SQl/sql_like.asp

and this https://msdn.microsoft.com/en-us/library/ms179859.aspx

But I didn't find my answer. Thanks!

LHA
  • 9,398
  • 8
  • 46
  • 85

3 Answers3

4
LIKE NULL

is not a meaningful expression for RDBMs that support or implement the SQL 92 standard.

First off, NULL [going all the way back to C.J. Date] means "unknown", so nothing can be 'LIKE' unknown.

The only valid tests for NULL are "IS NULL" or "IS NOT NULL".

Bret Weinraub
  • 1,943
  • 15
  • 21
1

Yes, LIKE NULL is valid in all RDBMS. LIKE is an operator followed by a string and a string can be null; so no problem.

Comparing a value to NULL, no matter what operator (<, <=, =, <>, LIKE, etc. - except for IS which is especially made to compare with NULL), results in UNKNOWN. UNKNOWN is not TRUE, so the condition is not met in case of NULL. And anyway, in case @firstName contains NULL, @firstName IS NULL evaluates to TRUE, so it doesn't even matter what LIKE @firstName results in then (because @firstName IS NULL OR firstName LIKE @firstName is TRUE when at least one of the two conditions is TRUE).

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • I tested LIKE NULL on Mssql, MySQL, PostgreSQL, SQLite. They worked fine. But It failed on Oracle database. – LHA Feb 27 '17 at 21:19
  • *How* did it fail? It works just just fine for me in Oracle 11.2. And I actually doubt that any Oracle version would not handle this correctly. That would be a severe bug. – Thorsten Kettner Feb 28 '17 at 07:21
  • I used sqlfiddle. I just tested again. It seems that sqlfiddle Oracle is having problem so the query didn't work. I will accept this answer. Thanks! – LHA Feb 28 '17 at 14:25
0

This has been asked before. Here are a coulpe of good answers:

like '%' does not accept NULL value

Behaviour of NOT LIKE with NULL values

If you are simply trying to check if value IS NULL- then make the syntax:

SELECT * FROM Employee WHERE firstName IS NULL

(NULL IS NULL will always return true- it is identical to "WHERE 1 = 1"). Hope this helps somewhat.

Community
  • 1
  • 1
datadevelopr
  • 53
  • 5