>_ IzzyLab

Hands-on task

This task is step 12 of the course “SQL with SQLite”

SQLite: LEFT JOIN and customers without orders

medium~7 min

The database /root/shop.db has tables customers(id, name) and orders(id, customer_id, amount). You need to find customers who have no orders at all and write their names (one per line) to /root/out.txt.

Data hint: some customers have no orders whatsoever — those are exactly the ones you need to get.

What you'll practise:
  • sqlite3
  • left join
  • is null
  • anti-join
  • sql

Topic breakdown

A plain INNER JOIN returns only rows that have a match in both tables, so "orphans" from the left table are lost. LEFT JOIN takes all rows from the left table and fills in the right table's data, and where there is no match, the right table's columns become NULL. This is the basis of the "anti-join" technique: do a left join and in WHERE keep only rows where the right table's key field IS NULL. A comparison with NULL is written as IS NULL, not = NULL.

What's checked

  1. the customer without orders is found
  2. customers with orders are not in the result

What it looks like