GROUP BY with NULL values behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY with NULL values

Consider the table Employees with columns Department and Salary. Some Department values are NULL.
What is the output of this query?

SELECT Department, COUNT(*) AS Count FROM Employees GROUP BY Department ORDER BY Department;

SQL

CREATE TABLE Employees (Department VARCHAR(20), Salary INT);
INSERT INTO Employees VALUES
(NULL, 5000), ('HR', 4000), ('IT', 6000), (NULL, 4500), ('HR', 4200);

A [{"Department": "HR", "Count": 2}, {"Department": "IT", "Count": 1}, {"Department": null, "Count": 0}]

B [{"Department": "HR", "Count": 2}, {"Department": "IT", "Count": 1}]

C [{"Department": null, "Count": 2}, {"Department": "HR", "Count": 2}, {"Department": "IT", "Count": 1}]

D [{"Department": "HR", "Count": 2}, {"Department": "IT", "Count": 1}, {"Department": "NULL", "Count": 2}]

💡 Hint

Remember that NULL values are grouped together as one group in GROUP BY.

In SQL, NULL values in GROUP BY are treated as a single group. So all rows with Department NULL are counted together.

🧠 Conceptual

intermediate

How does GROUP BY treat NULL values?

In SQL, when you use GROUP BY on a column that contains NULL values, how are those NULL values treated?

A NULL values are ignored and not included in any group.

B Each NULL value is treated as a distinct group.

C NULL values cause the query to fail with an error.

D All NULL values are grouped together as a single group.

💡 Hint

Think about how NULL represents unknown but equal unknowns in grouping.

SQL treats all NULLs in a GROUP BY column as one group, so they are counted together.

📝 Syntax

advanced

Identify the error in GROUP BY with NULL handling

Which of the following SQL queries will cause a syntax error when grouping by a column that contains NULL values?

SQL

Table: Sales (Region VARCHAR, Amount INT) with some NULL Region values

A SELECT Region, SUM(Amount) FROM Sales GROUP BY Region;

B SELECT Region, SUM(Amount) FROM Sales GROUP BY Region WHERE Region IS NOT NULL;

C SELECT Region, SUM(Amount) FROM Sales GROUP BY Region HAVING Region IS NOT NULL;

D SELECT Region, SUM(Amount) FROM Sales GROUP BY Region ORDER BY Region;

💡 Hint

Remember the order of clauses in SQL SELECT statements.

The WHERE clause cannot come after GROUP BY; it must come before. Using WHERE after GROUP BY causes syntax error.

❓ optimization

advanced

Optimizing GROUP BY with NULL values filtering

You want to count rows per category but exclude NULL categories efficiently. Which query is best for performance?

A SELECT Category, COUNT(*) FROM Products WHERE Category IS NOT NULL GROUP BY Category;

B SELECT Category, COUNT(*) FROM Products GROUP BY Category HAVING Category IS NOT NULL;

C SELECT Category, COUNT(*) FROM Products GROUP BY Category;

D SELECT Category, COUNT(*) FROM Products WHERE Category <> NULL GROUP BY Category;

💡 Hint

Filtering rows before grouping reduces data processed.

Filtering NULLs in WHERE clause reduces rows before grouping, improving performance over HAVING.

🔧 Debug

expert

Why does this GROUP BY query return unexpected NULL group?

Given the table Orders with columns CustomerID and Status, the query below returns a group with NULL CustomerID unexpectedly.

SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

What is the most likely reason for this NULL group appearing?

A There are rows where CustomerID is NULL, and GROUP BY treats all NULLs as one group.

B The query is missing a WHERE clause to exclude NULL CustomerIDs, causing a syntax error.

C CustomerID column has a default value of NULL which is not allowed in GROUP BY.

D The database automatically converts NULLs to zero in GROUP BY, causing confusion.

💡 Hint

Check if any rows have NULL in the grouped column.

GROUP BY groups all NULL values together, so if CustomerID is NULL in some rows, they form a NULL group.