inner join

INNERJOIN is the most commonly used join operation. From a mathematical point of view, it is to find the intersection of two tables, and from the perspective of Cartesian product, it is to pick out the records whose ON clause conditions are satisfied from the Cartesian product.
In my opinion, inner join is similar to equijoin, and natural join is a special kind of inner join

The difference between natural join and equijoin

Natural join is a special equi-join, which requires that the components to be compared in the two relations must be the same attribute group, and the duplicate attribute columns are removed from the result

what is equijoin

Equijoin is a common connection method of relational operation-join operation. It is a special case of conditional connection (or θ connection) when the connection operator is the “=” sign, that is, when θ=0.

table aaa

table bbb:

Query the data with equal attributes C B D in table aaa and table bbb

Equijoin query:

SElect*from aaa,bbb WHERE aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
result:

-- natural join keyword natural join
SELECT*from aaa natural join bbb;

It can be seen from the above two results that the equijoin is to take the data with the specified value equal from the intersection of the two table data. The natural join is to take the data with the same attribute and equal value in the two tables, and eliminate the same attribute column.

How inner joins are implemented

The first: where

SElect*from aaa,bbb WHERE aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E

The second: inner join

select*from aaa inner join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;

The third type: join

select*from aaa join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;

Fourth: STRAIGHT_JOIN

select*from aaa STRAIGHT_JOIN bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;

The latter three have the same effect, and the third can be understood as the abbreviation of the second. The main difference between the fourth and the second is that the insertion method is different, and the performance of the fourth is slightly lower

outer join

There are three types of outer joins: left outer join, right outer join, and full outer join. Corresponding SQL: LEFT/RIGHT/FULL OUTER JOIN.

left join

What is a left join?

The meaning of left join LEFT JOIN is to find the intersection of two tables A table and B table plus the remaining data of the left table. Still from the perspective of Cartesian product, it is to first pick out the records whose ON clause conditions are satisfied from the Cartesian product, and then add the remaining records in the left table A table.

select*from aaa left join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;

right join

What is right join?

Similarly, right join RIGHT JOIN is to find the intersection of two tables A and B plus the remaining data of right table B. Described again from the perspective of Cartesian product, the right join is to pick out the records whose ON clause conditions are satisfied from the Cartesian product, and then add the remaining records in the right table

select*from aaa right join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;

fully connected

A full outer join is to find the union of the sets of two tables A and B. From the perspective of Cartesian product, it is to pick out the records whose ON clause conditions are satisfied from the Cartesian product, then add the remaining records in the left table, and finally add the remaining records in the right table. In addition, MySQL does not support OUTER JOIN, but we can do the UNION operation on the results of left join and right join.

select*from aaa left join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
union 
select*from aaa right join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E