In Oracle SQL, does null = null?

Question

I can't test if null = null, since I don't have the permissions to create data on the database i'm using.

My question is:

If I have a table with two rows:

Row 1: ItemID = 1, CollectionID = 1, Price = null
Row 2: ItemID = 2, CollectionID = 1, Price = null

And my query is as follows:

SELECT CollectionID
  FROM my_table TB
 WHERE Price >= (SELECT avg(Price)
                   FROM my_table 
                  WHERE TB.CollectionID = CollectionID);

Will collectionID 1 be displayed in the results? In other words, we have null values for TB.CollectionID and for CollectionID.

Does TB.CollectionID = CollectionID ?

paxdiablo · Accepted Answer · 2016-04-18 07:02:49Z

9

No, NULL is equal to no other value, including another instance of NULL (this is true for SQL in general, not just Oracle ^(a)).

That's because NULL is not a value, rather it's an entity meaning "unknown", "not available" or "not applicable".

That's why, if you want to check if something is NULL, you use:

where something is null

rather than:

where something = null

If you do want to treat NULL values as equal, you can do something like:

where TB.CollectionID = CollectionID
   or (TB.CollectionID is null and CollectionID is null)

but that sort of goes against the whole concept of SQL tri-value logic.

And, as an aside, don't let your lack of power on your own systems limit what you can do. See SqlFiddle, for example, which can let you create and manipulate databases at will.

^(a) Though, of course, some DBMS don't always follow the standard in ways that make sense, such as a certain large vendor (no names, but it starts with o and ends with racle ^(b)) not being able to store an zero-length string into a varchar-type field, instead coercing it to be NULL :-)

^(b) Or perhaps in this case, it makes much more sense to say it starts with NULL and ends with oracle :-)

edited Apr 18, 2016 at 7:02

answered Feb 16, 2015 at 3:44

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user207421 Over a year ago

@Louis-Philippe Baillargeon Note that this is a rule of SQL. It isn't specific to Oracle.

Przemyslaw Kruglej Over a year ago

It's worth mentioning that the DECODE function is an exception to this rule and it does treat two NULLs as equal.

Collectives™ on Stack Overflow

In Oracle SQL, does null = null?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related