Inner Joins
In the previous lesson, we introduced the concept of joins in SQL and discussed the different types of joins. In this lesson, we'll focus on inner joins and learn about their specific properties and how to use them in SQL.
Basic Syntax
The basic syntax for an inner join in SQL is as follows:
SQLSELECT columns FROM table_1 INNER JOIN table_2 ON join_condition;
The INNER JOIN keyword is used to specify that an inner join is being performed, and the ON keyword is used to specify the join condition. The join condition is an expression that evaluates to TRUE or FALSE for each row in the tables being joined, and determines which rows from each table are included in the result set.
What are inner joins?
An inner join returns only the rows where there is a match in both tables being joined. Inner joins are the most common type of join and are used when you want to combine data from two related tables.
When to use an inner join
One way to think about inner joins is that they're like the center of a Venn diagram in which each table has overlap, where the overlap is defined by the columns you define in the ON a = b condition. This is similar to the intersection of the two sets of data. (Technically, they're not exactly the same, since columns aren't always sets of unique values).
Practically speaking, inner joins are best used in situations where you only care about data that matches across both tables. Here are a few examples.
Example 1: Orders from last month
Imagine we have a table of orders and customers and we want to find all the orders from a given month along with the email of the customer who placed the order. Here's some example data:
To find the orders and customer data from March, here's an example query we could write:
SQLSELECT order.*, customers.email
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
WHERE orders.order_date BETWEEN '2022-03-01' and '2022-04-01';
This will return the combined data set:
Try it yourself
Given tables for customers and orders, with foreign key customer_id referencing customers.id, write a query to return all orders with their associated customer data.
Are JOIN and INNER JOIN the same?
Yes! In SQL, JOIN is a shorthand for INNER JOIN. In other words, inner join is the default type of join, and you don't have to include the keyword INNER unless you want to for clarity or readability—always a good thing to consider!
Joining across multiple tables
Joining data across two tables using an association table is a way to handle many-to-many relationships in a relational database. An association table, also known as a join table or junction table, is a table that has a many-to-many relationship with two other tables. It contains only the foreign keys that link the two other tables and serves as a bridge between them.
Here's an example to help illustrate how it works. Let's say you have a users table, a teams table, and a team_memberships table. The users table contains information about each user, the teams table contains information about each team, and the team_memberships table contains information about the relationship between users and teams. The team_memberships table would have two foreign keys, one for the users table and one for the teams table.
To join the data from the users and teams tables, you would use an inner join on the team_memberships table. The join condition would be based on the foreign keys in the team_memberships table that refer to the primary keys in the users and teams tables.
SQLSELECT users.user_id, users.name, teams.team_id, teams.name
FROM users
INNER JOIN team_memberships
ON users.user_id = team_memberships.user_id
INNER JOIN teams
ON teams.team_id = team_memberships.team_id;
In this query, two inner joins are performed. The first inner join combines the data from the users table and the team_memberships table, and the second inner join combines the data from the teams table and the team_memberships table. The result set will only include rows where there are matching user IDs and team IDs across all three tables.