CODEWITHSUNDEEP
mysqlsqldatabasenull
CSstudZ
CSstudZ

Reputation: 121

When to pay attention to NULL values in SQL queries?

I know that you should not use the clause NOT IN when you have NULL values you're trying to compare to. Also, an arithmetic operation involving a NULL returns NULL.

However in other situations NULL values don't cause issues, for instance, there is no problem with operations such as MAX, MIN…

So, what are situations/operations where one should be aware of NULL values?

Thank you!

Upvotes: 0

Views: 75

Answers (2)

Uueerdo
Uueerdo

Reputation: 15941

Most operations or comparisons involving NULL result in NULL (which is a not "true" value in a boolean sense, but doesn't equal false either).

The most notable exceptions are:

  • aggregate functions (such as sum, min, max, etc...) which generally ignore NULL values unless they are the only values fed to them
  • functions and operators that are meant for specifically dealing with such values, like IFNULL(), COALESCE(), x IS NULL, x <=> y, etc...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You should always pay attention to NULL values. They are valid values for all SQL types (or almost all, maybe there are a handful of exceptions somewhere). They can also be produced in the course of normal query operations (via outer joins, for instance).

What do they effect? A close approximation is that all functions and operators are affected by NULL values; most return NULL if any values are NULL. There ARE exceptions of course.

In addition, NULL values cause JOINs to fail, unless special care is taken.

Unless you rigorously define all columns as NOT NULL and avoid outer joins, you should always be thinking about how NULL values would affect the logic.

Upvotes: 2

Related Questions