MySQL---Multi-table Association

First, introduce the concept of a set: The set is unordered and unique.

disorder: refers to the concept that the inner elements of a set have no relative order, and for two sets, two sets are equal as long as the element value and the number of elements are the same.

Uniqueness: refers to elements within the set that do not have equal values.

The set shown is wrong because there are 2 ' 3 ' breaches of uniqueness

The two collections shown are the same, and the collection elements are unordered

operation of the set: intersection, set, multiply

Intersection: Two sets of common elements of a collection

Set: A collection of all elements of a collection

Multiply: Also into a Cartesian product, two sets of all element combinations

The result of collection 1* Collection 2 is as follows:

where (1,3) is a combination of ' 1 ' in collection 1 and ' 3 ' in set 2

A table is actually a collection, and each row is an element of the collection

Now there are two tables

Goods table: contains product ID, column ID, product name, 8 items

Channel table: column ID, column name, there are 3 columns

Want to get a quote containing the product ID, product name, column ID, column name, how to do?

full multiplication: Cartesian product has a total of 3*8=24 records

That is, all combinations of rows of two tables

Select the line where the requirement is met

Although the correct answer, but if the goods table and the channel table is large, such as the goods table has 1W records, the channel also has 1W records. You need to generate a temporary table of 1w*1w in memory, and most of the records are not what we need. So the total multiplication wasted space and reduced efficiency.

Left JOIN: Syntax table 1 LEFT JOIN table 2 on condition

Table 2 rows that meet the criteria are selected in table 1 as a benchmark

Right connection: Syntax Table 2 R join table 1 on condition equivalent to table 1 left JOIN table 2 on condition

So the connection is often interchangeable, but the right connection is a bit counter-logical, it is recommended to choose left connection.

Inner joins: Syntax table 1 inner JOIN table 2 on condition

Visible left and right connections, internal connections, full multiplication can complete the required functions, but the full multiplication of the efficiency is the lowest.

In the next blog post, the differences and connections between the left and right connections and the inner joins are described in detail.