# Working with Joins - Case Study - Cont'd

+ `Hash Join` operator estimates the result set size based on the row counts and the selectivity of the join columns
+ Row_Num_TD x Selectivity_col2_TD x Row_Num_TA = 2000 x (1/101) x 1000 = 19,801.98
+ In fact, the join result should be only ten rows (see page 12)

```
tidb>SELECT COUNT(DISTINCT(col2)) FROM TA;
+-----------------------+
| COUNT(DISTINCT(col2)) |
+-----------------------+
|                   100 |
+-----------------------+
1 row in set (0.01 sec)

tidb>SELECT COUNT(DISTINCT(col2)) FROM TD;
+-----------------------+
| COUNT(DISTINCT(col2)) |
+-----------------------+
|                   101 |
+-----------------------+
1 row in set (0.01 sec)

tidb>EXPLAIN SELECT COUNT(*) FROM TA, TD WHERE TA.col2 = TD.col2;
+-------------------------------+----------+-----------+-------------------------------------------+----------------------------------------------------+
| id                            | estRows  | task      | access object                             | operator info                                      |
+-------------------------------+----------+-----------+-------------------------------------------+----------------------------------------------------+
| HashAgg_9                     | 1.00     | root      |                                           | funcs:count(1)->Column#13                          |
| └─HashJoin_21                 | 19801.98 | root      |                                           | inner join, equal:[eq(test.ta.col2, test.td.col2)] |
|   ├─IndexReader_25(Build)     | 1000.00  | root      |                                           | index:IndexFullScan_24                             |
|   │ └─IndexFullScan_24        | 1000.00  | cop[tikv] | table:TA, index:idx_col1_col2(col1, col2) | keep order:false                                   |
|   └─IndexReader_29(Probe)     | 2000.00  | root      |                                           | index:IndexFullScan_28                             |
|     └─IndexFullScan_28        | 2000.00  | cop[tikv] | table:TD, index:idx_col2(col2)            | keep order:false                                   |
+-------------------------------+----------+-----------+-------------------------------------------+----------------------------------------------------+
6 rows in set (0.00 sec)

tidb>SELECT COUNT(*) FROM TA, TD WHERE TA.col2 = TD.col2;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.01 sec)
```
----------------------------------------------------
We observed that the `Hash Join` operator estimates the result set size based on the row counts of the driving and driven tables, along with the selectivity of the join columns. However, it does not consider the distribution and correlation of data between these tables. In the example provided, the selectivity of column `col2` in the driven table `TD` is 1/101. The row count for table `TA` is 1,000, and for table `TD`, it is 2,000. This leads to an estimated result set size of 2000 x (1/101) x 1000 = 19,801.98. In reality, though, only one row in column `col2` of table `TD` matches with column `col2` in table `TA`, resulting in just 10 rows in the actual result set. Consequently, the `estRows` estimated by the optimizer significantly deviates from the actual result.