Join query in MySQL

Preface

What is Mysql's join? The word join means to join, participate, and connect. In the database, it also means to connect. Connect the two tables to query the data we want. In the database, the usage of join is mainly divided into three types, namely left join, right join and inner join, but in actual use, there are seven kinds of operations between two tables, so let’s start to understand this today. Seven ways to use it

All the ellipses below represent two different tables. Assume that the left is the test1 table, the right is the test2 table, and the following is the case of the two tables

The test1 table has three fields: uid, name, and region

The test2 table has four fields: uid, name, gender, and age

1. Fully connected

As shown in the figure, the two tables are fully connected. Suppose that there are M records in A table and N records in B table. When fully connected, the Cartesian product is used to calculate, so the query is M×N. recording

The sql statement and query results of the two tables test1 and test2 are as follows

SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid UNION SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid = test2.uid;

Query all the data in the two tables, if there is no data, it will be empty

Summary: The content of the query is all the content of the two tables

2. Go to the intersection

As shown in the figure, the two tables are intersected to join the query, and the full join of the two tables removes the intersecting part in the middle is to intersect

The sql statement and results of the two tables test1 and test2 to intersect and join are as follows

 SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid WHERE test2.uid IS NULL 
 UNION SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid = test2.uid WHERE test1.uid IS NULL


For the information queried, the common part of 101~107 is removed, and the unique parts of the two tables are queried-108 and 109 of test1, and 100 of test2

Summary: The content of the query is the content of the two tables without common parts

3. Left connection

As shown in the figure, the two tables are left-connected and query, and the remaining content of the left table is added according to the same part

The sql statement and results of the two tables test1 and test2 to intersect and join are as follows

SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid;

Just looking at the picture seems to be no different from querying only the left table. In fact, the common parts of the two tables are queried together. If there is data in the right table, it will be found out, and if there is no data, it will be empty.

It mainly depends on which table is the main one. Here, the left table is the main one. The data of the left table is queried. If there is data in the right table, it is displayed, and if there is no data, it is empty.

Summary: The query is the common part of the two tables plus the rest of the left table

4. Right connection

The sql statement and results of the two tables test1 and test2 to intersect and join are as follows

SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid =test2.uid;


Similar to the left join, just looking at the picture seems to be no different from only querying the right table. In fact, the common parts of the two tables are also queried together. If there is data in the left table, it will be found out, and if there is no data, it will be empty.

It mainly depends on which table is the main one. Here, the left table is the main one. The data of the left table is queried. If there is data in the right table, it is displayed, and if there is no data, it is empty.

Summary: The query is the common part of the two tables plus the rest of the right table

5. Internal connection

The sql statement and results of the two tables test1 and test2 to intersect and join are as follows

SELECT * FROM test1 INNER JOIN test2 ON test1.`uid` = test2.`uid`;


In the table, 109 and 108 in test1 and 100 in test2 are not queried

Summary: The content of the query is the common part of the two tables

6. Left only

The sql statement and results of the two tables test1 and test2 to intersect and join are as follows

SELECT * FROM test1 LEFT JOIN test2 ON test1.uid = test2.uid WHERE test2.uid IS NULL;


Here, the unique 108 and 109 data in the test1 table are queried

Summary: The query is the content that is not in the right table in the left table

7. Right only

The sql statement and results of the two tables test1 and test2 to intersect and join are as follows

SELECT * FROM test1 RIGHT JOIN test2 ON test1.uid = test2.uid WHERE test1.uid IS  NULL;


Here, the unique 100 data in the test2 table is queried

Summary: The query is the content that is not in the left table in the right table