Mysql --- multi-Table Association

First, we need to introduce the concept of a set: the set has no sequence and uniqueness.

Sequent:Elements in a set do not have relative sequence. For two sets, the two sets are equal as long as the element values and number of elements are the same.

Uniqueness:The element in the set does not have the same value.

The set shown is incorrect because two '3' entries violate the uniqueness.

The two sets are the same, and the Set elements are unordered. <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> Signature + vK + signature + CzyzwvcD4KPHA + signature + CjxwPrKivK + jusGsuPa8r7rPy/Signature/qLb7u/Signature = "http://www.2cto.com/uploadfile/Collfiles/20140617/2014061708594924.jpg" alt = "\">

The result of Set 1 * Set 2 is as follows:

(1, 3) indicates the combination of '1' in Set 1 and '3' in Set 2.

A table is equivalent to a set. Each row is an element of the set.

There are now two tables

Goods table:Contains item ID, column ID, product name, and 8 items

Channel table:Topic ID, topic name, 3 columns

What should I do if I want to get a quote that contains the product ID, product name, topic ID, and topic name?

Multiply all:Cartesian products have a total of 3x8 = 24 records

That is, all the combinations of rows in the two tables

Select the rows that meet the requirements.

Although the correct answer is obtained, if the goods table and channel table are large, for example, the goods table has 1 million records and the channel has 1 million records. We need to generate a * temporary table in the memory, and most records are not what we need. Therefore, full multiplication wastes space and reduces efficiency.

Left join: syntax Table 1 left join table 2 on condition

Select rows in table 2 that meet the conditions based on table 1.

Right join: syntax Table 2 right join table 1 on condition is equivalent to Table 1 left join table 2 on condition

Therefore, the left and right connections are usually interchangeable, but the right connections have some anti-logic thinking. We recommend that you use the left connection.

Inner join: syntax Table 1 inner join table 2 on condition

It can be seen that the required functions can be completed for left-side join, inner join, and full multiplication, but the efficiency of full multiplication is the lowest, because full multiplication generates a large temporary table, there is no index.

Taking the left join as an example, "A left join B on condition" is actually equivalent to A temporary table, which contains all the fields of table A and table B and all the rows of Table B that meet the condition. In addition, clauses such as where can be followed.

Multi-table join query:

Join Table A, B, C, and D

A left join B on condition 1 left join C on condition 2 left join D on Condition 3

In the next blog, I will introduce in detail the differences and connections between left and right connections and internal connections.