- Tutorial Querying Null-valued Rows: Databases for Developers
- Description An introduction to handling null, including three-valued logic and null related functions. Click "Execute Prerequisite SQL" to create the table you need for this tutorial.
- Tags nulls
- Area SQL General
- Contributor Chris Saxon (Oracle)
- Created Friday April 06, 2018
- Modules 8

## Prerequisite SQL

`create table toys ( toy_id integer not null primary key, toy_name varchar2(100) not null, weight number(10, 2) not null, quantity_of_stuffing integer, volume_of_wood integer, times_lost integer ); insert into toys values (1, 'Mr. Penguin', 50, 100, null, 10); insert into toys values (2, 'Blue Brick', 10, null, 10, null); insert into toys values (3, 'Red Brick', 20, null, 20, 1); commit;`

## Nothing equals null!

Null is neither equal to nor not equal to anything. The result of:

null = <anything>

is always unknown. So the following query will

**always**return no rows:`select * from toys where volume_of_wood = null;`

This is also the case when you check if a column is not equal to null:

`select * from toys where volume_of_wood <> null;`

## Is null condition

To find rows that have a null-value, use the

**"is null"**condition.This query finds all the rows storing null in volume_of_wood:

`select * from toys where volume_of_wood is null;`

**Try it!**Insert this incomplete query the editor. Complete it to find the row where the times_lost is null ('Blue Brick'):

`select * from toys where`

## Nulls in range comparisons

If you want to find the toys with a volume of wood less than 15, you can write:

`select * from toys where volume_of_wood < 15;`

But this will exclude rows where the volume_of_wood is null. If you want to include these, you must also test for this:

`select * from toys where volume_of_wood < 15 or volume_of_wood is null;`

## Null functions

Oracle Database includes many functions to help you handle nulls. NVL and coalesce are two that map nulls to non-null values.

**NVL**This takes two arguments. If the first is null, it returns the second:

`select toy_name, volume_of_wood, nvl ( volume_of_wood , 0 ) mapped_volume_of_wood from toys;`

**Coalesce**This is like NVL. But it can take any number of arguments. It returns the first non-null value it finds:

`select t.*, coalesce ( volume_of_wood , 0 ) coalesce_two, coalesce ( times_lost, volume_of_wood , quantity_of_stuffing, 0 ) coalesce_many from toys t;`

You can use these functions in the where clause to map nulls to a real value. So you no longer need a separate "or column is null" test.

For example, these return the same rows as the final query in section 5:

`select * from toys where nvl ( volume_of_wood , 0 ) < 15; select * from toys where coalesce ( volume_of_wood , 0) < 15;`

## Magic Values

Null complicates your where clause. So some people are tempted to use "magic values" instead of null for missing or not applicable information.

For example, Mr. Penguin is made of fluff, not wood! So to show the volume_of_wood doesn't apply, you could set this to the "impossible" value of minus one.

Run this update to do this:

`update toys set volume_of_wood = -1 where volume_of_wood is null; select * from toys;`

You can now use standard comparison logic to find all the rows with a volume of wood less than 15 or where it doesn't apply:

`select * from toys where volume_of_wood < 15;`

At first glance this seems to make your code simpler. But it brings complications elsewhere. For example, say you're analysing the volume of wood used in your toys. You want to find the mean, standard deviation and minimum values for this.

Only Blue Brick and Red Brick use wood. So these calculations should return 15, 7.07 (rounded), and 10 respectively.

But if you run the query below, you get different results:

`select avg ( volume_of_wood ), stddev ( volume_of_wood ), min ( volume_of_wood ) from toys;`

This is because it includes the "impossible" value of -1 for Mr. Penguin.

To avoid this, you need to check that the volume_of_wood is greater than or equal to zero:

`select avg ( volume_of_wood ), stddev ( volume_of_wood ), min ( volume_of_wood ) from toys where volume_of_wood >= 0;`

So while magic values appear to make life easier, they often bring bigger problems elsewhere.