Is SQL LIKE NULL valid syntax for all database?

Question

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!

Answer 1

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".

Answer 2

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.