mysql table merge query results

In mysql, the storage format of data is usually managed in separate tables. Although this method can improve the efficiency of data query, it also brings certain management difficulties. In practical applications, it is usually necessary to perform query operations on multiple sub-tables. In this case, the merged table query function of MySQL needs to be used.

Merge table query refers to merging data from multiple tables into a result set according to certain rules. In mysql, table merge queries can be implemented by using syntax such as union, union all, and join. The following will introduce the usage and query effects of these syntaxes respectively.

1. union syntax

Union syntax can combine multiple query results into one result set and return it. It should be noted that the union operation will automatically remove duplicates. If you need to return all data, you need to use the union all operation.

The syntax format is as follows:

select 列名 from 表1 union select 列名 from 表2
select 列名 from 表1 union all select 列名 from 表2

For example, suppose we have two sub-tables users1 and users2. They have the same structure and both have id and name columns. We can use the following sql query statement Merge them into a result set:

select id,name from users1 union all select id,name from users2;

2. join syntax

join syntax can join multiple tables by specifying join conditions and return records that meet the conditions.

The syntax format is as follows:

select 表1.列1,表2.列1 from 表1 join 表2 on 表1.列1=表2.列1

For example, suppose we have two sub-tables users1 and users2. Both tables have a field related to the city. We can use the following sql query statement to They are connected:

select users1.id,users1.name,users2.city from users1 join users2 on users1.city=users2.city;

3. Subquery

Subquery refers to the nesting of another select statement in a select statement, which can further filter the query results. .

For example, suppose we have a sub-table orders, which contains the product ID and transaction amount. We want to query the total transaction amount and average transaction amount of each item. We can use the following SQL query statement:

select id,sum(amount) as total,avg(amount) as average from (select id,amount from orders) as temp group by id;

The above are the usage and effects of the three syntaxes of mysql table query. In actual applications, you can choose the corresponding method according to your needs for data query and statistical work.

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