NULL and NOT NULL

In SQL, a NULL value represents missing or unknown data. It is not the same as zero (0) or an empty string (''). It simply means "no value."

You might have NULL in a column when data is incomplete. For example, a client might not have provided an email yet.

You can use IS NULL or IS NOT NULL to check for NULL values.

The IS NULL operator returns TRUE if the value is NULL.

Copy
SELECT name
FROM clients
WHERE email IS NULL;

The example above shows how to find clients who have not provided an email.

The IS NOT NULL operator returns TRUE if the value is not NULL.

Copy
SELECT name
FROM clients
WHERE email IS NOT NULL;

The example above shows how to find clients who have provided an email.

You cannot use = to compare with NULL. Use IS NULL or IS NOT NULL.

The NOT NULL constraint prevents a column from having missing values.

For example, the query below creates a table with NOT NULL constraints on the id and name columns.

Copy
CREATE TABLE clients (
  id INT NOT NULL,
  name TEXT NOT NULL,
  email TEXT,
  signup_date TEXT
);

This ensures id and name are always provided.

Handling NULL properly ensures data integrity, accurate filtering and reporting, and fewer unexpected results in queries.