# Binding a Query with SQL Hints for Current Session - Example

```
tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no;
+-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+
| id                      | estRows    | task      | access object   | operator info                                                            |
+-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+
| MergeJoin_8             | 2825288.58 | root      |                 | inner join, left key:db1.employees.emp_no, right key:db1.salaries.emp_no |
| ├─TableReader_32(Build) | 2844047.00 | root      |                 | data:TableFullScan_31                                                    |
| │ └─TableFullScan_31    | 2844047.00 | cop[tikv] | table:salaries  | keep order:true                                                          |
| └─TableReader_30(Probe) | 300024.00  | root      |                 | data:TableFullScan_29                                                    |
|  └─TableFullScan_29     | 300024.00  | cop[tikv] | table:employees | keep order:true                                                          |
+-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+
5 rows in set (0.00 sec)

tidb> ~!@CREATE BINDING FOR@!~
   -> SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no
   -> USING
   -> SELECT /*+ HASH_JOIN(employees, salaries) */ * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no;
Query OK, 0 rows affected (0.00 sec)

tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no;
+-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
| id                      | estRows    | task      | access object   | operator info                                                     |
+-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
| HashJoin_27             | 2825288.58 | root      |                 | inner join, equal:[eq(db1.employees.emp_no, db1.salaries.emp_no)] |
| ├─TableReader_29(Build) | 300024.00  | root      |                 | data:TableFullScan_28                                             |
| │ └─TableFullScan_28    | 300024.00  | cop[tikv] | table:employees | keep order:false                                                  |
| └─TableReader_31(Probe) | 2844047.00 | root      |                 | data:TableFullScan_30                                             |
|  └─TableFullScan_30     | 2844047.00 | cop[tikv] | table:salaries  | keep order:false                                                  |
+-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)
```
----------------------------------------------------
After learning about `Optimizer Hints`, you might be curious about how to modify the execution plan of an SQL statement that is already part of someone else's program code or appears in multiple locations within the application code. In such scenarios, combining this with a feature called SQL binding is necessary.

```
tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no;
+-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+
| id                      | estRows    | task      | access object   | operator info                                                            |
+-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+
| MergeJoin_8             | 2825288.58 | root      |                 | inner join, left key:db1.employees.emp_no, right key:db1.salaries.emp_no |
| ├─TableReader_32(Build) | 2844047.00 | root      |                 | data:TableFullScan_31                                                    |
| │ └─TableFullScan_31    | 2844047.00 | cop[tikv] | table:salaries  | keep order:true                                                          |
| └─TableReader_30(Probe) | 300024.00  | root      |                 | data:TableFullScan_29                                                    |
|  └─TableFullScan_29     | 300024.00  | cop[tikv] | table:employees | keep order:true                                                          |
+-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+
5 rows in set (0.00 sec)

tidb> CREATE BINDING FOR
   -> SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no
   -> USING
   -> SELECT /*+ HASH_JOIN(employees, salaries) */ * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no;
Query OK, 0 rows affected (0.00 sec)

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

In the code above, the first SQL statement employs the `MergeJoin` method. We aim to change its execution plan without altering the SQL statement itself, including any `Optimizer Hints`.

To achieve this, we use the command `CREATE BINDING FOR` "current SQL statement" `USING` "target SQL statement"; to bind the desired execution plan.

After binding, when we execute the first SQL statement again, it utilizes the `HASH_JOIN` method. This indicates that our preferred execution plan is now bound to the original SQL statement, ensuring that whenever the database processes the bound SQL statement, it will use the specified execution plan. We can use `SHOW BINDINGS` to view all SQL statements with bound execution plans, as shown in the next slide.