MySQL
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
comments
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)
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)
SELECT tbl1.column_name, tbl2.column_name FROM table_1 tbl1 INNER JOIN table_2 tbl2 ON tbl1.column_X = tbl2.column_X;
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.
employeeid
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)
In the following example we are inner joining three tables customer, order_detail and address_detail.
customer
order_detail
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 );