Detailed explanation of join query in Mysql

Detailed explanation of join query in Mysql

##1. Basic concepts

The results of all rows obtained by "joining each row horizontally" of the two tables.

Assumption:

Table A has n1 rows and m1 columns;

Table B has n2 rows and m2 columns;

Then table A and table B After "docking", there will be:

n1*n2 rows;

m1+m2 columns.

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

3 Basic form of connection query:

from Table 1 [Connection method] join Table 2 [on connection conditions]

##Classification of connection queries

1. Cross connection

In fact, it is "all data" obtained after connecting two tables according to the basic concept of connection, without any "filtering" results. ——Filtering refers to connection conditions.

That is: the cross connection is the "all connection" without conditions - it is called the Cartesian product.

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

Form:

from Table 1 [cross] join Table 2;

or:

from Table 1, Table 2;

Inner join

Form:

from Table 1 [inner] join Table 2 on Table 1. Field 1 = Table 2. Field 2;

Meaning:

Obtain the data of those rows in the result of a "cross connection" that meet the set connection conditions (that is, the conditions after on);

Cross connections often have "meaningless data", As follows:

2. Look at the result of the inner connection:

3. The result is:

##4. Visible:

Inner join is actually to find the "meaningful" data rows in the data result of a cross connection.

As for a cross connection, some of the data in it are meaningful, and some are not meaningful (error 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 between two tables. The values ​​of the two fields with a "primary and foreign key relationship" 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 inner joins, we do not require that the two tables "must" have foreign key relationships - we just need to understand from a practical perspective that they have foreign key relationships (data relationships). , and their relationship is established using inner joins when querying.

The above is the detailed content of Detailed explanation of join query in Mysql. For more information, please follow other related articles on the PHP Chinese website!