9 Using different kinds of joins
Joining tables is an essential skill to have when writing SQL queries, but so far, we have 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, there will be plenty of scenarios where that kind of join won’t help you produce the results you need.
For example, you may be asked to produce a list of all orders for a given year and show if they used a particular discount code, or to find the names of all customers who didn’t place an order in a year, or to find a list of all customers in a particular city or state and show which placed orders and which did not. None of these queries can be accomplished using the type of join from the last chapter.
In this chapter, we’re going to learn how to use different joins in SQL to fulfill all the preceding requests. Let’s dive into these types of joins and see how to use them.
9.1 Inner joins
First, let’s talk a little bit more about the JOIN keyword we used in the previous chapter. This is actually a shorthand version of the keywords INNER JOIN, which is a particular type of join. Because it only joins values in both tables that meet the conditions of the join, the results set excludes any rows that do not meet the conditions.