I try to query a table with the following query:
select * from name where firstname NOT LIKE 'PETER%'
It is not returning any records where firstname is null. Is this some error or that is how it is? I come from a SQL Server background and getting confused with many things here.
A function can return null. Several Oracle functions return null in some situations.
The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
NULL means the absence of a value, meaning that LIKE and NOT LIKE should actually evaluate to FALSE and TRUE, respectively, when dealing with NULL values, and not the nonsensical NULL. NULL in SQL is supposed to mean "unknown" rather than "no value".
<> '' means is not an empty string, so the record does contain data (that there is an empty string) and is not actually null. So a query with is not null will return records with a value of string. empty, and a query with <> '' will return values that are neither null nor empty.
NULL is not matched with LIKE
. You have to explicitly ask for it with OR firstname IS NULL
Any comparison with NULL
returns "unknown" - which in most cases is the same as "not true".
If SQL Server returns NULL values for NOT LIKE 'PETER%'
than I'd consider that a bug in SQL Server.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With