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