How to work with NULL in SQL?

In SQL, NULL means the absence of a value, and it must be handled in a special way:

• Comparisons with NULL: Operators =, <> do not work with NULL as with regular values. For example, column = NULL always returns FALSE.

• Checking for NULL: Use IS NULL or IS NOT NULL to check for the presence or absence of a value.

• Handling in aggregates: Functions like COUNT(column) do not count NULL, whereas COUNT(*) counts all rows.

• Functions to replace NULL: COALESCE(column, default_value) returns the first non-NULL value from the list.

• Joining tables: When using JOIN, rows with NULL in key fields may not match, affecting the result. For example, INNER JOIN will exclude such rows, while LEFT JOIN will include them with NULL in the joined table's fields.

Example:

SELECT
  user_id,
  COALESCE(email, 'no-email@example.com') AS email_address
FROM users
WHERE last_login IS NOT NULL;

Here, we replace NULL in the email field with a default address and select only users with a known last login date.