在本教程中，我们将学习MySQL中的内部联接。

我们使用INNER JOIN来获取要连接的两个表中具有匹配值的行。

INNER JOIN的图示如下所示。

在本教程中，我们将使用"员工"和"评论"表。

员工表

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

评论表

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

INNER JOIN语法

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员工和评论表

在下面的示例中，我们将列出所有发表评论的员工。

因此，为此，我们将内联" employeeid"列上的两个表。
这将获取两个表中具有匹配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;

+------------+-----------+----------+-----------+---------------------+
| employeeid | firstname | lastname | commentid | commentbody         |
+------------+-----------+----------+-----------+---------------------+
| e01        |      |   |         3 | Happy               |
| e01        |      |   |         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多个表格

在下面的示例中，我们内部连接了三个表" 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
);