Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 125 ยท Joins

INNER JOIN

Master SQL INNER JOIN - write ON conditions, use USING for shared column names, join on several columns or ranges, and see why NATURAL JOIN is risky.

INNER JOIN is the join you'll write most often. It pairs up rows from two tables wherever the join condition is true and returns only those pairs. Rows that find no partner are left out of the result.

Why Joins? showed the idea. This lesson covers the details that make inner joins correct and readable: the full syntax, the difference between ON and USING, why NATURAL JOIN is a trap, how to join on several columns at once, and joins whose condition isn't a simple equality. The examples use the shop and school sample databases.

The syntax

SQL
SELECT columns
FROM left_table AS l
INNER JOIN right_table AS r
  ON r.some_column = l.other_column;
flowchart LR
    subgraph A["authors"]
        A1["1 Ada"]
        A2["2 Grace"]
        A3["3 Linus"]
    end
    subgraph B["books"]
        B1["book 10 (author_id 1)"]
        B2["book 11 (author_id 1)"]
        B3["book 12 (author_id 2)"]
    end
    A1 --- B1
    A1 --- B2
    A2 --- B3

An INNER JOIN keeps only the pairs that match: Ada appears twice (two books), Grace once, and Linus has no book, so he does not appear at all.

The keyword INNER is optional: JOIN alone means the same thing, and most people leave INNER out. Here are the products of the Audio category with their category name, which lives in a different table:

SQL Products with category names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The ON condition ties each product to its category. WHERE then filters the joined rows, and it can use columns from either table. Both tables have a name column, so the query gives each one an alias in the SELECT list (product, category); otherwise the result would show two columns called name.

Inner joins drop unmatched rows

The defining property of an inner join is that a row appears only if it found a match. In the school database, every course has a teacher except one: the Machine Learning Seminar (CS300) has teacher_id = NULL because nobody has been assigned yet.

SQL A course without a teacher disappears ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

There are 10 courses, but the join returns 9 rows. For CS300, the condition t.id = NULL is never true (NULL never equals anything), so the course has no partner and is dropped. That's exactly right for "courses together with their teachers", and exactly wrong for "a list of all courses". For the second question you need a LEFT JOIN, which keeps unmatched rows.

The same thing happens in the other direction. A teacher who teaches no course would vanish too. Here every teacher has at least one course, so all 7 appear somewhere in the result.

Inner join as a filter. Because unmatched rows disappear, an inner join also works as a filter: customers JOIN orders keeps only customers who have ordered. If you only need whether a match exists, not its columns, EXISTS says that more directly and never duplicates rows.

ON vs WHERE in an inner join

For an inner join, a condition gives the same result whether you put it in ON or in WHERE. These two queries are equivalent:

SQL
SELECT p.name, c.name AS category
FROM products AS p
JOIN categories AS c ON c.id = p.category_id AND c.name = 'Audio';

SELECT p.name, c.name AS category
FROM products AS p
JOIN categories AS c ON c.id = p.category_id
WHERE c.name = 'Audio';

Convention keeps them apart anyway: put the conditions that relate the tables (keys) in ON, and the conditions that filter rows in WHERE. Readers can then see the join structure at a glance. The distinction becomes essential with outer joins, where moving a condition between ON and WHERE changes the result. The LEFT JOIN lesson shows how.

USING: when the key columns share a name

If the matching columns have the same name in both tables, you can write USING (column) instead of an ON equality. The shop and school databases name their keys id and customer_id, so USING doesn't fit them. Here's a small schema where it does:

SQL JOIN ... USING

Press Run (or Ctrl + Enter) to see the result here.

USING (country_code) means ON offices.country_code = countries.country_code, with one bonus: the shared column appears only once in SELECT *, and you can refer to it without a table prefix. That's why the result has four columns rather than five. Kenya has no office, so, as with every inner join, it doesn't appear.

You can list several columns: USING (student_id, course_id). USING is standard SQL and works in SQLite, PostgreSQL, MySQL and Oracle, but not in SQL Server, which only supports ON.

Many teams name foreign keys after the referenced column precisely so they can use USING, for example by calling the primary key customer_id instead of id. Either convention is fine; just be consistent.

NATURAL JOIN, and why to avoid it

NATURAL JOIN goes one step further: it joins on every column name the two tables have in common, without you listing any. It sounds convenient. Here's what happens with products and categories:

SQL NATURAL JOIN surprise ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both queries return no rows. The two tables share the column names id and name, so NATURAL JOIN matched a product's id to a category's id (wrong key) and required the product's name to equal the category's name. No product is called "Kitchen", so nothing matches.

The deeper problem is that the join condition depends on column names that someone may change later. Add a created_at column to both tables next year, and every NATURAL JOIN between them silently gains a new condition. Write ON or USING so the condition is visible in the query and doesn't change behind your back.

Joining on several columns

Some tables are keyed by several columns together. In the school database, an enrollment is identified by (student_id, course_id, term_id): a student can take the same course again in a later term. Suppose another table records absences for each enrollment. Joining them must match all three columns:

SQL A three-column join ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Student 1 took course 1 (Algebra Foundations) in terms 2 and 3, and course 3 (Mechanics) in terms 1 and 3, so you get four rows, each with the absences of exactly that enrollment.

Now leave out one column of the key. This version forgets term_id:

SQL Forgetting part of the key ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

You get eight rows instead of four: every enrollment of a course is paired with the absences of both terms of that course. The query runs without complaint and a SUM(absences) over it would be doubled. The rule is simple: join on the whole key. If you see more rows than expected, compare your ON clause with the target table's primary key.

Conditions other than equality

Most joins compare keys with =, but ON accepts any condition. A range condition is common for lookup tables, such as price bands or tax brackets:

SQL Joining on a range ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each product falls into exactly one band because the ranges don't overlap (>= at the bottom, < at the top). Keeping bands in a table instead of a CASE expression means they can change without editing queries. If the ranges overlapped, a product would match two bands and be counted twice; with gaps, some products would match none and disappear.

Joins feed everything else

A join produces an ordinary set of rows, so everything you've learned applies on top of it: WHERE, GROUP BY, HAVING, ORDER BY. Here's the number of courses and enrolled seats per teacher:

SQL Join, then group ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

COUNT(*) counts enrollment rows, while COUNT(DISTINCT c.id) counts courses, because each course repeats once per enrollment. Rania Tanaka and Viktor Varga teach two courses each; everyone else teaches one.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between JOIN and INNER JOIN?

    JOIN on its own is an inner join. Writing INNER just makes it explicit.
  2. 2. The courses table has 10 rows, and one course has teacher_id NULL. How many rows does courses JOIN teachers ON teachers.id = courses.teacher_id return if every other course has a valid teacher?

    The course with a NULL teacher_id finds no match, because NULL never equals anything, so the inner join drops it.
  3. 3. Why did products NATURAL JOIN categories return no rows?

    NATURAL JOIN matches all same-named columns. Here that meant the wrong key (id = id) plus name = name, which never holds.
  4. 4. What does JOIN countries USING (country_code) do with the country_code column in SELECT *?

    USING merges the shared column into a single output column that you can reference without a table prefix.
  5. 5. A table is keyed by (student_id, course_id, term_id), but your join only matches student_id and course_id. What is the likely symptom?

    Leaving out part of the key lets each row match several partners, which multiplies rows and inflates sums and counts.

Summary

  • INNER JOIN (or just JOIN) returns only the row pairs where the ON condition is true; unmatched rows, including those with NULL keys, are dropped.
  • Put key conditions in ON and filters in WHERE; for inner joins the result is the same, but the intent is clearer.
  • USING (col) joins on same-named columns and shows the column once (not available in SQL Server).
  • Avoid NATURAL JOIN: it silently joins on every shared column name, which breaks when columns are added or are named id and name.
  • Join on the whole key of multi-column keys; a partial key multiplies rows.
  • ON can hold any condition, such as a range, and a join's output can be filtered, grouped and sorted like any table.

Next: LEFT JOIN, where you'll keep the rows that an inner join throws away.