SQL Inner Join

Posted on by Ian

This article provides an overview of the INNER JOIN in SQL, as well as some basic examples.

The SQL INNER JOIN returns rows when there is at least one row in both tables that match the join condition. It discards unmatched rows from both tables. This is the default join type.

Syntax

There are two ways to specify an inner join: in the FROM clause (using the INNER JOIN syntax), or using the WHERE clause.

To specify an inner join in the FROM clause:

SELECT *
FROM Table1 INNER JOIN Table2 
ON Table1.Column = Table2.Column;

To specify an inner join in the WHERE clause:

SELECT *
FROM Table1, Table2 
WHERE Table1.Column = Table2.Column;

Below are examples of each.

Examples

Here, we’ve got examples for each method of specifying an inner join.

Sample Data

First, here are the tables we’ll use for the examples.

The PetTypes table:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
| 2           | Cat       |
| 3           | Dog       |
| 4           | Rabbit    |
+-------------+-----------+
(4 rows affected)

The Pets table:

+---------+-------------+-----------+-----------+------------+
| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |
|---------+-------------+-----------+-----------+------------|
| 1       | 2           | 3         | Fluffy    | 2020-11-20 |
| 2       | 3           | 3         | Fetch     | 2019-08-16 |
| 3       | 2           | 2         | Scratch   | 2018-10-01 |
| 4       | 3           | 3         | Wag       | 2020-03-15 |
| 5       | 1           | 1         | Tweet     | 2020-11-28 |
| 6       | 3           | 4         | Fluffy    | 2020-09-17 |
| 7       | 3           | 2         | Bark      | NULL       |
| 8       | 2           | 4         | Meow      | NULL       |
+---------+-------------+-----------+-----------+------------+
(8 rows affected)

The Owners table:

+-----------+-------------+------------+----------------+-------------------+
| OwnerId   | FirstName   | LastName   | Phone          | Email             |
|-----------+-------------+------------+----------------+-------------------|
| 1         | Homer       | Connery    | (308) 555-0100 | [email protected] |
| 2         | Bart        | Pitt       | (231) 465-3497 | [email protected]  |
| 3         | Nancy       | Simpson    | (489) 591-0408 | NULL              |
| 4         | Boris       | Trump      | (349) 611-8908 | NULL              |
+-----------+-------------+------------+----------------+-------------------+

Note that:

  • The PetTypeId column of the Pets table is a foreign key of the PetTypeId of the PetTypes table (which is the primary key of that table).
  • The OwnerId column of the Pets table is a foreign key of the OwnerId column of the Owners table.

Example using the INNER JOIN Syntax

Here’s a basic example of specifying an inner join using the INNER JOIN syntax.

SELECT 
    p.PetName,
    pt.PetType
FROM Pets p INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;

Result:

-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Fluffy    | Cat       |
| Fetch     | Dog       |
| Scratch   | Cat       |
| Wag       | Dog       |
| Tweet     | Bird      |
| Fluffy    | Dog       |
| Bark      | Dog       |
| Meow      | Cat       |
+-----------+-----------+
(8 rows affected)

To specify an inner join in the FROM clause, we use INNER JOIN. We also use the ON keyword to define the predicate to be evaluated for each pair of joined rows.

Regardless of the join type, we qualify our column names with the table names. The reason we do this is to avoid any ambiguity regarding the column names between the tables. Both tables could have columns of the same name (like in our example), and in such cases, the DBMS won’t know which column you’re referring to. Prefixing the column names with their table names ensures that you’re referencing the right column, and prevents any errors that could result from any ambiguity about which column you’re referring to.

In this example, both tables have a PetTypeId column. The Pets.PetTypeId column is a foreign key to the PetTypes.PetTypeId column, which is the primary key for that table.

In this example, we can see that all pets are returned, but not all of the pet types are returned. There are no rabbits in the Pets table, and so the Rabbits pet type is not returned.

The reason the Rabbits type is not returned is because the INNER JOIN only returns rows when there is at least one row in both tables that match the join condition. In this case, Rabbits is only in one table (the PetTypes table).

Join Type is Optional

Note that the join type is optional. Therefore, most (if not all) DBMSs allow you to omit the INNER keyword. When you omit this (i.e. only specify JOIN), it is assumed to be an inner join.

Therefore, we could rewrite the above example to this:

SELECT
    Pets.PetName,
    PetTypes.PetType
FROM Pets
JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId;

Formatting

As with any SQL statement, you can use whitespace and indents, etc to format your queries.

For example, the FROM clause can be on one whole line if you prefer:

SELECT
    Pets.PetName,
    PetTypes.PetType
FROM Pets JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId;

When you write larger tables that join multiple tables, indenting can help a lot.

Example using the WHERE Clause

The above join can also be referred to as an equi-join. An equi-join is a join containing only equality comparisons in the join predicate.

Here’s an example of specifying an inner join using the WHERE clause:

SELECT
    p.PetName,
    pt.PetType
FROM 
    Pets p, 
    PetTypes pt
WHERE p.PetTypeId = pt.PetTypeId;

Result:

+-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Fluffy    | Cat       |
| Fetch     | Dog       |
| Scratch   | Cat       |
| Wag       | Dog       |
| Tweet     | Bird      |
| Fluffy    | Dog       |
| Bark      | Dog       |
| Meow      | Cat       |
+-----------+-----------+
(8 rows affected)

This returned the same result as the previous example.

Here, we simply provided a comma separated list of the tables, and then a WHERE condition. If we’d omitted the WHERE condition, we would’ve ended up with a CROSS JOIN.

Many beginners find the above syntax much easier to understand than the INNER JOIN syntax. Feel free to use this syntax if you prefer, however, be aware that most SQL professionals prefer to use the INNER JOIN syntax from the previous example..

Inner Join on 3 Tables

Here’s an example of performing an inner join on 3 tables.

SELECT 
    p.PetName,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Pets p INNER JOIN PetTypes pt 
    ON p.PetTypeId = pt.PetTypeId
INNER JOIN Owners o 
    ON p.OwnerId = o.OwnerId;

Result:

+-----------+-----------+---------------+
| PetName   | PetType   | PetOwner      |
|-----------+-----------+---------------|
| Fluffy    | Cat       | Nancy Simpson |
| Fetch     | Dog       | Nancy Simpson |
| Scratch   | Cat       | Bart Pitt     |
| Wag       | Dog       | Nancy Simpson |
| Tweet     | Bird      | Homer Connery |
| Fluffy    | Dog       | Boris Trump   |
| Bark      | Dog       | Bart Pitt     |
| Meow      | Cat       | Boris Trump   |
+-----------+-----------+---------------+
(8 rows affected)

In this example, we brought the Owners table into the mix because we needed this query to return information about the owner.

To use a third table, all we did was add another INNER JOIN... ON argument along with the relevant table/column details.

In this case, I used T-SQL’s CONCAT() function to concatenate two columns, but this is irrelevant to the join.