CODEWITHSUNDEEP
mysqloracle-databasenull
LPB
LPB

Reputation: 515

In Oracle SQL, does null = null?

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 ?

Upvotes: 0

Views: 1714

Answers (1)

paxdiablo
paxdiablo

Reputation: 881523

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 :-)

Upvotes: 9

Related Questions