Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to query mysql multi-table, and what it involves is learned from the theoretical knowledge, there are many books and documents for your reference, from the perspective of practical significance, the accumulated practical experience for many years can be shared with you.

1. Union

"and" is to combine tables with the same number of fields and field types.

2. Cartesian product

The Cartesian product is the result of the return of a table with no join conditions.

3. Internal connection (inner join)

A special function JOIN is provided for the operation of database operations. Join is to generate a new relationship according to the comparison conditions of the corresponding field values in the Cartesian product data record of the table relationship. The connection is divided into inner connection (INNER JOIN), outer connection (OUTER JOIN) and cross connection (CROSS JOIN).

Inner join is to keep all matching data records in the table relationship and discard mismatched data records. According to the matching conditions, it can be divided into natural connection, equivalent connection and unequal connection.

3.1 Natural connection (NATURAL JOIN)

A natural join is a Cartesian product of a table relationship, which first automatically matches records according to fields of the same name in the table relationship, and then removes duplicate fields.

Its characteristics are as follows:

@ will automatically judge the fields with the same name, then match the data values and remove the unequal records of the same fields.

@ in a new relationship that performs a natural connection, although you can specify which fields are included, you cannot specify matching conditions during execution

@ in a new relationship after the execution of the natural condition, only one field name is matched, that is, the duplicate field is removed.

3.2 equivalent connection

The so-called equivalent join is that in the Cartesian product of the table relation, the data record whose matched field value is equal (= symbol) is selected. Compared with the natural join, the equivalent join relation data operation needs to specify the matching condition with the symbol = in the execution process, and the duplicate fields will not be removed from the new relationship.

3.3 unequal connection

An unequal join is a data record in the Cartesian product of a table relationship in which the matched field values are not equal (! = symbol).

4. External connection (OUTER JOIN)

The outer join is the Cartesian product data record of the table relationship, which not only retains all the matching data records in the table relationship, but also retains some mismatched data records. According to the data record source of retention mismatch condition, it can be divided into left outer connection (LEFT OUTER JOIN), right outer connection (RIGHT OUTER JOIN) and full external connection (FULL OUTER JOIN).

4.1 left external connection

The left outer join operation is the Cartesian product of the table relationship, which not only selects the matching data records, but also contains the mismatched data records in the left table.

4.2 right external connection

The right outer join is the Cartesian product of the table relationship, which not only selects the matching data records, but also contains the mismatched data records in the right table.

4.3 full external connection

The full outer join is the Cartesian product of the table relationship, which not only selects the matching data records, but also contains the mismatched data records in the left and right tables.

After reading the introduction of how to query the mysql multi-table above, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.