Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve left connection and right connection in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Take MySql as an example. Create two data tables in the MySQL database and insert some data respectively.

The example script 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 dating,' BJ'); insert into TABLE1 (name, city) values ('Person financing,' BJ'); insert into TABLE1 (name, city) values ('Person accounts,' SH'); insert into TABLE1 (name, city) values ('Person dating,' 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 walls,' BJ'); insert into TABLE2 (name, city) values ('Person Xerox,' SH'); insert into TABLE2 (name, city) values ('Person Yoshou,' SH'); insert into TABLE2 (name, city) values ('Person Zero,' NJ');

1. MySQL external connection-left connection result

Table1 is on the left, so it is called the left connection. In this case, all records in the table1, that is, table1, are listed. There are three situations:

a. For each record in table1, if the corresponding city also exists in table2 and happens to have only one, then it will be in the

A new record is formed in the returned result. As in the case of Person An and Person B above.

b. For each record in table1, if the corresponding city also happens to exist in table2 and has N records, then N new records will be formed in the returned results. As in the case of Person C above.

c. For each record in the table1, if the corresponding city does not exist in the table2, then one will be formed in the returned result.

A new record with all NULL on the right side of the record. As in the case of Person D above.

Records that do not meet the above three rules will not be listed.

2. MySQL external connection-right connection result

Table2 is on the right, so it is called the right connection. In this case, all records in the table2, that is, table2, are listed. There are three situations:

a. For each record in table2, if the corresponding city also exists in table1 and happens to have only one, then it will be in the

A new record is formed in the returned result. As in the case of Person X and Person Y above.

b. For each record in table2, if the corresponding city also happens to exist in table1 and has N records, then N new records will be formed in the returned results. As in the case of Person W above.

c. For each record in the table2, if the corresponding city does not exist in the table1, then one will be formed in the returned result.

A new record, and all NULL on the left side of the record. As in the case of Person Z above.

Records that do not meet the above three rules will not be listed.

3. MySQL intra-connection

There is no case where the field is NULL in the data records connected within MySQL. You can simply think of the result of the inner link as the result of excluding the record with the existing field of NULL from the result of the left or right join. It can even be considered that if only the data records obtained from the inner join operation are left in the two tables, for example, there are only Person A, Person B in table1, and only Person W, Person X and Person Y in Person CMI Table 2, then the results returned by the left join and right join between the two tables are the same.

Note: select * from table1 an inner join table2 b on a.city = b.city and select * from table1 a join table2 b on a.city = b.city have the same effect, that is, if there are no keywords such as left, right, or inner on the left side of join, the default is inner concatenation. In addition, MySQL does not support full join.

After reading the above, do you have any further understanding of how to achieve left and right connections in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.