NULL in DISTINCT, GROUP BY, and ORDER BY in SQL - Step-by-Step Execution

Concept Flow - NULL in DISTINCT, GROUP BY, and ORDER BY

Start Query

↓

Evaluate SELECT

↓

Apply DISTINCT

↓

Group Rows with GROUP BY

↓

Sort Rows with ORDER BY

↓

Return Result Set

The query processes rows by selecting columns, then removes duplicates with DISTINCT, groups rows with GROUP BY, and finally sorts them with ORDER BY, treating NULLs as equal in DISTINCT and GROUP BY.

Execution Sample

SQL

SELECT DISTINCT col
FROM table
GROUP BY col
ORDER BY col;

This query groups rows by 'col' and orders the result by 'col', showing how NULL values are handled in grouping and ordering.

Execution Table

Step	Action	Input Rows	Output Rows	Notes
1	Initial Rows	[1, NULL, 2, NULL, 3]	[1, NULL, 2, NULL, 3]	Original data with NULLs
2	Apply DISTINCT	[1, NULL, 2, NULL, 3]	[1, NULL, 2, 3]	NULL treated as equal, duplicates removed
3	GROUP BY col	[1, NULL, 2, 3]	[1, NULL, 2, 3]	Rows grouped; NULLs form one group
4	ORDER BY col	[1, NULL, 2, 3]	[NULL, 1, 2, 3]	NULLs sorted first by default
5	Return Result	[NULL, 1, 2, 3]	[NULL, 1, 2, 3]	Final sorted grouped distinct rows

💡 All steps complete; NULLs treated as equal in DISTINCT and GROUP BY, sorted first in ORDER BY

Variable Tracker

Variable	Start	After DISTINCT	After GROUP BY	After ORDER BY	Final
Rows	[1, NULL, 2, NULL, 3]	[1, NULL, 2, 3]	[1, NULL, 2, 3]	[NULL, 1, 2, 3]	[NULL, 1, 2, 3]

Key Moments - 3 Insights

Why does DISTINCT treat multiple NULLs as one value?

Because in SQL, NULLs are considered equal for DISTINCT, so duplicates of NULL collapse into one row as shown in step 2 of the execution_table.

How does GROUP BY handle NULL values?

GROUP BY treats all NULLs as belonging to the same group, so multiple NULL rows form one group, as seen in step 3.

Where do NULLs appear when using ORDER BY without specifying NULLS FIRST or LAST?

By default, NULLs appear first in ORDER BY ascending, as shown in step 4 where NULL is placed before 1.

Visual Quiz - 3 Questions

Look at the execution_table, after applying DISTINCT, how many NULL values remain?

A One NULL value

B Two NULL values

C No NULL values

D Three NULL values

💡 Hint

Check the 'After DISTINCT' column in the 'Rows' variable in variable_tracker and step 2 in execution_table.

At which step does the query group all NULL values into one group?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'GROUP BY col' step in execution_table where grouping happens.

If ORDER BY was changed to ORDER BY col DESC, where would NULLs appear?

A At the beginning

B In the middle

C At the end

D NULLs would be removed

💡 Hint

By default, NULLs sort last in descending order; see step 4 notes about ORDER BY behavior.

Concept Snapshot

SQL treats NULLs as equal in DISTINCT and GROUP BY, so multiple NULLs collapse into one group or distinct value.
ORDER BY sorts NULLs first by default in ascending order and last in descending order.
This affects how query results with NULLs appear when using these clauses.
Remember: NULL is not a value but treated specially in these operations.

Full Transcript

This visual execution shows how SQL handles NULL values in DISTINCT, GROUP BY, and ORDER BY clauses. Initially, the data contains multiple NULLs. When DISTINCT is applied, all NULLs are treated as one, removing duplicates. GROUP BY then groups all NULLs into a single group. Finally, ORDER BY sorts the results, placing NULLs first by default in ascending order. This step-by-step trace helps understand that NULLs are treated as equal in DISTINCT and GROUP BY, and their position in ORDER BY depends on sorting direction.

Practice

1. What happens to NULL values when using DISTINCT in SQL?

easy

A. NULL values are ignored and not shown in the result.

B. All NULL values are treated as one group and appear once.

C. Each NULL is treated as a unique value and appears multiple times.

D. DISTINCT causes an error if NULL values exist.

Solution

1. Step 1: Understand DISTINCT behavior with NULL

   In SQL, DISTINCT treats all NULL values as equal, grouping them together.

2. Step 2: Result of DISTINCT on NULL values

   This means only one NULL appears in the output, no matter how many NULLs exist.

3. Final Answer:

   All NULL values are treated as one group and appear once. -> Option B

4. Quick Check:

   NULLs grouped once = A [OK]

Hint: NULLs count as one in DISTINCT and GROUP BY [OK]

Common Mistakes:

• Thinking NULLs are ignored in DISTINCT
• Assuming each NULL is unique
• Believing DISTINCT errors on NULL

2. Which of the following is the correct syntax to select distinct values including NULLs from a table named employees on column department?

easy

A. SELECT department DISTINCT FROM employees;

B. SELECT UNIQUE department FROM employees;

C. SELECT DISTINCT(department) FROM employees;

D. SELECT DISTINCT department FROM employees;

Solution

1. Step 1: Identify correct DISTINCT syntax

   The correct SQL syntax to get unique values is SELECT DISTINCT column FROM table;. Parentheses are not used with DISTINCT.

2. Step 2: Check each option

   SELECT DISTINCT department FROM employees; uses correct syntax. SELECT UNIQUE department FROM employees; uses UNIQUE which is not standard SQL. SELECT DISTINCT(department) FROM employees; incorrectly uses parentheses. SELECT department DISTINCT FROM employees; has wrong keyword order.

3. Final Answer:

   SELECT DISTINCT department FROM employees; -> Option D

4. Quick Check:

   Standard DISTINCT syntax = D [OK]

Hint: Use DISTINCT without parentheses for unique values [OK]

Common Mistakes:

• Using UNIQUE instead of DISTINCT
• Adding parentheses after DISTINCT
• Misplacing DISTINCT keyword

3. Given the table sales with column region containing values: ('East', 'West', NULL, 'East', NULL), what will be the result of this query?

SELECT region FROM sales GROUP BY region ORDER BY region ASC;

medium

A. [NULL, 'East', 'West']

B. ['East', 'West', NULL]

C. ['East', NULL, 'West']

D. [NULL, 'West', 'East']

Solution

1. Step 1: GROUP BY groups NULLs together

   The GROUP BY groups all NULL values as one group. So NULL appears once.

2. Step 2: ORDER BY ascending places NULL first

   In ascending order, NULL values come before any text values. Then 'East' and 'West' follow alphabetically.

3. Final Answer:

   [NULL, 'East', 'West'] -> Option A

4. Quick Check:

   NULL first in ORDER BY ASC = A [OK]

Hint: ORDER BY ASC puts NULL before text values [OK]

Common Mistakes:

• Assuming NULL sorts last
• Treating NULL as a string value
• Expecting multiple NULL rows in GROUP BY

4. What is wrong with this query if the goal is to list unique category values including NULLs?

SELECT category FROM products GROUP BY category ORDER BY category;

medium

A. Query is correct and will work as intended.

B. ORDER BY cannot be used with GROUP BY.

C. Missing closing semicolon causes syntax error.

D. GROUP BY ignores NULL values, so NULLs won't appear.

Solution

1. Step 1: Check syntax and logic

   The query groups by category and orders by category. This is valid SQL syntax.

2. Step 2: Understand NULL handling

   GROUP BY includes NULL as a group, so NULL values appear once. ORDER BY sorts NULLs first by default.

3. Final Answer:

   Query is correct and will work as intended. -> Option A

4. Quick Check:

   GROUP BY with ORDER BY and NULLs works = C [OK]

Hint: GROUP BY includes NULLs; ORDER BY works with GROUP BY [OK]

Common Mistakes:

• Thinking NULLs are excluded by GROUP BY
• Believing ORDER BY can't follow GROUP BY
• Assuming missing semicolon breaks query

5. You have a table orders with a column status that can be 'Pending', 'Shipped', or NULL. You want to count how many orders exist for each status, including NULLs as a separate group. Which query correctly achieves this?

hard

A. SELECT status, COUNT(*) FROM orders GROUP BY status HAVING status IS NOT NULL ORDER BY status;

B. SELECT status, COUNT(*) FROM orders WHERE status IS NOT NULL GROUP BY status ORDER BY status;

C. SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status ASC;

D. SELECT DISTINCT status, COUNT(*) FROM orders ORDER BY status;

Solution

1. Step 1: Understand grouping with NULL

   GROUP BY status groups NULL values as one group, so counting includes NULLs separately.

2. Step 2: Analyze each option

   SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status ASC; groups all statuses including NULL and orders them ascending. SELECT status, COUNT(*) FROM orders GROUP BY status HAVING status IS NOT NULL ORDER BY status; excludes NULL with HAVING. SELECT status, COUNT(*) FROM orders WHERE status IS NOT NULL GROUP BY status ORDER BY status; filters NULL out in WHERE. SELECT DISTINCT status, COUNT(*) FROM orders ORDER BY status; uses DISTINCT with COUNT(*) incorrectly.

3. Final Answer:

   SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status ASC; -> Option C

4. Quick Check:

   GROUP BY includes NULL groups = B [OK]

Hint: Use GROUP BY without WHERE to include NULL counts [OK]

Common Mistakes:

• Filtering NULLs out before grouping
• Using HAVING to exclude NULLs unintentionally
• Misusing DISTINCT with aggregate functions