database concepts

Using a database: multiple table queries

NULL in queries

NULL behaviours

The following example illustrates the different behaviour of NULL when compared to 'normal' vlaues.

1. Look at the following two queries:

     SELECT *
     FROM Item
     Where ItemPrice = 600 OR ItemPrice <> 600;		      SELECT*
     FROM Item;

Do they always return the same rows?

Why?

Showme

No, if a NULL value exists in the ItemPrice column the two queries will not return the same result.

The second query (SELECT * FROM Item;) will always return all the rows in the table regardless of any NULL values.

The first query will not return any rows that have NULL in the ItemPrice column. If a row has a NULL value for ItemPrice both conditions (ItemPrice = 600, ItemPrice <> 600) will return UNKNOWN and thus the row will not be included in the output for the SELECT.

Note that for all rows which have a non-NULL value for ItemPrice, one of the two conditions must be true - a value must equal 600 or not equal 600. These rows will all be included in the output.

Use of NULL

NULL is ignored in aggregation functions (MIN, MAX, SUM, AVG and COUNT). NULL never contributes to a sum, average or count and can never be the minimum or maximum of a column.

2. Look at the following two queries:

      SELECT COUNT(*)
      FROM Items;		      SELECT COUNT(ItemPrice)
     FROM Item;

Do they always return the same result?

Why?

Showme

The first query counts the number of items while the second query counts the number of items we have a price for. If ItemPrice is never NULL the results of the two queries will be the same. However if ItemPrice can be NULL the result of the two queries need not be the same.