# Join Example - Join Key `emp_no`

```8
tidb> SHOW CREATE TABLE employees\G;
*************************** 1. row ***************************
 Table: employees
Create Table: CREATE TABLE `employees` (
 ~!@`emp_no` int(11) NOT NULL@!~,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` enum('M','F') NOT NULL,
 `hire_date` date NOT NULL,
 ~!@PRIMARY KEY (`emp_no`)@!~ /*T![clustered_index] CLUSTERED */,
 KEY `idx_first_name` (`first_name`),
 KEY `idx_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
```

```8
tidb>SHOW CREATE TABLE salaries\G;
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  ~!@`emp_no` int(11) NOT NULL@!~,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
```

```
tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no;
+-----------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
| id                          | estRows    | task      | access object   | operator info                                                     |
+-----------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
| HashJoin_9                  | 2825288.58 | root      |                 | inner join, equal:[eq(db1.employees.emp_no, db1.salaries.emp_no)] |
| ├─TableReader_11(Build)     | 300024.00  | root      |                 | data:TableFullScan_10                                             |
| │ └─TableFullScan_10        | 300024.00  | cop[tikv] | table:employees | keep order:false                                                  |
| └─TableReader_13(Probe)     | 2844047.00 | root      |                 | data:TableFullScan_12                                             |
| └─TableFullScan_12          | 2844047.00 | cop[tikv] | table:salaries  | keep order:false                                                  |
+-----------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
5 rows in set (0.01 sec)
```

------------------------------------------------------------------------------
In this section, we will introduce several typical algorithm of table join, as well as how to evaluate and optimize them from a performance perspective. First, let's familiarize ourselves with the case table we will be explaining. Here is the `employees` table:

```
tidb> SHOW CREATE TABLE employees\G;
*************************** 1. row ***************************
 Table: employees
Create Table: CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` enum('M','F') NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`) /*T![clustered_index] CLUSTERED */,
 KEY `idx_first_name` (`first_name`),
 KEY `idx_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
```
Here is the `salaries` table:

```
tidb>SHOW CREATE TABLE salaries\G;
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
```


As we can see, both the `employees` table and the `salaries` table currently do not have any indexes. Let's take a look at a simple table join query:

```
tidb> EXPLAIN SELECT * FROM db1.employees, db1.salaries WHERE db1.employees.emp_no = db1.salaries.emp_no;
+-----------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
| id                          | estRows    | task      | access object   | operator info                                                     |
+-----------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
| HashJoin_9                  | 2825288.58 | root      |                 | inner join, equal:[eq(db1.employees.emp_no, db1.salaries.emp_no)] |
| ├─TableReader_11(Build)     | 300024.00  | root      |                 | data:TableFullScan_10                                             |
| │ └─TableFullScan_10        | 300024.00  | cop[tikv] | table:employees | keep order:false                                                  |
| └─TableReader_13(Probe)     | 2844047.00 | root      |                 | data:TableFullScan_12                                             |
| └─TableFullScan_12          | 2844047.00 | cop[tikv] | table:salaries  | keep order:false                                                  |
+-----------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
5 rows in set (0.01 sec)
```

Looking at the execution plan above, we can see the `HashJoin_9` on the first row, which represents the `Hash Join` operation in the table join. This is also the first type of table join operator we will introduce.