Detailed explanation of mysql connection query example

Connection query is to "connect" two or more tables as a data source, and obtain the required data from them; this article mainly shares with you the detailed explanation of mysql connection query examples, hoping to help Everyone.

Cross join cross join:

No conditions, just the result of connecting all data rows according to the basic concept of join. It is also called "Cartesian product";

For table 1 (n1 fields, n2 rows), table 2, (m1 fields, m2 rows), the result of their cross connection is:

• has n1+m1 columns;

• has n2*m2 rows;

form There are:

• select * from Table 1, Table 2;

• ##select * from Table 1 join Table 2;

• select * from Table 1 cross join Table 2;

Example:

Result:

Inner join inner join:

Form:

select * from Table 1 [inner] join Table 2 on connection condition;

Example:

Result:

Note: Although this kind of inner join query between tables can be It is reflected as the "relationship" between tables - usually a foreign key relationship - but this connection does not need to be used if there is a foreign key relationship.

You can use as to set an alias for it:

left (outer) join:

Form:

表1（左表）  left  【outer】 join  表2（右表）  on  连接条件

Meaning: In fact, it is the result of the inner join of the two tables, plus the results of the data in the left table that does not meet the conditions set by the inner join;

Example:

Result:

It can be seen that the result of the left join and the data in the left table will be "all taken out";

right (outer) join:

Form:

表1（左表）  right  【outer】 join  表2（右表）  on  连接条件

Meaning: In fact, it is the result of inner join of two tables , plus the results of those data in the right table that do not meet the conditions set by the inner join;

Example:

Result:

Connection query

is to "connect" two or more tables as a data source and obtain the required data from them;

Cross join cross join:

No conditions, just the result of connecting all data rows according to the basic concept of join. It is also called "Cartesian product";

For table 1 (n1 fields, n2 rows), table 2, (m1 fields, m2 rows), the result of their cross connection is:

• has n1+m1 columns;

• has n2*m2 rows;

form There are:

• select * from Table 1, Table 2;

##select * from Table 1 join Table 2;
select * from Table 1 cross join Table 2;
Example:

Result:

Inner join inner join:

Form:

select * from Table 1 [inner] join Table 2 on connection condition;

Example:

Result:


Note: This kind of inner join query between tables, although It can be reflected as a "relationship" between tables - usually a foreign key relationship - but this connection does not require a foreign key relationship to be used.

You can use as to set an alias for it:


left (outer) join:

Form:

表1（左表）  left  【outer】 join  表2（右表）  on  连接条件

Meaning: In fact, it is the result of the inner join of the two tables, plus the results of the data in the left table that does not meet the conditions set by the inner join;

Example:

Result:


It can be seen that the result of the left join and the data in the left table will be "all taken out";

right (outer) join:

Form:

表1（左表）  right  【outer】 join  表2（右表）  on  连接条件

Meaning: In fact, it is the result of inner join of two tables , plus the results of those data in the right table that do not meet the conditions set by the inner join;

Example:

Result:

Related recommendations:

mysql connection query left connection, right connection, inner connection example detailed explanation

The importance of index in MySQL connection query Sex

Classic mysql connection query example_MySQL

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