Basics of INNER JOIN

Last time, we talked about the different types of JOINs in SQL. Today, we're gonna dive deeper into INNER JOIN.

INNER JOIN is a way to combine data in relational databases that lets you grab rows from two tables and return only those rows that "match" according to a condition you set. In other words, INNER JOIN only returns the overlapping parts of two tables, ignoring everything else.

Let’s imagine you have two boxes. One has cards with students, and the other has cards with courses that students are registered for. You want to know which students are signed up for which courses. If there’s no match (like, a student isn’t signed up anywhere), we don’t care about that for now. This is a perfect scenario for INNER JOIN.

Syntax of INNER JOIN

The syntax is pretty straightforward — you specify the two tables you want to join and set the join condition using the ON keyword.

SELECT columns
FROM table1 INNER JOIN table2
ON table1.field = table2.field;

• table1 and table2 — these are the tables you want to join.
• field — these are the columns you’re matching on.
• The condition after ON tells SQL how to match up rows from both tables.

Examples of using INNER JOIN

For the next examples, we’ll work with two tables:

Table students — student data

Table enrollments — course enrollment data

Notice that student Dia (with student_id = 4) isn’t registered for any course.

Example 1: Getting students and their courses

We want to find out which students are enrolled in which courses. This is a classic use case for INNER JOIN. We only care about where there’s a match between the students and enrollments tables based on student_id.

SELECT students.name, enrollments.course_id
FROM students INNER JOIN enrollments
ON students.student_id = enrollments.student_id;

Result:

What do we see? INNER JOIN only returned students who are registered for courses. Dia, who isn’t enrolled anywhere, got left out.

Example 2: Getting orders and customers

Now let’s look at another example. Say we have tables orders and customers. We want to get a list of all orders with customer names.

Table orders

Table customers

Task: we need to join orders and customers by customer_id to return only those orders that have a matching customer.

SELECT orders.order_id, customers.name, orders.amount
FROM orders INNER JOIN customers
ON orders.customer_id = customers.customer_id;

Result:

Notice that the order with order_id = 3 didn’t make it into the result, because there’s no customer with customer_id = 103 in the customers table.

How INNER JOIN helps you join tables (and what can go wrong)

INNER JOIN is the main tool you’ll use in pretty much any project that involves a relational database. It’s like a wrench in your toolbox: you could try to get by without it, but it’s gonna be way harder to get stuff done. For example:

• When building reports where you need to combine data from several tables.
• For analytics, when you need to join facts with dimensions (like sales and customers).
• For integrating data from third-party systems.

The most common rookie mistake is forgetting the ON or messing up the join condition. If you don’t set the right condition, instead of what you expect, you’ll get the cartesian product of the two tables — that could be thousands or millions of rows that make no sense.

Example of a mistake:

In this example, there’s no join condition, so the query will create every possible combination of rows from both tables (and that’s probably not what you want):

SELECT students.name, enrollments.course_id
FROM students, enrollments;  -- MISTAKE: no join condition!

The result will look like chaos: every row from students gets combined with every row from enrollments.

2

Task

SQL SELF, level 11, lesson 1

Locked

Simple join of students and courses

Simple join of students and courses

Open

2

Task

SQL SELF, level 11, lesson 1

Locked

List of customers and their orders

List of customers and their orders

Open