NULL in DISTINCT, GROUP BY, and ORDER BY in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of DISTINCT with NULL values

Consider a table Employees with a column Department that contains some NULL values. What will be the result of the following query?

SELECT DISTINCT Department FROM Employees ORDER BY Department;

SQL

CREATE TABLE Employees (ID INT, Department VARCHAR(20));
INSERT INTO Employees VALUES (1, 'Sales'), (2, NULL), (3, 'HR'), (4, NULL), (5, 'Sales');

SELECT DISTINCT Department FROM Employees ORDER BY Department;

A NULL appears once at the top, followed by 'HR' and 'Sales' in alphabetical order

B NULL appears multiple times, one for each NULL row, followed by 'HR' and 'Sales'

C 'HR' and 'Sales' appear, but NULL values are excluded

D Query results in an error because NULL cannot be ordered

💡 Hint

DISTINCT removes duplicates including NULLs, and ORDER BY sorts NULLs first by default in many SQL dialects.

The DISTINCT keyword treats all NULLs as one group, so only one NULL appears. ORDER BY sorts NULLs before non-NULL values by default, so NULL appears first, then 'HR' and 'Sales' alphabetically.

❓ query_result

intermediate

GROUP BY behavior with NULL values

Given a table Orders with a column Region that contains NULL values, what will be the output of this query?

SELECT Region, COUNT(*) FROM Orders GROUP BY Region ORDER BY Region;

SQL

CREATE TABLE Orders (OrderID INT, Region VARCHAR(20));
INSERT INTO Orders VALUES (1, 'East'), (2, NULL), (3, 'West'), (4, NULL), (5, 'East');

SELECT Region, COUNT(*) FROM Orders GROUP BY Region ORDER BY Region;

A Two rows with NULL and 'East' only, 'West' is excluded

B Three rows: NULL, 'East', and 'West', with NULL last in order

C Three rows: NULL, 'East', and 'West', with NULL first in order

D Query fails because NULL cannot be grouped

💡 Hint

GROUP BY treats NULL as a group, and ORDER BY sorts NULLs first by default.

GROUP BY groups NULL values together as one group. ORDER BY sorts NULLs before non-NULL values, so the output has three rows with NULL first, then 'East', then 'West'.

📝 Syntax

advanced

Ordering NULLs explicitly in ORDER BY

Which of the following queries will correctly order the Category column with NULLs appearing last?

SELECT Category FROM Products ORDER BY Category NULLS LAST;

A SELECT Category FROM Products ORDER BY Category NULLS LAST;

B SELECT Category FROM Products ORDER BY Category NULL LAST;

C SELECT Category FROM Products ORDER BY Category IS NULL, Category;

D SELECT Category FROM Products ORDER BY Category DESC NULLS LAST;

💡 Hint

The standard syntax to put NULLs last is 'NULLS LAST' after the column name.

Option A uses the correct standard SQL syntax to order NULLs last. Option A is valid but orders descending first, which changes the order. Option A is a workaround but not standard syntax. Option A is invalid syntax.

❓ optimization

advanced

Optimizing GROUP BY with NULL values

You want to optimize a query that groups by a column Status which contains many NULL values. Which approach can improve performance while keeping NULLs grouped correctly?

A Use DISTINCT instead of GROUP BY to avoid NULL grouping overhead

B Replace NULLs with a placeholder value using COALESCE(Status, 'Unknown') in GROUP BY

C Use GROUP BY Status without changes; NULLs are grouped automatically

D Exclude NULL rows using WHERE Status IS NOT NULL before GROUP BY

💡 Hint

Replacing NULLs with a fixed value can help indexes and grouping.

Using COALESCE replaces NULLs with a known value, which can improve grouping performance and indexing. Excluding NULLs changes results. GROUP BY alone works but may be slower. DISTINCT does not replace GROUP BY functionality.

🧠 Conceptual

expert

Understanding NULL behavior in DISTINCT, GROUP BY, and ORDER BY

Which statement correctly describes how NULL values behave in DISTINCT, GROUP BY, and ORDER BY clauses in SQL?

A NULLs cause errors in DISTINCT and GROUP BY, but ORDER BY ignores NULLs

B NULLs are treated as distinct in DISTINCT, grouped together in GROUP BY, and sorted first in ORDER BY by default in many SQL dialects

C NULLs are treated as equal in DISTINCT and GROUP BY, but ORDER BY always places NULLs at the end regardless of dialect

D NULLs are treated as equal in DISTINCT and GROUP BY, and ORDER BY placement of NULLs depends on the SQL dialect and can be controlled explicitly

💡 Hint

Think about how NULLs are grouped and sorted in different SQL clauses and dialects.

NULLs are treated as equal for DISTINCT and GROUP BY, so they appear as one group. ORDER BY placement of NULLs varies by SQL dialect; some put NULLs first, others last. This can be controlled explicitly with NULLS FIRST or NULLS LAST.