What does mysql join mean?

In mysql, join means "connection". The main function of join is to obtain data that exists in different tables based on the relationship between columns in two or more tables. Join refers to connecting two tables, which are the "driving table" and the "driven table". Join connections are divided into three categories: 1. Inner join, the queried data is the intersection of two tables; 2. Outer join, the connected table will first be divided into a base table and a reference table, and then returned based on the base table. Records that meet and do not meet the conditions; 3. Full connection, all data in the left and right tables can be queried.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Everyone should be familiar with join. Join can connect two tables.

Introduction to join

join refers to joining two tables, the two tables are the driving table and the driven table.

Join in the database becomes a connection. The main function of the connection is to obtain data that exists in different tables based on the relationship between columns in two or more tables. There are three types of connections: inner join, outer join, full join

join example

1.Inner join
The data queried by the inner connection is the intersection of the two tables, which is the part represented by red in the figure above.

2. Left outer join
The content of the left outer link in the red part in the figure is that it contains all the rows of the left table (regardless of the right whether there are matching rows in the table), and all matching rows in the right table.

3. Right external link
The content of the right external link in the red part in the figure is that it contains all the rows of the right table (regardless of the left (whether there are matching rows in the table), and all matching rows in the left table.

4. Left join

The left join uses the red part in the figure to query the unique data of the left table

Analysis: In fact, the above picture is based on the left outer join. The left outer join obtains the left table, but the left table also contains some parts that both the left and right tables have. Areas with the same data, this part of the same data needs to be removed. The condition for removal is B.key IS NULL

##5. Right join

If you understand the above left join, then the right join is similar, it is to query the unique data of the right table

6.Full Connection (The writing method below is not supported in Mysql)

Query all the data in the left and right tables

But! This way of writing is not supported in MySQL, so other methods can only be used.

All of A and B are the data unique to A, unique to B and jointly owned by A and B

Can be used in Mysql:

select * from Table A left join Table B on A.Key = B.Key (Find all of A)

## select * from Table A right join Table B on A.Key = B.Key (Find all of B)

7. Full outer join (

Mysql does not support the writing method below )

Data query other than the common data of the left and right tables

Filter out that for table A, B is empty, and for table B, For table B, if A is empty

MySQL does not support this writing method, so other methods can only be used.

In fact, a full external join is unique to A and unique to B

Mysql syntax: select * from Table A left join Table B on A.Key = B.Key where B.Key is null (Find out what is unique to A)

                                                                                                                                                                                                                                                                                        because (Find what is unique to B)

[Related recommendations: mysql video tutorial]

The above is the detailed content of What does mysql join mean?. For more information, please follow other related articles on the PHP Chinese website!