Re: NULL versus NOT NULL usage

In general, nulls are where there is no value specified for a column on a row.

You need to be extra careful/conscious of these when using them. For one, Oracle doesn't assume a null equals a null, but if YOU want to asume that in a SQL statement, you need to use NVL to default a constant "dummy" value into the columns when it they are null in order for your join to work.

On another note, if you are doing mathmatical functions, like a price times quantity to arrive at an extended price and quantity can be null, you also need NVL to default something like 0 or 1 (depending on what null means to you) in your computation. Depending on your data, nulls in a numeric column can mean either or you may opt to ignore any rows where the column is null altogether. Otherwise, Oracle will ignore that row of data if you try to use a null value in a mathematical operation.

In short, Oracle basically assumes nothing about a null value except that it is an unknown (more or less), so you have to be explicit when working with nulls.

-- 
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)