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
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!
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).