Mysql join query principle knowledge points

Mysql join query

1. Basic concepts

Connect each row of the two tables horizontally in pairs to obtain the results of all rows.

Assumptions:

Table A has n1 rows and m1 columns;

Table B has n2 rows and m2 columns;

After Table A and Table B are "connected", we will have:

n1*n2 rows;

m1+m2 columns.

2. The result after they are connected is similar to this:

3. Basic form of join query: from table 1 [join method] join table 2 [on join condition] Basic form of join query: from table 1 [join method] join table 2 [on join condition]

1. Classification of connection queries

Cross Connect

In fact, it is the "all data" obtained after connecting two tables according to the basic concept of connection, without any "filtering" result - filtering refers to the connection condition.

That is, a cross join is an unconditional "join all" - also known as a Cartesian product.

Cross joins usually have no practical value because after joining the data, the meaning of each row of data may be "lost".

form:

from table1 [cross] join table2;

or:

from Table1 , Table2 ;

Inner Join

form:

from table1 [inner] join table2 on table1.field1 = table2.field2;

meaning:

Get the data of the rows that meet the set connection conditions (the conditions after on) in the result of a "cross connection";

Cross joins often have "meaningless data", as follows:

2. Look at the results of the inner connection:

3. The result is:

4. It can be seen that inner join is actually to find the "meaningful" data rows in the data results of a cross join. In a cross join, some of the data are meaningful, while some are meaningless (erroneous data).

However, please note:

• 1. This connection condition is not set arbitrarily, but must be set according to the actual relationship between the tables. Usually, the relationship is that the values ​​of the two fields with a "primary and foreign key relationship" between the two tables are equal.
• 2. It can be seen that the connection query has its inherent logical consistency with the "foreign key relationship" we learned before.
• 3. However, when we do an inner join, it does not require that the two tables "must" have a foreign key relationship - we just understand from a practical perspective that they have a foreign key relationship (data relationship), and when using an inner join during querying, their relationship is established. It can be seen that inner join is actually to find the "meaningful" data rows in the data results of a cross join. In a cross join, some of the data are meaningful, while some are meaningless (erroneous data).