Inner join
Inner join INNERJOIN is the most commonly used connection operation. Described in mathematical terms, this sentence can be rewritten as: From the perspective of set theory, we require the intersection of two sets; and from the perspective of Cartesian product, we need to filter out the Cartesian product elements that satisfy the ON condition.
In my opinion, inner joins are similar to equal joins. Natural joins are a special join in inner joins.
Natural joins and equal joins The difference between value joins
What is a natural join?
What is an equijoin?
Example analysis:
Table aaa

Table bbb:

##Query the data with equal attributes C B D in table aaa and table bbb
Equal join query:
SElect*from aaa,bbb WHERE aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
Copy after login
Copy after login
Result:

Natural join query:
-- 自然连接用关键字 natural join
SELECT*from aaa natural join bbb;
Copy after login
Result:

It can be concluded from the above two results that equijoin selects specific data with equal values from the intersection of two tables. Natural join refers to taking data with the same attributes and equal attribute values in two tables and eliminating these same attribute columns.
How to implement inner join
The first one: where
SElect*from aaa,bbb WHERE aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
Copy after login
Copy after login
The second one: inner join
select*from aaa inner join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
Copy after login
The third one: join
select*from aaa join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
Copy after login
The fourth one: STRAIGHT_JOIN
select*from aaa STRAIGHT_JOIN bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
Copy after login
The latter three have the same effect, and the third one can be understood as the abbreviation of the second one. The main difference between the fourth and second type is the insertion method. The performance of the fourth type is slightly lower.
Outer join
- There are three types of outer joins: left outer join , right outer join, full outer join. You can use left joins, right joins, and full joins in SQL statements to achieve this function.
Left join
What is a left join?
LEFT JOIN means to take the intersection of table A and table B, and also include the remaining data in the left table. From the perspective of Cartesian product, we need to first select the records that meet the conditions of the ON clause, and then add the remaining records in the left table A

Code implementation:
select*from aaa left join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
Copy after login
Result:

Right join
What is a left join?
- Similarly, RIGHT JOIN is equivalent to obtaining the intersection of table A and table B, plus the remaining data of table B. When describing the right join, you can start from the perspective of Cartesian product, that is, select the records that meet the ON condition and add the unselected records in the right table to them

Code implementation:
select*from aaa right join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;
Copy after login
Result:

Full join
Full outer join is to find two tables A The union of the set B. From the perspective of the Cartesian product, it is to select the records whose ON clause condition is true 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 implement UNION operations on the results of left joins and right joins.

Code implementation:
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
Copy after login
Result:

The above is the detailed content of What is MySQL connection query. For more information, please follow other related articles on the PHP Chinese website!