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 darcy@pemberley.com
2 luna luna@mice.com
3 nala me@cartoon.com
4 tigger me@cartoon.com

5 WHERE id in (...)

id name email
1 mr darcy darcy@pemberley.com
2 luna luna@mice.com

6 SELECT name

name
mr darcy
luna