I85PREP
⌘ K
ICDL Using Databases study materials
ICDL Using Databases study resources

The Query That Returns Nothing

ICDL Using Databases · Put your knowledge to work in context.

All case studies

The Query That Returns Nothing

A small library database has a Loans table with fields LoanID, MemberID, BookTitle, DateBorrowed, and DateReturned. DateReturned is left blank (empty) while a book is still out. The librarian builds a query to list all books currently on loan and enters the criterion DateReturned = Null in the design grid. The query returns zero rows, even though she knows 37 books are out. She then tries DateReturned = "" and still gets nothing useful. A colleague suggests the problem is the criterion, not the data.

1. Explain why `DateReturned = Null` returns no rows, and give the correct criterion.

Read the worked answer

Null means unknown / no value, not 'equal to nothing'. Any comparison with Null using = evaluates to unknown, so the row is not returned. That is why DateReturned = Null matches nothing, and DateReturned = "" fails too because an empty string is not the same as a null date.

The correct approach is the null test:

DateReturned IS NULL

In a query design grid this is usually entered as Is Null in the Criteria row for that field. This returns every loan where no return date has been recorded - the 37 books still out.

2. The librarian also wants the 12 books that were returned late (returned more than 14 days after borrowing). Write the logic she needs and explain why she cannot simply filter `DateReturned` alone.

Read the worked answer

Lateness depends on the difference between two dates, not on either date in isolation. Filtering DateReturned alone can only test when it came back, not how long it was out.

She needs a calculated condition comparing the two fields:

DateReturned - DateBorrowed > 14

(or the equivalent DateDiff expression, depending on the tool).

Two cautions:

  • The IS NULL rows (still out) must be excluded from this count, or the arithmetic on a null return date yields unknown and muddies results. Add DateReturned IS NOT NULL alongside the lateness test.
  • Confirm the date fields are true Date/Time types; if they are text, the subtraction will not work as intended.

3. She wants one query that lists books still out **and** books returned late, in a single result. Is combining them with OR on the same field sensible? Justify your answer.

Read the worked answer

No - the two conditions test different things and cannot both sit as simple OR criteria on DateReturned.

  • 'Still out' is DateReturned IS NULL.
  • 'Returned late' is DateReturned - DateBorrowed > 14 (with DateReturned IS NOT NULL).

A clean single result is built by putting each condition on its own criteria row joined by OR, or by a UNION of two selects. The key judgement: OR across criteria rows means 'match either condition', which is exactly what she wants here - but each row must be a complete, correct condition in its own right. Writing IS NULL OR > 14 on one line against one field is malformed and will not give the intended set.