LEFT JOIN in SQL

Level 4138 keystrokes

LEFT JOIN keeps every user, even without orders, so COUNT(orders.id) can report 0 for them.

SQL code

-- Return each user's name and their number of orders
1SELECT
2 users.name,
-- COUNT(orders.id) skips NULLs, so a user with no orders gets 0, not 1
3 COUNT(orders.id) AS order_count
4FROM
5 users
-- LEFT JOIN keeps all users; without a matching order, the orders columns are NULL
6LEFT JOIN
7 orders ON users.id = orders.user_id
-- One group per user; grouping by id keeps two users with the same name apart
8GROUP BY
9 users.id,
10 users.name;

Output

+-------+-------------+
| name  | order_count |
+-------+-------------+
| Alice |           2 |
| Bob   |           1 |
| Carol |           0 |
+-------+-------------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • .× 6
  • _× 2
  • ,× 2
  • ;× 1
  • (× 1
  • )× 1
  • =× 1

More SQL examples at level 4

← SQL typing practice