Detailed explanations and differences between Left-connected and right-connected MYSQL and internal-connected mysql

Detailed explanations and differences between Left-connected and right-connected MYSQL and internal-connected mysql

Difference between left and right connections and internal connections of MYSQLHere, we will go through an example to explain these concepts clearly.

The Code is as follows:

drop table table1;CREATE TABLE `andrew`.`table1`(`name` VARCHAR(32) NOT NULL,`city` VARCHAR(32) NOT NULL)ENGINE = MyISAM;insert into TABLE1(name, city) values ('Person A', 'BJ');insert into TABLE1(name, city) values ('Person B', 'BJ');insert into TABLE1(name, city) values ('Person C', 'SH');insert into TABLE1(name, city) values ('Person D', 'SZ');commit;drop table table2;CREATE TABLE `andrew`.`table2`(`name` VARCHAR(32) NOT NULL,`city` VARCHAR(32) NOT NULL)ENGINE = MyISAM;insert into TABLE2(name, city) values ('Person W', 'BJ');insert into TABLE2(name, city) values ('Person X', 'SH');insert into TABLE2(name, city) values ('Person Y', 'SH');insert into TABLE2(name, city) values ('Person Z', 'NJ');commit;

1. Outer Join-left join result

Table1 is left, so it is the left join. In this case, all records in Table 1 are listed. There are three situations:

A. if the city corresponding to each record in table 1 also exists in table 2 and there is only one record

A new record is generated in the returned result. As shown in the figure above, Person A corresponds to Person B.

B. If the city corresponding to each record in table 1 also exists in table 2 and there are N records, N new records will be generated in the returned results. The following figure shows the situation of Person C.

C. If the city corresponding to each record in Table 1 does not exist in table 2,

New record, and all the right side of the record is NULL. The following figure shows the situation of Person D.

Records that do not comply with the preceding three rules will not be listed.

2. Outer Join-right join result

Table2 is the right join. In this case, all records in Table 2 are listed. There are three situations:

A. if the city corresponding to each record in Table 2 also exists in Table 1 and there is only one record

A new record is generated in the returned result. As shown in the preceding figure, the Person X corresponds to Person Y.

B. If the city corresponding to each record in Table 2 also exists in Table 1 and there are N records, N new records will be generated in the returned results. The following figure shows the situation of Person W.

C. If the city corresponding to each record in Table 2 does not exist in table 1,

New record, and all NULL on the left of the record. The following figure shows the situation of Person Z.

Records that do not comply with the preceding three rules will not be listed.

3. Internal Connection

No field is NULL in the data record of the internal connection. We can simply think that the result of the inner link is the result obtained after the record with the NULL field is removed from the result of the Left or Right join. It can even be considered that if the two tables only have the data records obtained after the inner join operation, for example, Table 1 contains only Person A, Person B, and Person C, table 2 contains only Person W, Person X, and Person Y. The left join and right join results of the two tables are the same.

Note: select * from table1 a inner join table2 B on. city = B. city and select * from table1 a join table2 B on. city = B. the city effect is the same, that is, if the left side of the join has no keywords such as left, right, or inner, the default value is inner join. MySQL does not support full join.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!