NULL in DISTINCT, GROUP BY, and ORDER BY in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does NULL represent in SQL?

NULL means a missing or unknown value. It is not the same as zero or an empty string.

beginner

How does DISTINCT treat NULL values in SQL?

DISTINCT treats all NULLs as the same value. So, multiple NULLs count as one distinct value.

beginner

How does GROUP BY handle NULL values?

GROUP BY groups all rows with NULL in the same column together as one group.

intermediate

Where do NULL values appear when using ORDER BY?

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

intermediate

Can NULL values affect the result count in DISTINCT or GROUP BY?

Yes, since all NULLs count as one value, they reduce the number of distinct groups or rows compared to counting each NULL separately.

In SQL, how does DISTINCT treat multiple NULL values in a column?

A Counts all NULLs as one distinct value

B Ignores NULL values completely

C Returns an error if NULLs exist

D Counts each NULL as a separate distinct value

DISTINCT treats all NULL values as the same, so they count as one distinct value.

When using GROUP BY on a column with NULL values, what happens to those NULLs?

A Each NULL is treated as a separate group

B They are excluded from the result

C They are grouped together as one group

D The query fails with an error

GROUP BY groups all rows with NULL in that column into a single group.

By default, where do NULL values appear when sorting with ORDER BY ascending?

A At the end of the result set

B In the middle of the result set

C At the beginning of the result set

D They are removed from the result set

Most SQL databases place NULLs last when sorting ascending, but this can vary.

Does GROUP BY consider NULL values equal or different?

A Depends on the database version

B Different, each NULL is unique

C NULLs are ignored

D Equal, all NULLs form one group

GROUP BY treats all NULLs as equal and groups them together.

If a column has multiple NULLs, how many distinct values does DISTINCT count for those NULLs?

A Zero

B One

C The number of NULLs

D It depends on the query

DISTINCT counts all NULLs as one distinct value.

Explain how NULL values are treated in DISTINCT, GROUP BY, and ORDER BY clauses in SQL.

Think about how SQL handles unknown or missing values in these clauses.

Describe the impact of NULL values on the number of groups or distinct rows returned by SQL queries using DISTINCT or GROUP BY.

Consider how NULLs are treated as equal or different.