DbSchema PostgreSQL starts when you begin the lesson

Lesson 10

Keeping rows without a match: LEFT JOIN

For readers who finished lesson 9 and can write a SELECT with a JOIN.

After this lesson you can list every customer, including the ones who never ordered, and find the rows that have no match in another table.
  • Why orders have their own table
  • What a join does
  • What JOIN leaves out
  • LEFT JOIN
  • Finding what is missing
  • A table joined to itself
  • 4 exercises

Start the lesson

Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.

For a reference page on the same statements, read SQL Joins Explained.

Why a join is needed

Why the shop keeps orders in their own table

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
The two tables, drawn by DbSchema from the practice database.

A customer places many orders. If every order were stored inside customers, the customer's name and email would be copied onto each one, and a changed email would have to be fixed in every copy.

So each order is a row in orders, and its customer_id holds the number of the customer who placed it. The line in the diagram is that link, the foreign key you met in lesson 8.

Why a join is needed

A join puts the two tables back together

Customer 480, Alex, is linked to orders 467 and 947, which both have customer_id 480. The join result has two rows: Alex 467 and Alex 947.
Two of Alex's four orders are drawn.

To show a customer's name next to an order, PostgreSQL pairs each order with the customer whose customer_id matches. That pairing is the join from lesson 9:

select c.first_name, o.order_id
from customers c
join orders o on o.customer_id = c.customer_id;

Try it

Run the join for four customers

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
select c.customer_id, c.first_name, o.order_id
from customers c
join orders o on o.customer_id = c.customer_id
where c.customer_id between 479 and 482
order by c.customer_id, o.order_id;

You asked for customers 479 to 482, but only 479 and 480 came back.

The problem

A plain JOIN drops the customers who never ordered

Customer 481, Fatima, has no matching order. The join result has Alex's two orders, and Fatima's row is crossed out.

Fatima, customer 481, has no row in orders. The join finds nobody to pair her with, so she isn't in the result. Customer 482 has no orders either.

For a list of orders that's right. For a list of customers it's wrong: Fatima is a customer, she just hasn't bought anything yet.

The fix

LEFT JOIN keeps every row of the first table

With LEFT JOIN, the result keeps Fatima, and her order_id is NULL.

Write LEFT JOIN instead of JOIN. Every row of the table written first, here customers, stays in the result. Where a customer has no order, the columns from orders are NULL.

select c.first_name, o.order_id
from customers c
left join orders o on o.customer_id = c.customer_id;

Try it

Run the same four customers with LEFT JOIN

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
select c.customer_id, c.first_name, o.order_id
from customers c
left join orders o on o.customer_id = c.customer_id
where c.customer_id between 479 and 482
order by c.customer_id, o.order_id;

Customers 481 and 482 are back, with NULL where an order would be.

Using it

Find the customers who never ordered

Only a customer without orders has NULL in o.order_id, so filtering on it keeps exactly those customers.

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
select c.customer_id, c.first_name, c.last_name
from customers c
left join orders o on o.customer_id = c.customer_id
where o.order_id is null;

20 customers, numbers 481 to 500, have never ordered.

Watch out

Count orders with count(o.order_id), not count(*)

LEFT JOIN made one row for Fatima, with NULL in every order column. count(*) counts that row, so it says she has 1 order. count(o.order_id) skips NULL, as every aggregate does (lesson 6), and says 0.

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
select c.customer_id, count(*), count(o.order_id)
from customers c
left join orders o on o.customer_id = c.customer_id
where c.customer_id in (480, 481)
group by c.customer_id;
customer_idcountcount
48044
48110

One more use

A table can be joined to itself

🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary
The employees table in DbSchema: manager_id points back into the same table.

An employee's manager_id holds the employee_id of their manager. To print both names on one row, join employees to itself under two names, e for the employee and m for the manager.

The six department heads have no manager, so use LEFT JOIN to keep them.

select e.first_name, e.last_name, m.first_name as manager
from employees e
left join employees m on m.employee_id = e.manager_id;

All 60 employees are there. The six department heads come first, with NULL as manager.

In DbSchema

The lines in a diagram are the joins you can write

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) employee_id 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) order_id 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) product_id 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) department_id 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status Table public.order_items ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The lines of an order: one row for each product in it.order_items 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ order_id * int4 ↗ orders( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The order this line belongs to: an order_id of orders.order_id 🔗 References orders ( order_id ) 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ line_no * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number of the line within its order. With order_id, it identifies the line.line_no ⧉ product_id * int4 ↗ products( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The product on this line: a product_id of products.product_id 🔗 References products ( product_id ) ⧉ quantity * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces of the product were ordered.quantity ⧉ unit_price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
The practice database, drawn by DbSchema.

A DbSchema diagram draws each foreign key as a line between two tables, so you can read off which columns to join on. Connect DbSchema to your own PostgreSQL and it draws this diagram for your tables.

Download DbSchema

The Community Edition is free. Every download starts with a 15-day trial of all features.

Exercise 1 of 4

List the id, first name and last name of every customer who has no orders.

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
Expected result

It shows once PostgreSQL is ready.


    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select c.customer_id, c.first_name, c.last_name
from customers c
left join orders o on o.customer_id = c.customer_id
where o.order_id is null

    






    
Exercise 2 of 4

    




List every customer's id and their number of orders. Customers without orders must appear with 0.











  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  







🔗  Foreign Key orders_customer_id_fkey
orders ➔ customers ( customer_id )

🔗  Foreign Key orders_customer_id_fkey
orders ➔ customers ( customer_id )

customer_id




Table public.customers
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The people who buy from the shop.customers
  🔑  Pk customers_pkey ( customer_id ) ⧉  customer_id
     * int4
 ↙ orders( customer_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the customer.customer_id
🔗  Referred by orders ( customer_id ) 
  ⧉  first_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The first name of the customer.first_name
  ⧉  last_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The last name of the customer.last_name
  🔍  Unq customers_email_key ( email ) ⧉  email
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The email address of the customer. No two customers share one.email
  ⧉  phone
     text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The phone number of the customer. NULL when the customer gave none.phone
  ⧉  country
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The country the customer lives in.country
  ⧉  signup_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the customer created an account.signup_date





Table public.orders
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      One purchase by one customer. Its products are the rows of order_items.orders
  🔑  Pk orders_pkey ( order_id ) ⧉  order_id
     * int4
 ↙ order_items( order_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the order.order_id
🔗  Referred by order_items ( order_id ) 
  ⧉  customer_id
     * int4
 ↗ customers( customer_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The customer who placed the order: a customer_id of customers.customer_id
🔗  References customers ( customer_id ) 
  ⧉  employee_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id
🔗  References employees ( employee_id ) 
  ⧉  order_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the order was placed.order_date
  ⧉  status
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      Where the order stands: pending, shipped, delivered or cancelled.status


Hover a table or a column to read its comment.





    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select c.customer_id, count(o.order_id)
from customers c
left join orders o on o.customer_id = c.customer_id
group by c.customer_id

    






    
Exercise 3 of 4

    




List every employee's first and last name, with their manager's first and last name. The department heads must appear too.












  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  







🔗  Foreign Key employees_manager_id_fkey
employees ➔ employees ( manager_id -> employee_id )

🔗  Foreign Key employees_manager_id_fkey
employees ➔ employees ( manager_id -> employee_id )

manager_id




Table public.employees
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The people who work for the shop.employees
  🔑  Pk employees_pkey ( employee_id ) ⧉  employee_id
     * int4
 ↙ employees( manager_id )
 ↙ orders( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the employee.employee_id
🔗  Referred by employees ( manager_id -> employee_id ) 
Referred by orders ( employee_id ) 
  ⧉  first_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The first name of the employee.first_name
  ⧉  last_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The last name of the employee.last_name
  🔍  Unq employees_email_key ( email ) ⧉  email
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The work email address. No two employees share one.email
  ⧉  department_id
     * int4
 ↗ departments( department_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The department the employee works in: a department_id of departments.department_id
🔗  References departments ( department_id ) 
  ⧉  manager_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id
🔗  References employees ( manager_id -> employee_id ) 
  ⧉  hire_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the employee started.hire_date
  ⧉  salary
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The yearly salary, with two decimals.salary


Hover a table or a column to read its comment.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select e.first_name, e.last_name, m.first_name, m.last_name
from employees e
left join employees m on m.employee_id = e.manager_id

    






    
Exercise 4 of 4

    




List the name of every product that nobody has ordered. An order's products are in order_items.











  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  







🔗  Foreign Key order_items_product_id_fkey
order_items ➔ products ( product_id )

🔗  Foreign Key order_items_product_id_fkey
order_items ➔ products ( product_id )

product_id




Table public.order_items
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The lines of an order: one row for each product in it.order_items
  🔑  Pk order_items_pkey ( order_id, line_no ) ⧉  order_id
     * int4
 ↗ orders( order_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The order this line belongs to: an order_id of orders.order_id
🔗  References orders ( order_id ) 
  🔑  Pk order_items_pkey ( order_id, line_no ) ⧉  line_no
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number of the line within its order. With order_id, it identifies the line.line_no
  ⧉  product_id
     * int4
 ↗ products( product_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The product on this line: a product_id of products.product_id
🔗  References products ( product_id ) 
  ⧉  quantity
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      How many pieces of the product were ordered.quantity
  ⧉  unit_price
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price





Table public.products
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      What the shop sells.products
  🔑  Pk products_pkey ( product_id ) ⧉  product_id
     * int4
 ↙ order_items( product_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the product.product_id
🔗  Referred by order_items ( product_id ) 
  🔍  Unq products_name_key ( name ) ⧉  name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The name of the product. No two products share a name.name
  ⧉  category
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The group the product belongs to, such as Accessories or Displays.category
  ⧉  price
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The current price, with two decimals. Always more than 0.price
  ⧉  stock
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      How many pieces are in the warehouse. Never below 0.stock


Hover a table or a column to read its comment.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select p.name
from products p
left join order_items oi on oi.product_id = p.product_id
where oi.order_id is null