NULL in DISTINCT, GROUP BY, and ORDER BY in SQL

Introduction

We use NULL to represent missing or unknown values in data. Understanding how NULL behaves with DISTINCT, GROUP BY, and ORDER BY helps us organize and summarize data correctly.

When you want to find unique values in a column that may have missing data.

When grouping data to summarize, and some groups might have NULL values.

When sorting data that includes NULLs, to know where they appear in the order.

Syntax

SQL

SELECT DISTINCT column_name FROM table_name;

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

SELECT column_name FROM table_name ORDER BY column_name [ASC|DESC];

NULL values are treated as equal in DISTINCT and GROUP BY, so they appear as one group or one distinct value.

In ORDER BY, NULLs usually appear first in ascending order and last in descending order, but this can vary by database.

Examples

This finds unique cities, treating all NULL cities as one group.

SQL

SELECT DISTINCT city FROM customers;

This counts how many customers are in each city, including those with NULL city.

SQL

SELECT city, COUNT(*) FROM customers GROUP BY city;

This sorts customers by city name, with NULL cities usually appearing first.

SQL

SELECT city FROM customers ORDER BY city ASC;

Sample Program

This example shows how NULL values appear in DISTINCT, GROUP BY, and ORDER BY results.

SQL

CREATE TABLE customers (id INT, city VARCHAR(20));
INSERT INTO customers VALUES (1, 'New York'), (2, NULL), (3, 'Los Angeles'), (4, NULL), (5, 'New York');

-- Find distinct cities
SELECT DISTINCT city FROM customers;

-- Count customers per city
SELECT city, COUNT(*) FROM customers GROUP BY city;

-- Order customers by city
SELECT city FROM customers ORDER BY city ASC;

Important Notes

NULL is not the same as an empty string or zero; it means unknown or missing.

Some databases allow controlling NULL order in ORDER BY with NULLS FIRST or NULLS LAST.

Summary

NULL values are grouped together as one in DISTINCT and GROUP BY.

ORDER BY usually places NULLs before other values in ascending order.

Knowing how NULL behaves helps avoid surprises in data results.