MySQL's left and right internal connection usage example

This article illustrates MySQL's right and left internal connection usage. Share to everyone for your reference. Specifically as follows:

Use an example to resolve the MySQL left connection, the right connection and the inner join

The

code is as follows: CREATE TABLE user_id (ID decimal (18));


CREATE TABLE User_profile (ID decimal (), name varchar (255));


INSERT INTO user_id values (1);


INSERT INTO user_id values (2);


INSERT into user_id values (3);


INSERT INTO user_id values (4);


INSERT INTO user_id values (5);


INSERT INTO user_id values (6);


INSERT INTO user_id values (1);

INSERT into user_profile values (1, "AA");
INSERT into user_profile values (2, "BB");
INSERT into User_profile values (3, "CC");
INSERT into User_profile values (4, "DD");
INSERT into User_profile values (5, "ee");
INSERT into User_profile values (5, "EE");
INSERT into user_profile values (8, ' zz ');

A. Left connection:

The code is as follows: Mysql> select a.ID ID, ifnull (b.name, ' n/a ') name from user_id a LEFT join User_profile b on a.id = b.ID;


mysql> Select a.ID ID, ifnull (b.name, ' n/a ') name from user_id a LEFT join User_profile b on a.id = b.ID;


+------+------+


| ID | name |


+------+------+


| 1 | AA |


| 2 | bb |


| 3 | CC |


| 4 | DD |


| 5 | EE |


| 5 | EE |


| 6 | N/A |


| 1 | AA |


+------+------+


8 rows in Set (0.00 sec)

User_ID left, so that the left connection. In this case, the main user_id, that is, all the records in USER_ID are listed. The following three kinds of situations are divided:

1. The ID corresponding to each record in the user_id if it exists in the user_profile and is just one, a new record will be formed in the returned result. As above 1, 2, 3, 4 corresponds to the case.
2. For each record in the user_id, if the ID corresponds to the user_profile and there are n bars, then an n new record will be formed in the returned result. As the above 5 corresponds to the case.
3. For each record in user_id, if the ID is not present in the User_profile, a new record is formed in the returned result, and the right of the record is all null. As the above 6 corresponds to the case.

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

For example, to query for data that does not exist in a related table, by ID Association, to find records in the USER_ID table that do not exist in the User_profile:

The

code is as follows: SELECT COUNT (*) from user_id left join user_profile on user_id.id = user_profile.id where user_profile.id is n ull;

Two. Right connection

User_profile right, so that the right connection. In this case, the main user_profile, that is, user_profile all the records will be listed. The following three kinds of situations are divided:

1. The ID corresponding to each record in the user_profile if it exists in the user_id and is just one, a new record will be formed in the returned result. As above 2, 3, 4, 5 corresponds to the case.
2. For each record in the User_profile, if the ID corresponds to the user_id and there are n bars, then an n new record will be formed in the returned result. As the above 1 corresponds to the case.
3. For each record in the User_profile, if the ID is not present in the USER_ID, a new record is formed in the returned result, and the left side of the record is all null. As the above 8 corresponds to the case.

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

Three. Internal connection

In a data record that is connected within MySQL, no null is present in the field. It is easy to assume that the result of the inner link is the result of excluding a record of NULL in a left or right connection, and that MySQL does not support full join

The

code is as follows: Mysql> select * from user_id a INNER join user_profile b on a.id = b.ID;


+------+------+------+


| ID | ID | name |


+------+------+------+


|    1 | 1 | AA |


|    1 | 1 | AA |


|    2 | 2 | bb |


|    3 | 3 | CC |


|    4 | 4 | DD |


|    5 | 5 | EE |


|    5 | 5 | EE |


+------+------+------+


7 rows in Set (0.00 sec)


Mysql> SELECT * from user_id A, user_profile b where a.id = b.ID;


+------+------+------+


| ID | ID | name |


+------+------+------+


|    1 | 1 | AA |


|    1 | 1 | AA |


|    2 | 2 | bb |


|    3 | 3 | CC |


|    4 | 4 | DD |


|    5 | 5 | EE |


|    5 | 5 | EE |


+------+------+------+


7 rows in Set (0.00 sec)


Mysql> SELECT * from user_id a join user_profile b on a.id = b.ID;


+------+------+------+


| ID | ID | name |


+------+------+------+


|    1 | 1 | AA |


|    1 | 1 | AA |


|    2 | 2 | bb |


|    3 | 3 | CC |


|    4 | 4 | DD |


|    5 | 5 | EE |


|    5 | 5 | EE |


+------+------+------+


7 rows in Set (0.00 sec)

I hope this article will help you with the MySQL program design.