SQL NULL Values | Academic Block

SQL NULL VALUES

Learn what NULL means in SQL and how to work with missing, unknown, or unavailable values in SQLite.

What is a NULL Value?

In SQL, NULL represents a missing, unknown, or unavailable value. It is not the same as zero, an empty string, or a value containing spaces.

SQLite supports NULL values and provides special operators such as IS NULL and IS NOT NULL for working with them.

Inserting a NULL Value

You can explicitly insert NULL into a column when a value is not available.

CREATE TABLE devices (
  id INTEGER,
  name TEXT,
  serial_number TEXT
);

INSERT INTO devices (id, name, serial_number)
VALUES (1, 'Tablet', NULL);

SELECT * FROM devices;

NULL is Not Zero

A value of 0 is an actual numeric value. NULL means that no value is available.

CREATE TABLE inventory (
  item TEXT,
  quantity INTEGER
);

INSERT INTO inventory (item, quantity)
VALUES
  ('Notebook', 0),
  ('Marker', NULL);

SELECT * FROM inventory;

Finding NULL Values with IS NULL

Use IS NULL to find rows where a column contains a NULL value. The normal equality operator = should not be used for NULL.

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  manager TEXT
);

INSERT INTO employees (id, name, manager)
VALUES
  (1, 'Anaya', 'Raj'),
  (2, 'Kunal', NULL),
  (3, 'Meera', 'Sonia');

SELECT * FROM employees
WHERE manager IS NULL;

Finding Non-NULL Values

Use IS NOT NULL to return rows where a column contains an actual value.

CREATE TABLE courses (
  id INTEGER,
  title TEXT,
  instructor TEXT
);

INSERT INTO courses (id, title, instructor)
VALUES
  (1, 'Astronomy', 'Dr. Rao'),
  (2, 'Geology', NULL),
  (3, 'Robotics', 'Dr. Mehta');

SELECT * FROM courses
WHERE instructor IS NOT NULL;

NULL with WHERE

NULL values require special handling when filtering records with a WHERE clause.

CREATE TABLE shipments (
  id INTEGER,
  product TEXT,
  tracking_code TEXT
);

INSERT INTO shipments (id, product, tracking_code)
VALUES
  (101, 'Keyboard', 'TRK1001'),
  (102, 'Mouse', NULL),
  (103, 'Monitor', 'TRK1003');

SELECT product, tracking_code
FROM shipments
WHERE tracking_code IS NULL;

NULL is Different from an Empty String

An empty string '' is a text value containing no characters. It is different from NULL.

CREATE TABLE profiles (
  id INTEGER,
  username TEXT,
  nickname TEXT
);

INSERT INTO profiles (id, username, nickname)
VALUES
  (1, 'skywalker', ''),
  (2, 'starlight', NULL);

SELECT
  id,
  username,
  nickname
FROM profiles;

Replacing NULL with IFNULL()

SQLite provides the IFNULL() function to replace a NULL value with another value.

CREATE TABLE members (
  id INTEGER,
  name TEXT,
  phone TEXT
);

INSERT INTO members (id, name, phone)
VALUES
  (1, 'Arjun', '9876543210'),
  (2, 'Diya', NULL),
  (3, 'Kabir', '9123456780');

SELECT
  name,
  IFNULL(phone, 'Not Available') AS phone
FROM members;

Using COALESCE() with NULL

SQLite also supports COALESCE(), which returns the first non-NULL value from the supplied expressions.

CREATE TABLE contacts (
  id INTEGER,
  name TEXT,
  mobile TEXT,
  email TEXT
);

INSERT INTO contacts (id, name, mobile, email)
VALUES
  (1, 'Nisha', NULL, 'nisha@example.com'),
  (2, 'Varun', '9000011111', NULL),
  (3, 'Ira', NULL, NULL);

SELECT
  name,
  COALESCE(mobile, email, 'No Contact') AS contact
FROM contacts;

Counting NULL and Non-NULL Values

COUNT(column) counts only non-NULL values, while COUNT(*) counts all rows.

CREATE TABLE events (
  id INTEGER,
  event_name TEXT,
  venue TEXT
);

INSERT INTO events (id, event_name, venue)
VALUES
  (1, 'Tech Expo', 'Hall A'),
  (2, 'Science Fair', NULL),
  (3, 'Book Festival', 'Hall C'),
  (4, 'Art Show', NULL);

SELECT
  COUNT(*) AS total_events,
  COUNT(venue) AS events_with_venue
FROM events;

Sorting NULL Values

NULL values can also appear when sorting query results with ORDER BY.

CREATE TABLE tasks (
  id INTEGER,
  task_name TEXT,
  priority INTEGER
);

INSERT INTO tasks (id, task_name, priority)
VALUES
  (1, 'Backup Files', 2),
  (2, 'Update Website', NULL),
  (3, 'Check Database', 1),
  (4, 'Write Report', 3);

SELECT *
FROM tasks
ORDER BY priority;

NULL in Calculations

Arithmetic expressions involving NULL generally produce NULL because the missing value cannot be used as a known number.

CREATE TABLE products (
  name TEXT,
  price REAL,
  discount REAL
);

INSERT INTO products (name, price, discount)
VALUES
  ('Backpack', 1800, 200),
  ('Notebook Set', 450, NULL),
  ('Desk Organizer', 750, 50);

SELECT
  name,
  price,
  discount,
  price - discount AS final_price
FROM products;

Handling NULL in Calculations

You can use IFNULL() to treat a NULL numeric value as another number before performing a calculation.

CREATE TABLE bills (
  item TEXT,
  amount REAL,
  tax REAL
);

INSERT INTO bills (item, amount, tax)
VALUES
  ('Printer', 12000, 1200),
  ('Scanner', 8000, NULL),
  ('Projector', 25000, 2500);

SELECT
  item,
  amount,
  tax,
  amount + IFNULL(tax, 0) AS total
FROM bills;

NULL with NOT

When checking for missing values, use IS NOT NULL rather than trying to compare a column with NULL.

CREATE TABLE instructors (
  id INTEGER,
  name TEXT,
  room TEXT
);

INSERT INTO instructors (id, name, room)
VALUES
  (1, 'Dr. Sen', 'R101'),
  (2, 'Dr. Kapoor', NULL),
  (3, 'Dr. Malik', 'R203');

SELECT *
FROM instructors
WHERE room IS NOT NULL;

NULL Values with CASE

A CASE expression can be used to display a meaningful message when a column contains NULL.

CREATE TABLE applications (
  applicant TEXT,
  result TEXT
);

INSERT INTO applications (applicant, result)
VALUES
  ('Rohan', 'Selected'),
  ('Maya', NULL),
  ('Adil', 'Rejected');

SELECT
  applicant,
  CASE
    WHEN result IS NULL THEN 'Pending'
    ELSE result
  END AS application_status
FROM applications;

Common NULL Operations

Operation	Purpose	Example
IS NULL	Find NULL values	`WHERE phone IS NULL`
IS NOT NULL	Find non-NULL values	`WHERE phone IS NOT NULL`
IFNULL()	Replace NULL with a value	`IFNULL(phone, 'Unknown')`
COALESCE()	Return first non-NULL value	`COALESCE(phone, email)`
COUNT(column)	Count non-NULL values	`COUNT(phone)`

NULL vs Other Values

Value	Meaning	Example
`NULL`	Missing or unknown value	`phone = NULL`
`0`	Numeric zero	`quantity = 0`
`''`	Empty text string	`nickname = ''`
`'NULL'`	The text “NULL”	`status = 'NULL'`

Advantages of Understanding NULL

Helps represent missing or unavailable information.
Makes database records more flexible.
Allows incomplete data to be handled correctly.
Helps produce accurate filtering and reporting queries.
Functions such as IFNULL() and COALESCE() make NULL easier to handle.

Best Practices

Use IS NULL instead of = NULL.
Use IS NOT NULL when searching for available values.
Do not confuse NULL with zero or an empty string.
Use IFNULL() or COALESCE() when a fallback value is needed.
Use NOT NULL constraints when a column must always contain a value.
Consider how NULL values affect calculations and aggregate functions.

Understanding NULL values is essential for working with incomplete or unavailable data in SQL. Once you know how to identify, replace, and work with NULL values, you can write more accurate database queries.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.