Home > Database > Mysql Tutorial > mysql joint query example sharing

mysql joint query example sharing

小云云
Release: 2018-03-15 10:41:21
Original
1744 people have browsed it

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.

mysql joint query example sharing

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:

select 语句1
union 【all | distinct】
select 语句2;
Copy after login
Copy after login

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;

mysql joint query example sharing
Comparison:
mysql joint query example sharing

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;
Copy after login

Example:
mysql joint query example sharing

Implementing "full outer connection":

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

Example:
mysql joint query example sharing
The result is:
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.

mysql joint query example sharing

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:

select 语句1
union 【all | distinct】
select 语句2;
Copy after login
Copy after login

Note:

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

  • 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;

mysql joint query example sharing
Comparison:
mysql joint query example sharing

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;
Copy after login

Example:
mysql joint query example sharing

##Realize "full outer connection":

select  * f rom  表1  left  join 表2 on  条件unionselect  * f rom  表1  right  join 表2  on  条件
Copy after login
Example:


mysql joint query example sharingThe result is:

mysql joint query example sharing

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template