What is MySQL connection query

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?

• Natural join (Naturaljoin) is a special equivalent join. It requires that the components to be compared in the two relationships must be the same attribute group, and duplicates are included in the result. The attribute column is removed.

What is an equijoin?

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

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

Result:

Natural join query:

-- 自然连接用关键字 natural join
SELECT*from aaa natural join bbb;

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

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;

The third one: join

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

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;

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;

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;

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

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!