Customers who never ordered

Find the customers who have never placed an order.

What to build

A query that finds customers with no orders.

Requirements

• Use LEFT JOIN from customers to orders, or NOT EXISTS.
• Keep only the customers with no matching order.
• Sort by name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);

Output

Columns name and city, in alphabetical order of name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

A customer may have no orders or many.

Example

When a test starts with:

INSERT INTO customers VALUES
  (1, 'Rahim', 'Dhaka'),
  (2, 'Nadia', 'Sylhet'),
  (3, 'Karim', 'Khulna');
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, 'Pen', 30),
  (102, 1, 'Bag', 900);

your SQL should print:

name   city
-----  ------
Karim  Khulna
Nadia  Sylhet
(2 rows)

Why it works

A LEFT JOIN keeps customers with no order and fills the order columns with NULL, so o.id IS NULL picks exactly those customers.

Examples

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
INSERT INTO customers VALUES
  (1, 'Rahim', 'Dhaka'),
  (2, 'Nadia', 'Sylhet'),
  (3, 'Karim', 'Khulna');
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, 'Pen', 30),
  (102, 1, 'Bag', 900);

name   city
-----  ------
Karim  Khulna
Nadia  Sylhet
(2 rows)

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
INSERT INTO customers VALUES
  (1, 'Tania', 'Dhaka'),
  (2, 'Sumon', NULL);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, 'Glue', 40),
  (102, 2, 'Marker', 60);

name  city
----  ----
(0 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;

Lesson: Joining tables →