A subquery might be easier to read than a JOIN

This query finds the name of every client that has ever bought tuna. This is a nice way to filter one table (clients) based on data in another table (sales). You can do the same thing with an INNER JOIN but I find this version easier to read.

 SELECT name
 FROM clients 
 WHERE id in (
     SELECT DISTINCT client_id
     FROM sales
     WHERE item = 'tuna'
 )

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

1 FROM sales

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

2 WHERE item = 'tuna'

client_id item
1 tuna
2 tuna

3 SELECT DISTINCT client_id

Here the DISTINCT isn't necessary but if there were tons of duplicates the DISTINCT might make the query run faster.

client_id
1
2

4 FROM clients

id name email
1 mr darcy [email protected]
2 luna [email protected]
3 nala [email protected]
4 tigger [email protected]

5 WHERE id in (...)

id name email
1 mr darcy [email protected]
2 luna [email protected]

6 SELECT name

name
mr darcy
luna