What is the multi-table joint query statement in mysql

The multi-table joint query statement in mysql is: [select statement 1 union [union option] select statement 2 union [union option] select statement n]. The result of multi-table joint query is to combine the query results of multiple select statements.

【Related learning recommendation: mysql tutorial(video)】

Mysql multi-table joint query statement is:

The joint query result is to combine the query results of multiple select statements together.

You can use the union and union all keywords to merge.

Basic syntax:

select statement 1

union [union option]

select statement 2

union [union option]

select statement n

The union option has two options: all (repeat and output); distinct (deduplication , completely repeated, will be deduplicated by default)

The fields of the two tables should be the same.

Example:
 select id,addrid
 from addr
 union all
 select id,addrid
 from student

The meaning of joint query

1. Query the same table, but the requirements are different

2. Multi-table query: The structure of multiple tables is exactly the same, and the saved data (structure) is also the same

Use of joint query order by

In joint query: order by can only use one last, Parentheses are required for query statements.

Example:
 ---(mistake)
 select * from student where sex="man" order by score
 union
 select * from student wherre sex="woman" order by score;
 In this case, an error will be reported, because there cannot be two order by in a sentence
 --- (Correct but not as desired)
 select * from student where sex="man"
 union
 select * from student wherre sex="woman" order by score;
 This situation is correct, but merging is meaningless, he will disrupt the previous sex division
 ---(correct)
 (select * from student where sex="man" order by score
 limit 10)
 union
 (select * from student wherre sex="woman" order by score
 limit 10);
 When using order by in a substatement, due to priority issues, the entire clause needs to be enclosed in () and must be used in conjunction with limit, otherwise it will not take effect.  

For more programming learning, please pay attention to the php training column!

The above is the detailed content of the multi-table joint query statement in mysql. For more information, please pay attention to other related articles on 1024programmer.com!