← All tasksSQL · Middle

Products that were never ordered

List products that don't appear in any order_items row: columns id and name, ordered by id. Note that product_id can be NULL (the product was deleted).

Tables

CREATE TABLE products (
  id   int PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE order_items (
  order_id   int NOT NULL,
  product_id int,  -- NULL: the product was deleted
  quantity   int NOT NULL
);

SELECT id, name
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items)
ORDER BY id;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO products VALUES (1, 'Mug'), (2, 'Tea'), (3, 'Pen');
INSERT INTO order_items VALUES (100, 1, 2), (101, 1, 1);

-- a NULL product_id: NOT IN returns nothing here
INSERT INTO products VALUES (1, 'Mug'), (2, 'Tea'), (3, 'Pen');
INSERT INTO order_items VALUES (100, 2, 1), (101, NULL, 1);

-- every product was ordered
INSERT INTO products VALUES (1, 'Mug');
INSERT INTO order_items VALUES (100, 1, 1);