Analyzes the similarities and differences between left and right connections of MySQL and equivalent connections

Three connection features

　Left join)

The on Clause connection condition is used to connect equivalent records in two tables, but does not affect the number of record sets. If a record in the left_table table cannot be found in the right_table table, the record is still displayed in the record set clock, only the right_table table needs to replace the value of the column displayed in the query with NULL;

In the on Clause connection condition, the table right_table. columnname = xxx is used to control whether the right_table table has column values that meet the requirements or whether it is displayed in the query column with null replacement, without affecting the number of record sets;

The where clause controls whether the record meets the query requirements. If the record does not meet the query requirements, it is filtered out;

Summary: The on Clause controls whether the column values of the right_table column match the display condition. If the column values do not match the display condition, null is used to replace the column values. This does not affect the records that meet the query condition; the where clause controls the records that are displayed in the final record set.

　　Right join)

The on Clause connection condition is used to connect equivalent records in Table 2. If a record in Table right_table cannot be found in Table left_table, then, the value of the left_able table needs to be replaced by null in the column displayed in the query;

In the on Clause connection condition, the table left_table. columnname = xxx is used to control whether the left_table table has column values that meet the requirements, or whether to use the null replacement method to display the values in the query list;

The where clause controls whether the record meets the query requirements. If the record does not meet the query requirements, it is filtered out;

Summary: The on Clause controls whether the column values of the left_table column match the display condition, but null is replaced if the column values do not match the query condition. This does not affect the records that meet the query condition; the where clause controls the records that are displayed in the final record set. We will find that left join and right join are similar, but match with the table on the left or right of the join keyword.

　　Equijoin

The on Clause connection condition is no longer the same as that of the Left or Right join. In addition to matching two table records, it also filters records, if the record in left_table cannot be found in right_table, it will be filtered out;

The where clause filters the record set, regardless of the constraints on the table left_table, the table right_table, or the conditions related to table 2 connections, refresh non-conforming records;

Address: http://tech.it168.com/a2011/0407/1175/000001175400_1.shtml