mysql joint query example sharing

Basic concept: Combine the results of two query statements with the same number of fields into one query result in a "stacked top and bottom" manner. This article mainly shares with you mysql joint query examples, hoping to help everyone.

Visible:

• The "number of fields" in the query results of the two select statements must be consistent;

• Usually, the field types of the two query statements should be consistent;

• You can also combine more query results;

Grammar form:

ｓｅｌｅｃｔ　语句１
ｕｎｉｏｎ　【ａｌｌ　｜　ｄｉｓｔｉｎｃｔ】
ｓｅｌｅｃｔ　语句２；

Note:

• This joint query statement will "automatically eliminate duplicate rows" by default, that is, the default is distinct

• If you want to display all data (allowing duplicate rows), use all

Details:

should The result of this joint query is understood to be ultimately a "table data", and the field name in the first select statement is used by default;

Comparison:

By default, the order by clause and the limit clause can only sort and limit the number of results after the entire union:

select... union select... order by XXX limit m,n;

Example:

Implementing "full outer connection":

select  * f rom  表1  left  join 表2 on  条件unionselect  * f rom  表1  right  join 表2  on  条件

Example:

The result is:

Basic concept: Combine the results of two query statements with the same number of fields into one query result in a "stacked top and bottom" manner.

Visible:

• The "number of fields" of the query results of the two select statements must be consistent;

• Usually, the field types of the two query statements should be consistent;

• You can also combine more query results;

Grammar form:

ｓｅｌｅｃｔ　语句１
ｕｎｉｏｎ　【ａｌｌ　｜　ｄｉｓｔｉｎｃｔ】
ｓｅｌｅｃｔ　语句２；

Note:

• This joint query statement will "automatically eliminate duplicate rows" by default, that is, the default is ｄｉｓｔｎｃｔ

• If you want to display all data (allowing duplicate rows), use all

Details:

The result of this joint query should be understood as ultimately a "table data", and the field name in the first select statement is used by default;

Comparison:

By default, the order by clause and the limit clause can only sort and limit the number of results after the entire union:

select... union select... order by XXX limit m,n;

Example:

##Realize "full outer connection":

select  * f rom  表1  left  join 表2 on  条件unionselect  * f rom  表1  right  join 表2  on  条件

Example:

The result is:

Related recommendations:

Analysis and optimization of Mysql multi-table joint query efficiency

An example of joint query on the database

MySQL multi-table joint query instructions

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