Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Sometimes you don't have all the information filled out in a row. Whether that's by the design of your database or because someone failed to enter anything, it can be handy to retrieve rows with or without information missing.
-
0:00
Remember earlier when we wanted to find loans where we wanted to find loans where
-
0:04
the due date was after December 18th, 2015 because we wanted to send out reminders
-
0:08
to those who need to return their books soon.
-
0:11
In other words, what are the loans that are due back after December 18th, 2015?
-
0:17
In the returned_on column we see there's missing values in some rows.
-
0:22
That's because these books haven't been returned yet.
-
0:25
The rows with the dates in them are returned.
-
0:28
We don't want to bug someone who's already returned his or her book.
-
0:32
We need to find those rows with missing values.
-
0:35
We can't just write return _ on equals or nothing.
-
0:40
We'll get a syntax error.
-
0:46
There's a special way in SQL to represent a missing value, and
-
0:50
that's by writing null.
-
0:52
A missing value is known as a null value, but
-
0:56
when we use null, you cannot use it with the equality operator.
-
1:01
We have to use null in conjunction with the is key word.
-
1:07
See how this only brings back loans that haven't been returned.
-
1:11
So, what if we wanted to send a thank you email
-
1:14
to those who have returned their books?
-
1:17
We can use is not null.
-
1:24
Thank you patron number four.
-
1:25
Let's see who that is.
-
1:27
Select first name,
-
1:31
email FROM patrons WHERE id = 4
-
1:40
So to recap a missing value can be expressed in SQL as null.
-
1:46
You can search for null or
-
1:47
missing values using the key word is followed by the value null.
-
1:53
To filter out missing values you can use IS NOT NULL.
You need to sign up for Treehouse in order to download course files.
Sign up