A simple LEFT JOIN

Here's a join query that counts how many items each client has bought: 

 SELECT clients.name AS client_name, count(item)
 FROM clients
 LEFT JOIN sales ON sales.client_id = clients.id
 GROUP BY clients.name
 ORDER BY count(item) DESC

Let's go through that query one step at a time:

1 FROM clients

id name email
1 mr darcy darcy@pemberley.com
2 luna luna@mice.com
3 nala me@cartoon.com
4 tigger me@cartoon.com

2 LEFT JOIN sales

Here's the sales table:

client_id item
1 catnip
1 blanket
1 tuna
2 tuna
5 laser pointer

3 LEFT JOIN sales ON sales.client_id = clients.id

Clients data on the left, sales on the right

id name email client_id item
1 mr darcy darcy@pemberley.com 1 blanket
1 mr darcy darcy@pemberley.com 1 catnip
1 mr darcy darcy@pemberley.com 1 tuna
2 luna luna@mice.com 2 tuna
3 nala me@cartoon.com
4 tigger me@cartoon.com

4 GROUP BY clients.name

id name email client_id item
2 luna luna@mice.com 2 tuna
id name email client_id item
1 mr darcy darcy@pemberley.com 1 blanket
1 mr darcy darcy@pemberley.com 1 catnip
1 mr darcy darcy@pemberley.com 1 tuna
id name email client_id item
3 nala me@cartoon.com
id name email client_id item
4 tigger me@cartoon.com

5 SELECT clients.name AS client_name, count(item)

client_name count(item)
luna 1
mr darcy 3
nala 0
tigger 0

6 ORDER BY count(item) DESC

client_name count(item)
mr darcy 3
luna 1
tigger 0
nala 0