Filtering Out or Finding Missing Information1: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.
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