Oracle SQL NULL Practice: What Does This Query Return?

Unlimited Oracle SQL practice on NULL. Each problem shows a small emp table and one query: pick what the query returns, or tap the rows that come back. A wrong answer names the slip, such as reading = NULL as IS NULL.

This is the whole emp table. NULL means the row has no value in that column.

Tap the rows in the table.Rows selected: 0

Answer to see the working.

Oracle exam prep

Studying for an Oracle SQL exam? Start with the free real exam questions, then get the full pack.

Oracle 1Z0-071272 real exam questions · US$39
Free: 68 questionsGet the full pack
Oracle 1Z0-082178 real exam questions · US$39
Free: 44 questionsGet the full pack

Oracle NULL rules FAQ

Why does NOT IN return no rows when the list has a NULL?

NOT IN compares the column with every item in the list, and all of those tests must be TRUE. A comparison with NULL is UNKNOWN, never TRUE, so the condition is FALSE or UNKNOWN for every row and no rows are returned. This is easy to miss when the list comes from a subquery. Add WHERE column IS NOT NULL to the subquery.

Why does WHERE comm = NULL return no rows?

A NULL cannot be equal or unequal to any value or to another NULL, so comm = NULL is UNKNOWN for every row, and WHERE returns only the rows where the condition is TRUE. The same goes for comm <> NULL. To test for NULL, use only IS NULL and IS NOT NULL.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows, including duplicates and NULLs. COUNT(column) counts the rows where the column is not NULL, and COUNT(DISTINCT column) counts each value that is not NULL once. COUNT never returns NULL: with nothing to count it returns 0.

Does AVG count the rows that are NULL?

No. All aggregate functions except COUNT(*), GROUPING and GROUPING_ID ignore NULLs, so AVG adds the values that are not NULL and divides by how many of those there are. To count a NULL as 0, write AVG(NVL(column, 0)), which divides by every row.

What do SUM, AVG, MIN and MAX return when every value is NULL?

NULL. If the rows hold only NULLs in the argument, or there are no rows at all, these functions return NULL, not 0. COUNT is the exception and returns 0.

Where do NULLs go in an Oracle ORDER BY?

Last in ascending order and first in descending order: NULLS LAST is the default for ASC and NULLS FIRST is the default for DESC. So ORDER BY comm DESC with FETCH FIRST 3 ROWS ONLY returns the NULL rows before the highest values. Write NULLS FIRST or NULLS LAST after the sort key to choose.

Is an empty string NULL in Oracle?

Yes. Oracle Database treats a character value with a length of zero as NULL, so name <> '' is a comparison with NULL and returns no rows. Oracle notes that this may change in a future release and recommends not treating empty strings the same as NULLs.

What is the difference between NVL, NVL2 and COALESCE?

NVL(a, b) returns b when a is NULL, and a when it is not. NVL2(a, b, c) returns b when a is not NULL and c when a is NULL. COALESCE returns the first argument in its list that is not NULL, and NULL if they all are.

What does sal + comm return when comm is NULL?

NULL. Any arithmetic expression that contains a NULL evaluates to NULL, so the row drops out of a comparison such as sal + comm > 3000 and adds nothing to SUM(sal + comm). Write sal + NVL(comm, 0) to count the NULL as 0.

What do UNION, UNION ALL, INTERSECT and MINUS return?

UNION returns all distinct rows selected by either query, and UNION ALL returns all of them, duplicates included. INTERSECT returns the distinct rows selected by both queries. MINUS returns the distinct rows selected by the first query but not the second.

Sources
  • Oracle Database 19c SQL Language Reference, Nulls: a character value with a length of zero is null, arithmetic with a null is null, a comparison with a null is UNKNOWN, and a WHERE condition that is UNKNOWN returns no rows.
  • SQL Language Reference, Logical Conditions, Null Conditions, Comparison Conditions and About SQL Conditions: the NOT, AND and OR truth tables with UNKNOWN, IS NULL, and operators evaluated before conditions.
  • SQL Language Reference, IN Condition: IN is = ANY, NOT IN is != ALL, a null in a NOT IN list returns no rows, and NOT IN with a subquery that returns no rows returns all rows.
  • SQL Language Reference, Aggregate Functions and COUNT: aggregates ignore nulls, only nulls gives null, COUNT never returns null, COUNT(*) and COUNT(expr).
  • SQL Language Reference, NVL, NVL2, COALESCE and About SQL Functions: what each function returns for a null.
  • SQL Language Reference, SELECT (order_by_clause and row_limiting_clause): NULLS LAST is the default for ascending order and NULLS FIRST for descending, and FETCH FIRST with ROWS ONLY.
  • SQL Language Reference, Set Operators and The UNION [ALL], INTERSECT, MINUS Operators: what each set operator returns.
  • The same rules are in the current release, Oracle AI Database 26ai SQL Language Reference (the 23ai addresses now open these pages).
  • Checked 20 November 2026. The tool's own approach: the emp table and the queries are invented. The drill never asks what these pages do not state: no NULL reaches UNION, INTERSECT or MINUS, the sort values that are not NULL are all different, and there is no GROUP BY. An independent re-solver, written from the quoted rules, agreed with the drill on 21,000 generated problems.

Checked 20 November 2026 against the Oracle Database SQL Language Reference, 19c and 26ai.

More free tools

All free tools