SQL NULLs

NULL is UNKNOWN or MISSING data. NULL affects the integrity of the database.

In order to prevent nulls from inserted in the database, table should have NOT NULL constraint.

Two ways NOT NULL constraint can be implemented on database:

1) Implement Constraint when Table is created

CREATE TABLE ConstraintTable
(ID INT, ColSecond INT NOT NULL)

2) Implement Constraint after Table is created

ALTER TABLE ConstraintTable
ALTER COLUMN ID INT NOT NULL

[Source]

An Example showing how null values are treated in aggregate functions:

Table Item_Sales_2010_Data

item_name	Sales_2010
item A	300
item B	200
item C	100
item D	NULL

Following are the results for aggregate functions:

SUM (Sales_2010) = 600
AVG (Sales_2010) = 200
MAX (Sales_2010) = 300
MIN (Sales_2010) = 100
COUNT (Sales_2010) = 3

SQL - IS NULL

In order to select only the records with NULL values in the "Email" column?

Using the IS NULL operator:

SELECT LastName, FirstName, Email
FROM EMP
WHERE Email IS NULL

SQL - IS NOT NULL

Selecting only the records with no NULL values in the "Email" column:

Using the IS NOT NULL operator:

SELECT LastName, FirstName, Email
FROM EMP
WHERE Email IS NOT NULL

ISNULL()

You can use ISNULL() function to replace NULL value with another value.

ISNULL(expression1, ValueIfNull)

The following example selects the description, Price, minimum quantity, and maximum quantity from Sales. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.

SELECT Description, Price, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales;

Another Example

Why does query A returns a result but B doesn't?

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

Query A:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

[Source]