Simple Introduction: Left connection, right connection, and equivalent connection of MySQL

Author: skate
Time: 2012/02/18

 

Simple Introduction: Left connection, right connection, and equivalent connection of MySQL

 

Three connection 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.

 

1. 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 .....

 

2. 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 .....

 

3. 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:

 

A. 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;

 

To put it simply, "left_table.columnname_join = right_table.columnname_join" is used to count the Record Sets of equivalent join of two tables. The on Clause
The join condition is to limit the size of the record set in right_table. The condition in the WHERE clause is to limit the size of the final result set. The size of the final result set is to display all records of the "left_table" table, and the "right_table" matching "right_table" records display the "right_table" column values. If they do not match, the corresponding "right_table" is displayed as null (the size of the final result set is the number of records for the left_table partition where condition)

B. Right join)

The on Clause join condition is used to connect the equivalent records in Table 2. If a record in Table right_table cannot be found in Table left_table, then, the left_table table needs to replace the value of the column displayed in the query with NULL;

 

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 null replacement 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;

 

To put it simply, "left_table.columnname_join = right_table.columnname_join" is used to count the Record Sets of equivalent join of two tables. The on Clause
The join condition is to limit the size of the record set of left_table. The condition of the WHERE clause is to limit the size of the final result set. The size of the final result set is to display all records of the "right_table" table, and the records that match "left_table" with "right_table" show "left_table" column values. If they do not match, the corresponding "left_table" is displayed as null (the size of the final result set is the number of records for the left_table partition where condition)

C. equijoin

The on Clause join condition no longer works the same as the left or right join condition. It serves as a condition for matching two table records and 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 data:

 

Create a table
Mysql> Create Table left_table (ID int unsigned not null auto_increment,
-> Username varchar (40) not null,
-> Birthday datetime not null default '2017-00-00 00:00:00 ',
-> Cityid smallint not null default 0,
-> Creatdate timestamp not null default '2017-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 ';
Query OK, 0 rows affected (0.11 Sec)

 

Mysql>
Mysql> 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 '2017-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 ';
Query OK, 0 rows affected (0.00 Sec)

 

Insert Test Data

Execute the following SQL ten times
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 the following SQL 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;

View basic data:

Mysql> select * From right_table;
+ ----- + ------------ + -------- + ----------- + --------------------- +
| Uid | collectnum | buynum | searchnum | creatdate | alterdate |
+ ----- + ------------ + -------- + ----------- + --------------------- +
| 1 | 2545 | 78 | 171 | 17:42:37 | 17:42:37 |
| 6 | 4951 | 96 | 325 | 17:42:55 | 17:42:55 |
+ ----- + ------------ + -------- + ----------- + --------------------- +
2 rows in SET (0.00 Sec)

 

Mysql> select * From left_table;
+ ---- + ----------------- + --------------------- + -------- + --------------------- +
| ID | username | birthday | cityid | creatdate | alterdate |
+ ---- + ----------------- + --------------------- + -------- + --------------------- +
| 1 | 27311524@qq.com | 17:42:37 | 5 | 17:42:37 | 17:42:37 |
| 2 | 01644610@qq.com | 17:42:49 | 39 | 17:42:49 | 17:42:49 |
| 3 | 89103050@qq.com | 17:42:52 | 44 | 17:42:52 |
| 4 | 27363743@qq.com | 17:42:53 | 32 | 17:42:53 | 17:42:53 |
| 5 | 09483603@qq.com | 17:42:54 | 19 | 17:42:54 | 17:42:54 |
| 6 | 38929060@qq.com | 17:42:55 | 11 | 17:42:55 | 17:42:55 |
| 7 | 15852433@qq.com | 17:42:56 | 68 | 17:42:56 | 17:42:56 |
| 8 | 22581017@qq.com | 17:42:57 | 59 | 17:42:57 | 17:42:57 |
| 9 | 54863339@qq.com | 17:42:59 | 91 | 17:42:59 | 17:42:59 |
| 10 | 36242195@qq.com | 17:43:00 | 51 | 17:43:00 | 17:43:00 |
+ ---- + ----------------- + --------------------- + -------- + --------------------- +
10 rows in SET (0.00 Sec)

 

Mysql> select M. ID, M. Username from left_table m where M. ID <= 6;
+ ---- + ----------------- +
| ID | username |
+ ---- + ----------------- +
| 1 | 27311524@qq.com |
| 2 | 01644610@qq.com |
| 3 | 89103050@qq.com |
| 4 | 27363743@qq.com |
| 5 | 09483603@qq.com |
| 6 | 38929060@qq.com |
+ ---- + ----------------- +
6 rows in SET (0.02 Sec)

 

Left join)

 

No.: SQL _1
Mysql> 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 | 27311524@qq.com | 2545 | 78 |
| 2 | 01644610@qq.com | null |
| 3 | 89103050@qq.com | null |
| 4 | 27363743@qq.com | null |
| 5 | 09483603@qq.com | null |
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
6 rows in SET (0.02 Sec)

 

No.: SQL _2
Mysql> select M. ID, M. username, N. collectnum, N. buynum from left_table m left join right_table n on M. id = n. UID and N. searchnum & gt; 300 where M. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 1 | 27311524@qq.com | null |
| 2 | 01644610@qq.com | null |
| 3 | 89103050@qq.com | null |
| 4 | 27363743@qq.com | null |
| 5 | 09483603@qq.com | null |
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
6 rows in SET (0.00 Sec)

 

Comparison between SQL _1 and SQL _2
1. The number of records in the two result sets is the same, and the column values displayed in left_table are the same.
2. The values of the corresponding right_table columns in the two result sets are different.

 

Why is it different?
The number of records in the two record sets is the same because the where condition of the two SQL statements is the same, that is, the number of the final result sets will be the same, the corresponding column values of the right_table are different because SQL _2 filters out the matching records of the right_table IN THE on condition.

The right connection is similar. The following describes the equivalent connection.

 

Equivalent join with join
Mysql> select M. ID, M. username, N. collectnum, N. buynum
-> From left_table m inner join right_table n on M. ID = n. uid
-> Where M. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 1 | 27311524@qq.com | 2545 | 78 |
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
2 rows in SET (0.00 Sec)

Mysql> select M. ID, M. username, N. collectnum, N. buynum from left_table m inner join right_table n on M. id = n. UID and N. searchnum & gt; 300 where M. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
1 row in SET (0.01 Sec)

 

From the above, we can see that on has the function of filtering the final result set size.

 

Equivalent join without join (this method is more comfortable, because it has been used in Oracle)
Mysql> select M. ID, M. username, N. collectnum, N. buynum
-> From left_table M, right_table n
-> Where M. ID = n. UID and M. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 1 | 27311524@qq.com | 2545 | 78 |
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
2 rows in SET (0.00 Sec)

Mysql>

 

Left join for Common Errors

 

1. Put the Condition Limiting the table right_table in the where clause from the on clause, which will affect the size and size of the final record set.

Mysql> select M. ID, M. username, N. collectnum, N. buynum from left_table m left join right_table n on M. id = n. UID and N. searchnum & gt; 300 where M. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 1 | 27311524@qq.com | null |
| 2 | 01644610@qq.com | null |
| 3 | 89103050@qq.com | null |
| 4 | 27363743@qq.com | null |
| 5 | 09483603@qq.com | null |
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
6 rows in SET (0.00 Sec)

 

Select M. ID, M. username, N. collectnum, N. buynum from left_table M, right_table n where M. id = n. UID and M. ID <= 6 and N. searchnum> 300;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
1 row in SET (0.00 Sec)

 

2. Put the restriction table left_table or the condition that affects the final record set in the WHERE clause into the on clause, which is equivalent to the increasing number of records in the final result set.

Select M. ID, M. username, N. collectnum, N. buynum from left_table M, right_table n where M. id = n. UID and M. ID <= 6 and N. searchnum> 300;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
1 row in SET (0.00 Sec)

Mysql> select M. ID, M. username, N. collectnum, N. buynum from left_table m left join right_table n on M. id = n. UID and N. searchnum & gt; 300 where M. ID <= 6;
+ ---- + ----------------- + ------------ + -------- +
| ID | username | collectnum | buynum |
+ ---- + ----------------- + ------------ + -------- +
| 1 | 27311524@qq.com | null |
| 2 | 01644610@qq.com | null |
| 3 | 89103050@qq.com | null |
| 4 | 27363743@qq.com | null |
| 5 | 09483603@qq.com | null |
| 6 | 38929060@qq.com | 4951 | 96 |
+ ---- + ----------------- + ------------ + -------- +
6 rows in SET (0.00 Sec)

 

 

 

 

 

---------- END ----------