NULL is a special state in SQL. It’s very commonly used as a placeholder for missing data (“we don’t know her address!”)

What NULL means exactly depends on your data. For example, it’s really important to know if allergies IS NULL means:

• “no allergies” or
• “we don’t know if she has allergies or not”

NULL “should” mean “unknown” but it doesn’t always.

smiling stick figure with curly hair: it would be easier if NULL always meant the same thing but it really depends on your data!

where NULLs come from

• There were already NULL values in the table
• The window function LAG() can return NULL
• You did a LEFT JOIN and some of the rows on the left didn’t have a match for

tiny pensive stick figure with curly hair: ooh, not very cat has an owner so sometimes the owner name is NULL

ways to handle NULL s *

• Leave them in!

smiling stick figure with curly hair: I’d rather see a NULL and know there’s missing data than get misleading results

• Filter them out!
  ... WHERE first_name IS NOT NULL ...

• Use COALESCE or CASE to add a default value