Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Filtering Out or Finding Missing Information

1:58 with Andrew Chalkley

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