Top Level Categories — IS NULL in SQL

The problem

Brightlane's front-end team is rendering the top-level navigation menu and needs only the root categories — those with no parent category above them.

Write a query to return the ID and name of every root category.

Assumptions:

The categories table contains every product category in Brightlane's catalog.
The parent_id column links each category to its parent category; root categories have parent_id set to NULL.

Output:

One row per root category, with columns id and name.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name
FROM
  categories
WHERE
  parent_id IS NULL

The shape

A root category is one with no parent above it, and "no parent" is encoded as parent_id IS NULL. The four top-level menu items — Electronics, Clothing, Home & Garden, Books — are the rows where that link is absent.

Clause by clause

SELECT id, name returns the two columns the front-end needs to render a menu item: the identifier (for the link target) and the display name.
FROM categories reads the category records. The table is self-referential — every row points up to its parent via parent_id, except the root rows, which point nowhere.
WHERE parent_id IS NULL keeps only the rows at the top of the tree. IS NULL returns true for the four roots and false for every descendant.

Why this and not a sentinel row

Every category has a parent_id column, but the root categories have no parent. The schema needs a way to represent that absence, and NULL is the natural fit: it's the marker for "no value here."

The alternative is a sentinel — an id = 0 row that every root points to, or a parent_id of -1. Both work, both add a row or a magic number the application has to remember to filter out, and both lose the type-system honesty that NULL gives. NULL says exactly what it means: there is no parent.

The trap

WHERE parent_id = NULL returns zero rows and the navigation menu comes back empty. The front-end shows no top-level categories, and the bug looks like a data problem until someone reads the query. = against NULL evaluates to unknown, never true. IS NULL is the only operator that asks the right question.

You practiced using IS NULL on a self-referential table to find roots. The same shape applies to any tree-shaped data — categories, employees, organizational units.

Return the ID and name of every root category

The shape

Clause by clause

Why this and not a sentinel row

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.