SQL Null Functions | Academic Block

SQL NULL FUNCTIONS

Learn how to work with NULL values in SQL using SQLite-compatible functions such as COALESCE, IFNULL, NULLIF, and CASE.

What are SQL NULL Functions?

SQL NULL Functions are functions and expressions used to handle missing or unknown values in database columns. They can replace NULL values, compare values, or return alternative results when a value is missing.

In SQLite, commonly used techniques for handling NULL values include COALESCE(), IFNULL(), NULLIF(), and CASE.

Common NULL Functions

Function	Purpose	Example
`COALESCE()`	Returns the first non-NULL value	`COALESCE(phone, 'Not Available')`
`IFNULL()`	Replaces a NULL value with another value	`IFNULL(phone, 'Not Available')`
`NULLIF()`	Returns NULL when two values are equal	`NULLIF(score, 0)`
`CASE`	Handles NULL values using conditions	`CASE WHEN phone IS NULL THEN ...`

Using COALESCE()

The COALESCE() function returns the first non-NULL value from a list of expressions. It is useful when you have several possible values and want to use the first available one.

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  phone TEXT,
  email TEXT
);

INSERT INTO customers (customer_id, customer_name, phone, email)
VALUES
  (1, 'Aisha', NULL, 'aisha@example.com'),
  (2, 'Kabir', '9876501234', NULL),
  (3, 'Mira', NULL, NULL),
  (4, 'Dev', '9123405678', 'dev@example.com');

SELECT
  customer_name,
  COALESCE(phone, email, 'No contact information') AS contact
FROM customers;

COALESCE() with Multiple Values

COALESCE() can accept multiple expressions. SQLite checks them from left to right and returns the first value that is not NULL.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  work_phone TEXT,
  personal_phone TEXT,
  email TEXT
);

INSERT INTO employees
(employee_id, employee_name, work_phone, personal_phone, email)
VALUES
  (1, 'Riya', NULL, '9811100011', 'riya@example.com'),
  (2, 'Arjun', '9822200022', NULL, 'arjun@example.com'),
  (3, 'Neha', NULL, NULL, 'neha@example.com'),
  (4, 'Vivek', NULL, NULL, NULL);

SELECT
  employee_name,
  COALESCE(
    work_phone,
    personal_phone,
    email,
    'No contact available'
  ) AS preferred_contact
FROM employees;

Using IFNULL()

SQLite provides the IFNULL() function to replace a NULL value with a specified alternative value. It accepts two arguments.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  discount REAL
);

INSERT INTO products (product_id, product_name, discount)
VALUES
  (1, 'Wireless Mouse', 100),
  (2, 'USB Hub', NULL),
  (3, 'Keyboard', 250),
  (4, 'Webcam', NULL);

SELECT
  product_name,
  discount,
  IFNULL(discount, 0) AS final_discount
FROM products;

IFNULL() in Calculations

NULL values can affect calculations. IFNULL() can provide a default value before performing the calculation.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  item TEXT NOT NULL,
  price REAL NOT NULL,
  discount REAL
);

INSERT INTO orders (order_id, item, price, discount)
VALUES
  (1, 'Backpack', 1800, 200),
  (2, 'Water Bottle', 700, NULL),
  (3, 'Notebook Set', 450, 50),
  (4, 'Desk Organizer', 900, NULL);

SELECT
  item,
  price,
  IFNULL(discount, 0) AS discount,
  price - IFNULL(discount, 0) AS final_price
FROM orders;

Using NULLIF()

The NULLIF() function compares two expressions. If they are equal, it returns NULL. Otherwise, it returns the first expression.

SELECT
  NULLIF(25, 25) AS same_values,
  NULLIF(25, 10) AS different_values;

Using NULLIF() with Data

NULLIF() can convert a special value, such as zero, into NULL. This can be useful when zero represents missing or unavailable information.

DROP TABLE IF EXISTS survey;

CREATE TABLE survey (
  response_id INTEGER PRIMARY KEY,
  participant TEXT NOT NULL,
  score INTEGER NOT NULL
);

INSERT INTO survey (response_id, participant, score)
VALUES
  (1, 'Anika', 85),
  (2, 'Rahul', 0),
  (3, 'Sonia', 92),
  (4, 'Karan', 0);

SELECT
  participant,
  score,
  NULLIF(score, 0) AS usable_score
FROM survey;

Using CASE with NULL

The CASE expression can test whether a value is NULL using IS NULL and return a descriptive result.

DROP TABLE IF EXISTS students;

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL,
  project_score INTEGER
);

INSERT INTO students (student_id, student_name, project_score)
VALUES
  (1, 'Tara', 88),
  (2, 'Aman', NULL),
  (3, 'Ishita', 94),
  (4, 'Rohan', NULL);

SELECT
  student_name,
  CASE
    WHEN project_score IS NULL THEN 'Not submitted'
    ELSE CAST(project_score AS TEXT)
  END AS project_result
FROM students;

Checking for NULL Values

To find NULL values, use IS NULL. Do not use = NULL, because NULL represents an unknown value and is handled differently from ordinary values.

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
  contact_id INTEGER PRIMARY KEY,
  contact_name TEXT NOT NULL,
  phone TEXT
);

INSERT INTO contacts (contact_id, contact_name, phone)
VALUES
  (1, 'Maya', '9001100011'),
  (2, 'Dev', NULL),
  (3, 'Sara', '9002200022'),
  (4, 'Kunal', NULL);

SELECT contact_name
FROM contacts
WHERE phone IS NULL;

Checking for NOT NULL Values

Use IS NOT NULL when you want to retrieve only records that contain a value in a particular column.

DROP TABLE IF EXISTS deliveries;

CREATE TABLE deliveries (
  delivery_id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  tracking_code TEXT
);

INSERT INTO deliveries (delivery_id, customer, tracking_code)
VALUES
  (1, 'Riya', 'TRK1001'),
  (2, 'Aarav', NULL),
  (3, 'Meera', 'TRK1003'),
  (4, 'Kabir', NULL);

SELECT customer, tracking_code
FROM deliveries
WHERE tracking_code IS NOT NULL;

COALESCE() with Aggregate Results

COALESCE() can also be used with aggregate functions to provide a default result when an expression produces NULL.

DROP TABLE IF EXISTS payments;

CREATE TABLE payments (
  payment_id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  amount REAL
);

INSERT INTO payments (payment_id, customer, amount)
VALUES
  (1, 'Asha', 1200),
  (2, 'Asha', 800),
  (3, 'Dev', NULL),
  (4, 'Mira', 1500);

SELECT
  customer,
  COALESCE(SUM(amount), 0) AS total_paid
FROM payments
GROUP BY customer
ORDER BY customer;

COALESCE() vs IFNULL()

Both COALESCE() and IFNULL() can replace NULL values. The main difference is that IFNULL() accepts two arguments, while COALESCE() can check multiple expressions.

SELECT
  IFNULL(NULL, 'Fallback') AS ifnull_result,
  COALESCE(NULL, NULL, 'First Available') AS coalesce_result;

Replacing NULL with a Default Label

A common use of NULL functions is displaying a meaningful label instead of showing an empty or missing value.

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT NOT NULL,
  instructor TEXT
);

INSERT INTO courses (course_id, course_name, instructor)
VALUES
  (1, 'SQL Fundamentals', 'Priya'),
  (2, 'Database Design', NULL),
  (3, 'Data Analysis', 'Rahul'),
  (4, 'Web Technology', NULL);

SELECT
  course_name,
  COALESCE(instructor, 'Instructor not assigned') AS instructor
FROM courses
ORDER BY course_id;

NULL Functions with Calculations

When a calculation contains NULL, the result can also become NULL. Functions such as COALESCE() can provide a fallback value before performing the calculation.

DROP TABLE IF EXISTS invoices;

CREATE TABLE invoices (
  invoice_id INTEGER PRIMARY KEY,
  item TEXT NOT NULL,
  base_price REAL NOT NULL,
  tax REAL
);

INSERT INTO invoices (invoice_id, item, base_price, tax)
VALUES
  (1, 'Laptop Stand', 2500, 450),
  (2, 'Desk Mat', 800, NULL),
  (3, 'Monitor Arm', 4200, 756),
  (4, 'Cable Organizer', 350, NULL);

SELECT
  item,
  base_price,
  COALESCE(tax, 0) AS tax,
  base_price + COALESCE(tax, 0) AS total_price
FROM invoices
ORDER BY invoice_id;

Common NULL Functions and Techniques

Function / Expression	Description	Example
`COALESCE()`	Returns the first non-NULL expression	`COALESCE(a, b, 'Unknown')`
`IFNULL()`	Replaces NULL with a specified value	`IFNULL(price, 0)`
`NULLIF()`	Returns NULL when two expressions are equal	`NULLIF(value, 0)`
`IS NULL`	Checks whether a value is NULL	`phone IS NULL`
`IS NOT NULL`	Checks whether a value is not NULL	`phone IS NOT NULL`
`CASE`	Provides conditional handling of NULL	`CASE WHEN x IS NULL THEN ...`

Advantages of NULL Functions

Handle missing data safely.
Provide meaningful default values.
Prevent unwanted NULL results in calculations.
Make query results easier to understand.
Help build reliable reports and data-processing queries.

Best Practices

Use IS NULL or IS NOT NULL to test for NULL values.
Use COALESCE() when you need to check multiple possible values.
Use IFNULL() for simple two-value NULL replacement in SQLite.
Use NULLIF() when a particular value should be treated as NULL.
Do not compare NULL using = NULL or != NULL.
Choose default values carefully so they do not misrepresent missing data.

SQL NULL Functions help you work with missing and unknown values efficiently. In SQLite, COALESCE(), IFNULL(), NULLIF(), and CASE provide flexible ways to handle NULL values in queries and calculations.

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.