MYSQL left connection right connection and the internal connection of the detailed and difference _mysql

MYSQL left connection right connection and the difference between the inner joins , here on these concepts through an example, explained clearly.

The code is as follows:

drop table table1;

CREATE TABLE ' Andrew '. ' Table1 '

(

' name ' VARCHAR () not NULL,

' city ' VARCHAR () 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 () not NULL,

' city ' VARCHAR () 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 connection result

Table1 left, so that the left connection. In this case, the main table1, that is, all the records in Table1 are listed. There are three kinds of situations:

A. For each record in the Table1 the city if it happens to exist in the table2 and there is just one, then the

A new record is formed in the returned result. As above person A and person B correspond.

B. A city corresponding to each record in the Table1 if there is also an n bar in the Table2, then an n new record will be formed in the returned result. As the person C above corresponds to the case.

C. A city corresponding to each record in the Table1 if it does not exist in the table2, it will form an entry in the returned result

A new record, and the right side of the record is all null. As in the case of person D above.

Records that do not conform to the above three rules are not listed.

2. Outer join-Right connection result

Table2 right, so that the right connection. In this case, the main table2, that is, all the records in Table2 are listed. There are three kinds of situations:

A. For each record in the table2 the city if it happens to exist in the Table1 and there is just one, then the

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

B. A city corresponding to each record in the table2 if there is also an n bar in the Table1, then an n new record will be formed in the returned result. As the person W above corresponds to the situation.

C. A city corresponding to each record in the table2 if it does not exist in the Table1, it will form an entry in the returned result

A new record, and the left side of the record is all null. such as the person Z above corresponds to the situation.

Records that do not conform to the above three rules are not listed.

3. Internal connection

Null is not present in the data record for the connection within. It is simple to assume that the result of the inner link is the result of excluding a record of NULL in the field in the result of a left or right join. It is even possible to think that if only the data records of the two tables that are left with the inner JOIN operation, such as Table1 only person A, person B, and person c,table2, only person W, Person X, and man Y, Then the left and right connections between the two tables return the same result.

Note:select * FROM table1 a INNER join table2 b on a.city = b.city and select * FROM table1 a join table2 B on a.city = The effect of b.city is the same, that is, if the left side of the join does not have a keyword such as right or right or inner, the default is the inner join. Also, MySQL does not support full join.

Thank you for reading, I hope to help you, thank you for your support for this site!