Books with NULL Ratings - Problem

Given a books table containing information about books including their ratings, find all books that have not been rated yet (i.e., have a NULL rating).

The books table contains:

  • book_id: Unique identifier for each book
  • title: Book title
  • author: Book author
  • published_year: Year the book was published
  • rating: Book rating (can be NULL if not rated)

Return the result ordered by book_id in ascending order. Exclude the rating column from the output since all returned books will have NULL ratings.

Table Schema

books
Column Name Type Description
book_id PK int Unique identifier for each book
title varchar Title of the book
author varchar Author of the book
published_year int Year the book was published
rating decimal Book rating, can be NULL if not rated
Primary Key: book_id
Note: Each row represents one book. The rating column can contain NULL values for unrated books.

Input & Output

Example 1 — Mixed Rated and Unrated Books
Input Table:
book_id title author published_year rating
1 The Great Gatsby F. Scott Fitzgerald 1925 4.5
2 To Kill a Mockingbird Harper Lee 1960
3 Pride and Prejudice Jane Austen 1813 4.8
4 The Catcher in the Rye J.D. Salinger 1951
5 Animal Farm George Orwell 1945 4.2
6 Lord of the Flies William Golding 1954
Output:
book_id title author published_year
2 To Kill a Mockingbird Harper Lee 1960
4 The Catcher in the Rye J.D. Salinger 1951
6 Lord of the Flies William Golding 1954
💡 Note:

The query filters books where rating IS NULL. Books with IDs 2, 4, and 6 have NULL ratings, so they are included in the result. The other books have actual rating values (4.5, 4.8, 4.2) and are excluded. Results are ordered by book_id in ascending order.
Example 2 — All Books Have Ratings
Input Table:
book_id title author published_year rating
1 Book One Author A 2020 4
2 Book Two Author B 2021 3.5
Output:
book_id title author published_year
💡 Note:

When all books have ratings (no NULL values), the query returns an empty result set since no books match the rating IS NULL condition.
Example 3 — All Books Unrated
Input Table:
book_id title author published_year rating
1 New Book New Author 2023
3 Another Book Another Author 2023
Output:
book_id title author published_year
1 New Book New Author 2023
3 Another Book Another Author 2023
💡 Note:

When all books have NULL ratings, all books are returned in the result, ordered by book_id ascending.

Constraints

  • 1 ≤ book_id ≤ 10000
  • title and author are non-empty strings
  • published_year is a valid year
  • rating can be NULL or a decimal value between 0.0 and 5.0

Visualization

Tap to expand
Books with NULL Ratings INPUT: books table id title rating 1 SQL Guide 4.5 2 Python 101 NULL 3 Java Basics 3.8 4 Web Dev NULL 5 Data Science 4.2 6 ML Intro NULL = NULL rating rows SELECT book_id, title FROM books WHERE rating IS NULL ORDER BY book_id ASC; ALGORITHM STEPS 1 Scan Table Read each row from books 2 Check NULL Use IS NULL (not = NULL) 3 Exclude Rating Select only book_id, title 4 Sort Results ORDER BY book_id ASC NULL Comparison rating = NULL WRONG! rating IS NULL CORRECT NULL requires special handling FINAL RESULT book_id title 2 Python 101 4 Web Dev 6 ML Intro 3 Books Found Verification OK Sorted by book_id OK Rating excluded OK Only NULL ratings Key Insight: In SQL, NULL represents unknown/missing values. You cannot use = or != to compare with NULL because any comparison with NULL returns NULL (unknown), not TRUE or FALSE. Always use IS NULL or IS NOT NULL for NULL comparisons. Time Complexity: O(n) where n is the number of rows in the books table. TutorialsPoint - Books with NULL Ratings | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8
23.4K Views
High Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen