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

Since I started my career, I have found that many technical staff and colleagues have consulted the author or on the Internet about how to write LEFT JOIN and its features, and the equivalent connection is generally normal, I wrote a Microsoft SQL Server version a few years ago. I wrote another MySQL version here. I hope the technicians who have read it can help me solve the problem.

1. Three Join syntaxes

To make it easier for more technical staff to quickly understand and understand, we only discuss the connection operation of two table objects. The same is true for the connection operation of more than two table objects.

LEFT JOIN)

Select m. columnname ......, N. * columnname .....
FROM left_table m left join right_table n on m. columnname_join = N. columnname_join and n. columnname = XXX
Where m. columnname = XXX .....
RIGHT JOIN)

Select m. columnname ......, N. * columnname .....
FROM left_table m right join right_table n on m. columnname_join = N. columnname_join and m. columnname = XXX
Where n. columnname = XXX .....
Equijoin

Select m. columnname ......, N. * columnname .....
FROM left_table M [INNER] JOIN right_table n on m. columnname_join = N. columnname_join
Where m. columnname = XXX ..... And n. columnname = XXX ....
Or
Select m. columnname ......, N. * columnname .....
FROM left_table M, right_table N
Where m. columnname_join = N. columnname_join AND
M. columnname = XXX ..... And n. columnname = XXX ....
Note: Pay attention to the bold red font section in the preceding SQL syntax and the changes in the three different connection syntaxes.

 

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;

 

Test environment for connection syntax

Test Table Structure

Create table left_table (id int unsigned not null AUTO_INCREMENT,
Username VARCHAR (40) not null,
Birthday datetime not null default 0000-00-00 00:00:00,
CityID smallint not null default 0,
CreatDate timestamp not null default 0000-00-00 00:00:00,
AlterDate timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Primary key (ID ),
KEY idx_username (Username)
) ENGINE = InnoDB character set utf8 COLLATE utf8_general_ci;

Create table right_table (uid int unsigned not null,
CollectNum mediumint not null default 0,
BuyNum mediumint not null default 0,
SearchNum mediumint not null default 0,
CreatDate timestamp not null default 0000-00-00 00:00:00,
AlterDate timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Primary key (UID)
) ENGINE = InnoDB character set utf8 COLLATE utf8_general_ci;
Data used for testing

Execute the statement ***** VALUE *** for 10 times repeatedly:

Insert into left_table (Username, Birthday, CityID, CreatDate, AlterDate)
VALUES (CONCAT (SUBSTRING (RAND (), 3, 8), @ qq.com), DATE_ADD (NOW (), INTERVAL-SUBSTRING (RAND (), 3, 2) YEAR ), SUBSTRING (RAND (), 3, 2), DATE_ADD (NOW (), INTERVAL-SUBSTRING (RAND (), 3, 3) DAY), DATE_ADD (NOW (), INTERVAL-SUBSTRING (RAND (), 3, 2) DAY ));
Execute an INSERT ** SELECT ** statement:

Insert into right_table
Select id, SUBSTRING (RAND (), 3,4) AS CollectNum, SUBSTRING (RAND (), 3,2) AS BuyNum, SUBSTRING (RAND (), 3,3) AS SearchNum, CreatDate, AlterDate
FROM left_table where id % 5 = 1;
Note: The left_table table will have 10 consecutive records with ID values 1, 2, 3, *** and 10. The right_table table contains 2 discrete records with ID values 1 and 6, and the join conditions for table 2 are: left_table.ID = right_table.UID.

 

Comparison Test

Data that may be viewed in the benchmark test table (No.: SQL _1)

Root @ localhost: eugene 03:25:07> select m. ID, M. username FROM left_table m where m. ID <= 6;
+ ---- + ----------------- +
| ID | username |
+ ---- + ----------------- +
| 1 | 06440350@qq.com |
| 2 | 25173782@qq.com |
| 3 | 66328120@qq.com |
| 4 | 16752438@qq.com |
| 5 | 92117196@qq.com |
| 6 | 02026078@qq.com |
+ ---- + ----------------- +
Standard left join

SQL statements and data (No.: SQL _2) without conditions other than connection fields in ON statements)

Root @ localhost: eugene 03:37:58> select m. ID, M. username, N. CollectNum, N. BuyNum
-> FROM left_table m left join right_table n on m. ID = N. UID
-> Where m. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | CollectNum | BuyNum |
+ ---- + ----------------- + ------------ + -------- +
| 1 | 06440350@qq.com | 817 | 39 |
| 2 | 25173782@qq.com | NULL |
| & N