Joining tables is an essential skill for writing SQL queries, but so far, we’ve tried only one kind of join. To be fair, that type of join is the most common, but as you’ll see in this chapter, in plenty of scenarios, that kind of join won’t help you produce the results you need.
You might be asked to produce a list of all orders for a given year, for example, and show whether they used a particular discount code. Or you might be asked to find the names of all customers who didn’t place an order in a year. Or you might be asked to find a list of all customers in a particular city or state and show which ones placed orders and which did not. You can’t accomplish these queries using the join type from chapter 8, so you’re going to learn how to use different joins in SQL to fulfill all the preceding requests.
9.1 Inner joins
First, let’s talk a little bit more about the JOIN keyword we used in chapter 8. This join is a shorthand version of the keyword INNER JOIN, which is a particular type of join. Because it joins only values in both tables that meet the conditions of the join, the results set excludes any rows that don’t meet the conditions.