Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does Oracle 'NOT LIKE' expression do not return NULL?

Tags:

null

oracle

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.

like image 467
user1191463 Avatar asked Feb 13 '12 19:02

user1191463


People also ask

Can function return NULL value in Oracle?

A function can return null. Several Oracle functions return null in some situations.

IS NOT NULL expression?

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.

Is null or not like?

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

IS NOT NULL vs <> in Oracle?

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


2 Answers

NULL is not matched with LIKE. You have to explicitly ask for it with OR firstname IS NULL

like image 108
Kai Sternad Avatar answered Nov 12 '22 06:11

Kai Sternad


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.

like image 24
a_horse_with_no_name Avatar answered Nov 12 '22 06:11

a_horse_with_no_name