Easy Tutorial
Sql Having Sql Func Mid Sql Between Sql Drop Sql Wildcards Sql Join Inner Sql Join Full Sql Func Lcase Sql Where Sql Join Sql Func Round Sql Primarykey Sql Func First Sql Func Len Sql Distinct Sql Func Ucase Sql Function Sql Delete Sql Autoincrement Func Curtime Sql Update Sql Isnull Func Date Sub Sql Constraints Sql Intro Sql Dates Sql Select Into Sql Hosting Sql Create Table Sql Func Format Sql View Sql Func Count Sql Index Sql Like Sql Datatypes General Sql Datatypes Func Convert Sql Func Avg Sql Foreignkey Sql Func Sum Sql Join Right Func Curdate Sql Alias Sql Union Func Date Add Sql Groupby Sql Summary Sql In Sql Null Values Sql Syntax Sql Tutorial Sql Insert Into Select Sql And Or Sql Join Left Sql Func Now Func Date Sql Top Index Sql Create Db Sql Unique Sql Check Func Extract Sql Alter Sql Notnull Func Now Sql Select Sql Insert Sql Func Max Sql Exists Sql Func Min Func Date Format Func Datediff Mysql Func Getdate Sql Orderby Func Datediff Sql Func Last Sql Create Index Func Dateadd Sql Quickref Func Datepart
❮ Sql In Sql Syntax ❯

SQL NULL Values

NULL values represent missing unknown data.

By default, columns in a table can hold NULL values.

This chapter explains the IS NULL and IS NOT NULL operators.

SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

NULL values are handled differently than other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: NULL cannot be compared to 0; they are not equivalent.

SQL NULL Value Handling

Consider the following "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Stavanger

If the "Address" column in the "Persons" table is optional, it means that if a record is inserted without a value in the "Address" column, the "Address" column will be saved with a NULL value.

So how do we test for NULL values?

We cannot use comparison operators like =, <, or <> to test for NULL values.

We must use the IS NULL and IS NOT NULL operators instead.

SQL IS NULL

How do we select records that have NULL values in the "Address" column?

We must use the IS NULL operator:

The result set will look like this:

LastName FirstName Address
Hansen Ola
Pettersen Kari

Tip: Always use IS NULL to find NULL values.

SQL IS NOT NULL

How do we select records that do not have NULL values in the "Address" column?

We must use the IS NOT NULL operator:

The result set will look like this:

LastName FirstName Address
Svendson Tove Borgvn 23

In the next section, we will learn about the ISNULL(), NVL(), IFNULL(), and COALESCE() functions.

❮ Sql In Sql Syntax ❯