SQL NULL

What is SQL NULL?

Represents missing or undefined data in a relational database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NULL: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, and all ANSI compliant systems.

SQL NULL Full Explanation

NULL is a special marker defined by the SQL standard to denote the absence of any value in a column. It is not zero, not an empty string, and not the string "NULL". When a column contains NULL, it means the value is unknown, unavailable, or not applicable. SQL implements three-valued logic (TRUE, FALSE, UNKNOWN) to deal with NULLs, which affects comparisons, aggregates, and Boolean expressions.Key behaviors:- Comparisons with NULL using =, <, >, or <> return UNKNOWN, not TRUE/FALSE.- Use IS NULL or IS NOT NULL to test for NULL.- Aggregate functions (SUM, AVG, MAX, MIN) ignore NULLs, while COUNT(*) includes them.- Arithmetic or string operations that involve NULL generally return NULL.- Functions like COALESCE, NULLIF, ISNULL (SQL Server) and NVL (Oracle) provide ways to handle NULLs.Caveats:- NULLs can cause unexpected filtering and join results if not explicitly handled.- Unique constraints treat multiple NULLs as distinct in most dialects except SQL Server where only one NULL is allowed in a UNIQUE index (unless using a filtered index).

SQL NULL Syntax

NULL
-- test for NULL
a_column IS NULL
-- test for NOT NULL
a_column IS NOT NULL

SQL NULL Parameters

Example Queries Using SQL NULL

-- Insert a NULL value
INSERT INTO employees (id, middle_name) VALUES (1, NULL);

-- Retrieve rows where middle_name is NULL
SELECT id
FROM employees
WHERE middle_name IS NULL;

-- Replace NULL with a fallback value
SELECT COALESCE(middle_name, 'N/A') AS mid_name
FROM employees;

-- Compare NULL handling in aggregates
SELECT COUNT(*)          AS total_rows,
       COUNT(middle_name) AS non_null_middle_names
FROM employees;

Expected Output Using SQL NULL

- First statement stores a row with an unknown middle name
- Second query returns ids where middle_name is missing
- Third query outputs the actual middle name or 'N/A' if it was NULL
- Fourth query shows all rows versus how many have non-NULL middle names

Use Cases with SQL NULL

Represent data that is missing, inapplicable, or yet to be collected.
Filter datasets to find incomplete records.
Provide default values in reports or exports.
Build robust joins that correctly match NULLs using COALESCE or conditional logic.

Common Mistakes with SQL NULL

Using = NULL or <> NULL instead of IS NULL / IS NOT NULL.
Assuming COUNT(column) counts NULLs (it does not).
Forgetting that NULL comparisons return UNKNOWN, leading to unexpected filter results.
Treating NULL and empty string as equivalent (they are different in SQL).
Assuming unique indexes can store multiple identical NULLs in every dialect.

First Introduced In

SQL-86 (ANSI X3.135-1986)

Frequently Asked Questions

Why does = NULL not work?

Comparing with = or <> returns UNKNOWN because NULL represents an unknown value. Use IS NULL or IS NOT NULL.

How do I replace NULL values in results?

Wrap the column with COALESCE(col, fallback). SQL Server users can use ISNULL and Oracle users can use NVL.

Does COUNT(*) include NULLs?

Yes. COUNT(*) counts every row. COUNT(column) ignores rows where column is NULL.

Are empty strings the same as NULL?

No. An empty string has length zero while NULL means the value is unknown. Oracle is a notable exception where empty strings are treated as NULL.