Problem

Table: books

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| title          | varchar |
| author         | varchar |
| published_year | int     |
| rating         | decimal |
+----------------+---------+
book_id is the unique key for this table.
Each row of this table contains information about a book including its unique ID, title, author, publication year, and rating.
rating can be NULL, indicating that the book hasn't been rated yet.

Write a solution to find all books that have not been rated yet (i.e., have a NULL rating).

Return the result table ordered by book_id in ascending order.

The result format is in the following example.

Examples

Example 1

Input:

books table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

+---------+------------------------+------------------+----------------+--------+
| book_id | title                  | author           | published_year | rating |
+---------+------------------------+------------------+----------------+--------+
| 1       | The Great Gatsby       | F. Scott         | 1925           | 4.5    |
| 2       | To Kill a Mockingbird  | Harper Lee       | 1960           | NULL   |
| 3       | Pride and Prejudice    | Jane Austen      | 1813           | 4.8    |
| 4       | The Catcher in the Rye | J.D. Salinger    | 1951           | NULL   |
| 5       | Animal Farm            | George Orwell    | 1945           | 4.2    |
| 6       | Lord of the Flies      | William Golding  | 1954           | NULL   |
+---------+------------------------+------------------+----------------+--------+

    

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           |
    +---------+------------------------+------------------+----------------+

Explanation:

  • The books with book_id 2, 4, and 6 have NULL ratings.
  • These books are included in the result table.
  • The other books (book_id 1, 3, and 5) have ratings and are not included.

The result is ordered by book_id in ascending order

Solution

Method 1 – IS NULL Condition (MySQL)

Intuition

To find books that have not been rated, simply select rows where the rating column is NULL.

Approach

  1. Use a SELECT statement to retrieve all columns from the books table.
  2. Add a WHERE clause to filter rows where rating IS NULL.

Code

1
2
3

SELECT *
FROM books
WHERE rating IS NULL;

Complexity

  • ⏰ Time complexity: O(n) — Scans all rows in the table.
  • 🧺 Space complexity: O(1) — No extra space used.