MySQL - INNER JOIN

In this tutorial we will learn about inner join in MySQL.

We use the INNER JOIN to fetch rows that have matching values in both the tables that we are joining.

The pictorial representation of INNER JOIN is shown below.

For this tutorial we will be using the employee and comments table.

Employee Table

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Comments Table

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

INNER JOIN syntax

SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
INNER JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;

INNER JOIN employee and comments table

In the following example we will list all the employees who commented.

So, for this we will INNER JOIN the two tables on the column employeeid. This will fetch us all the rows that have matching employeeid in both the tables.

mysql> SELECT e.employeeid, e.firstname, e.lastname, c.commentid, c.commentbody
FROM employee e
INNER JOIN comments c
ON e.employeeid = c.employeeid
ORDER BY employeeid;

Output:

+------------+-----------+----------+-----------+---------------------+
| employeeid | firstname | lastname | commentid | commentbody         |
+------------+-----------+----------+-----------+---------------------+
| e01        | Yusuf     | Shakeel  |         3 | Happy               |
| e01        | Yusuf     | Shakeel  |         5 | Keep coding....     |
| e03        | Jane      | Doe      |         1 | Awesome             |
| e03        | Jane      | Doe      |         2 | Hello World         |
| e04        | Tin       | Tin      |         4 | This is my comment. |
+------------+-----------+----------+-----------+---------------------+
5 rows in set (0.01 sec)

INNER JOIN multiple tables

In the following example we are inner joining three tables customer, order_detail and address_detail.

mysql> SELECT c.customer_name, o.order_id, a.address
FROM (
  (
    customer c
    INNER JOIN address_detail a
    ON c.customerid = a.customerid
  )
  INNER JOIN order_detail o
  ON c.customerid = o.customerid
);