mysql combined query: What is a combined query? How to create a combined query

What is a combined query

Most SQL queries contain only a single SELECT statement that returns data from one or more tables. MySQL also allows you to execute multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are often called union or compound queries.

There are two basic situations in which combined queries need to be used:

1. Returning similarly structured data from different tables in a single query;

2. For a single The table executes multiple queries and returns data as a single query.

Combining queries and multiple WHERE conditions In most cases, combining two queries on the same table accomplishes the same job as a single query with multiple WHERE clause conditions. In other words, any SELECT statement with multiple WHERE clauses can be given as a combined query, as you can see in the following paragraphs. Both techniques perform differently on different queries. Therefore, you should try both techniques to determine which performs better for a specific query.

How to create a combined query

The UNION operator can be used to combine several SQL queries. Using UNION, you can give multiple SELECT statements and combine their results into a single result set.

Using UNION

Using UNION is very simple. All that is required is to give each SELECT statement and put the keyword UNION between each statement.

As an example, suppose you need a list of all items with a price less than or equal to 5, and you also want to include all items produced by suppliers 1001 and 1002 (regardless of price). Of course, this can be done with a WHERE clause, but this time we'll use UNION .

As stated, creating a UNION involves writing multiple SELECT statements. Let’s look at the single statement first:

Input:

select vend_id,prod_id,prod_price from products where prod_price <= 5;

Output:

##Input:

select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);

Output:

Analysis: The first SELECT retrieves all items with a price not higher than 5. The second SELECT uses IN to find all items produced by suppliers 1001 and 1002.

To combine these two statements, proceed as follows:

Input:

select vend_id,prod_id,prod_price from products where prod_price <= 5 union select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);

Output:

Analysis : This statement consists of the previous two SELECT statements, separated by the UNION keyword. UNION instructs MySQL to execute two SELECT statements and combine the output into a single query result set.

For reference, here is the same query using multiple WHERE clauses instead of UNION:

Input:

select vend_id,prod_id,prod_price from products where prod_price <= 5 or vend_id in (1001,1002);

In this simple example, use UNION May be more complex than using a WHERE clause. But for more complex filters, or when data is retrieved from multiple tables instead of a single table, using UNION may make the process simpler.

The above is the detailed content of mysql combined query: What is a combined query? How to create a combined query. For more information, please follow other related articles on the PHP Chinese website!