What is the MySQL connection query?

This article mainly shows you "MySQL connection query is what", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "MySQL connection query is what" this article.

Internal connection

Inner connection INNERJOIN is the most commonly used connection operation. From a mathematical point of view, it is to find the intersection of two tables, and from the point of view of Cartesian product, it is to pick out the records of ON clause conditions from Cartesian product.

In my opinion, the inner connection is similar to the equivalent connection, and the natural connection is a special connection in the inner connection.

The difference between natural connection and equivalent connection

What is a natural connection?

Natural join (Naturaljoin) is a special equivalent join, which requires that the components to be compared in two relationships must be the same attribute group, and the duplicate attribute columns must be removed from the result.

What is an equivalent connection?

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

Example analysis:

Table aaa

Table bbb:

Query data with equal attribute C B D in table aaa and table BBB

Equivalent join query:

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

Results:

Natural join query:

-- natural joinSELECT*from aaa natural join bbb keyword for natural connection

Results:

As can be seen from the above two results, an equivalent join is to take the data with the specified value equal from the intersection of the data of two tables. 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.

The implementation of internal connection

The first kind: where

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

The second kind: 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 kind: join

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

The fourth kind: 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 fourth and the second main difference is that the insertion mode is different, and the fourth performance is slightly lower.

External connection

There are three kinds of external connections: left outer connection, right outer connection and full external connection. Corresponding to SQL:LEFT/RIGHT/FULL OUTER JOIN.

Left connection

What is the left connection?

The meaning of the left join LEFT JOIN is to find the intersection of two tables An and B plus the rest of the data in the left table. Still, from the point of view of Cartesian product, we first pick out the records with the condition of ON clause from Cartesian product, and then add the remaining records in Table An on the left.

Code implementation:

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

Results:

Right connection

What is the left connection?

In the same way, the right join RIGHT JOIN is to find the intersection of two tables An and B plus the rest of the data in Table B. Again, from the point of view of Cartesian product, the right join is to pick out the records in which the condition of the ON clause is valid from the Cartesian product, and then add the remaining records in the right table.

Code implementation:

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

Results:

Full connection

A full outer join is to find the union of two sets of tables An and B. From the point of view of Cartesian product, it is to pick out the records with ON clause conditions from 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 UNION operations on the results of left and right connections.

Code implementation:

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

Results:

These are all the contents of the article "what is a MySQL connection query?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!