NULL Values in SQL – Tutorial

NULL Values in SQL: Video Lesson

            This video lesson, titled “NULL Values in SQL,” discusses handling and finding NULL Values in SQL and shows an example using Microsoft Access. This video lesson is from our introductory SQL course, titled “Mastering Introductory SQL Made Easy v.1.0.”

NULL Values in SQL: Overview

            When creating tables in SQL, you will need to deal with NULL values in SQL. A NULL value is a value that is unknown. These values can occur within database tables when a user who is performing data entry skips entering a value into a field. When a value is not entered, it is said to have a NULL value. Note that a NULL value is not equal to anything, as the value is unknown. It is not equal to zero or a blank text string, such as “ ”. It is also not greater than or less than any other value. However, you will need to decide how to deal with NULL values when creating fields within tables.

            You can often choose to disallow NULL values in SQL within fields by using the NOT NULL SQL constraint when creating the fields in a table. UNIQUE, PRIMARY KEY, and FOREIGN KEY fields also cannot contain NULL values within them, as NULL values would disrupt the relational abilities of the tables. Note that unless the NOT NULL SQL constraint in employed when creating the fields within a table, the field will typically default to allowing NULL values.

            You have some SQL statements to assist you in dealing with NULL values in SQL within table fields. Since NULL values are not comparable to other existing values, you cannot use the equal, greater than, or less than sign within a SELECT statement in SQL to find NULL values within fields. When creating queries using the SELECT statement, you will often need to use the IS NULL or IS NOT NULL statements within the SELECT statement to find values within fields where the value is either NULL or not NULL. While we have not yet examined the SELECT statement, which is covered within the Data Manipulation Language chapter, you can examine how the IS NULL and IS NOT NULL clauses can be added to the core SQL of the SELECT statement to find NULL values.

            The core SQL of a simple SELECT statement is shown below. In this example, “table_name” is the name of the table, “field_name” is the name of the field(s) to display in the query, and “field_name_to_compare” is the name of the field within which you want to find NULL values or find values that are not NULL. The next example shows the core SQL used to find null values within a field.

SELECT field_name, field_name1, field_name2, etc. FROM table_name

WHERE field_name_to_compare IS NULL

            The next example will find any values where there is NOT a NULL value within the “field_name_to_compare” field.

SELECT field_name, field_name1, field_name2, etc. FROM table_name

WHERE field_name_to_compare IS NOT NULL

NULL Values in SQL – Tutorial: A picture of the main bullet points of the lesson on NULL values in SQL.

NULL Values in SQL: General Syntax

• To find null values within the “field_name_to_compare” field.

SELECT field_name, field_name1, field_name2, etc. FROM table_name

WHERE field_name_to_compare IS NULL

• To find any values where there is NOT a NULL value within the “field_name_to_compare” field.

SELECT field_name, field_name1, field_name2, etc. FROM table_name

WHERE field_name_to_compare IS NOT NULL