Database:NULL-representing lack of value

From Juneday education
Jump to: navigation, search

Meta

Expand using link to the right to see the meta section with instructor's notes.

Description

Null values are the way databases represent the lack of a value in a row for some column. As we'll see, this can be quite useful. It is important, however, that the student understands that NULL has a special meaning in databases.

A numeric value can have the value 0 (zero). That is not the same as "no value at all", 0 is still a number and a value. A string (e.g. type TEXT or VARCHAR) can be empty. An empty string can be expressed in SQL for SQLite3 as two single quotes with nothing in between them. Now, this is still considered to be a value, the "empty string" value.

NULL is what we use when there is no value at all. This has a special meaning in SQL and has actually many implications as well as applications. We introduce NULL and give a use case in the lecture for this chapter. Prepare Expect this lecture to take about 20 minutes. We recommend that you read the online text and see the lecture before giving this lecture.

As a motivation or cliff-hanger for this lecture, you can use the example in the lecture with the following situation:

sqlite> CREATE TABLE book(title TEXT, author TEXT,
...> isbn TEXT PRIMARY KEY NOT NULL, publisher_id INTEGER);
sqlite> CREATE TABLE
...> publisher(publisher_id INTEGER PRIMARY KEY NOT NULL,
...> name TEXT UNIQUE NOT NULL);

Ask the following question at the start of the lecture:

Every book will have a publisher, described further in the publisher table. But if we have publishers in the publisher table for which we have no books in the books table, how can we find and list those publishers?

Heads-up

Think about:

  • Students struggle to understand the difference between an empty string and NULL
    • Introducing the .nullvalue command at least makes the difference visually obvious
  • Knowing about NULL is a prerequisite for understanding LEFT OUTER JOINs
  • In SQLite (and some other databases) NULL comparisons are special. Two NULLs aren’t considered equal - this is something that students may fail to understand or predict.
  • The operator to use for comparing with NULL is the operator IS
  • To prevent NULL values, the NOT NULL constraint can be used
    • Constraints are introduced in a later lecture, however
  • Inner JOINs filter out rows with no value for a requested column
    • This is best shown and explained using an example such as the one in the lecture

Introduction

Null values are the way databases represent the lack of a value in a row for some column. As we'll see, this can be quite useful. It is important, however, that you understand that NULL has a special meaning in databases.

A numeric value can have the value 0 (zero). That is not the same as "no value at all", 0 is still a number and a value. A string (e.g. type TEXT or VARCHAR) can be empty. An empty string can be expressed in SQL for SQLite3 as two single quotes with nothing in between them. Now, this is still considered to be a value, the "empty string" value.

NULL is what we use when there is no value at all. This has a special meaning in SQL and has actually many implications as well as applications. We'll introduce NULL and give a use case in the lecture for this chapter.

The difference between 0 (zero), '' (empty string) and null:

-- Type is different:
-- 0 has type integer:
sqlite> SELECT typeof(0);
typeof(0) 
----------
integer   

-- empty string has type text:
sqlite> SELECT typeof('');
typeof('')
----------
text      

-- null has type null (!)
sqlite> SELECT typeof(null);
typeof(null)
------------
null

-- Equality checks using the IS operator (0 means false, 1 means true)
sqlite> SELECT 0 IS null;
0 IS null 
----------
0         

sqlite> SELECT '' IS null;
'' IS null
----------
0

-- Only null is null         
sqlite> SELECT null IS null;
null IS null
------------
1

How does null end up in a row, you might wonder. The way that happens, is when you do an INSERT to a table and leave out some colum(s):

CREATE TABLE book(title TEXT, author TEXT, isbn TEXT PRIMARY KEY NOT NULL);

-- Insert but leave out the title column and value:
sqlite> INSERT INTO book (author, isbn) VALUES('Henrik and Rikard', '1234568');

-- Now we have NULL as the value for title on that row
-- Make null visible to us as NULL:
.nullvalue NULL

sqlite> SELECT * FROM book WHERE isbn = '1234567';
title       author             isbn      
----------  -----------------  ----------
NULL        Henrik and Rikard  1234567

Exercises

We put a few exercises here on the same page as the lecture, theory and examples, since we have only a few. We don't provide solutions to these exercises. You should consult with a class mate, teacher or supervisor if you are unsure of your answers.

Ex 1

Investigate if two NULL values are the same using the = operator and a select:

sqlite> SELECT NULL = NULL;

What was the result? Remember, in SQLite3, 1 represents TRUE and 0 represents FALSE.

Ex 2

Investigate if two NULL values are the same, using the IS operator:

sqlite> SELECT NULL is NULL;

What was the result?

Ex 3

Create the following table:

sqlite> CREATE TABLE singer(first_name TEXT, last_name TEXT);

Insert six first names only:

sqlite> INSERT INTO singer(first_name) VALUES ('Janis'), ('Sandy'), ('Joni'), ('June'), ('Marta'), ('Patti');

Select all first_name from singer using last_name = null in the WHERE clause.

How many names were printed? Why?

Select all first_name from singer using last_name IS NULL.

How many names were printed? Why?

Ex 4

Count how many rows there are in the singer table with a NULL last name. You can use COUNT(*) together with a where clause using the appropriate operator for checking against NULL.

Ex 5

Update every row in the singer table with a NULL last_name to have the empty string as last name.

Lecture slides and videos

Links

Further reading

Where to go next

« PreviousBook TOCNext »