Objective: To learn how to use the Union operator to combine multiple select statements into a result set. Combined Query: Most SQL queries only contain single select statements that return data from one or more tables. MySQL can also execute multiple queries (multiple select statements) and return the results as a single query result set. These combined queries are generally called union or compound queries ). There are two basic cases:
- Returns data of similar structures from different tables in a single query;
- Perform multiple queries on a single table and return data based on a single query.
Combined Query and multiple where conditions: In most cases, the work of combining two queries of the same table is the same as that of a single query with multiple WHERE clause conditions. In other words, any SELECT statement with multiple where clauses can be provided as a combined query, which can be seen in the following section. These two technologies have different performance in Different queries. Therefore, try these two technologies to determine which of the following queries has better performance. Create a Combined Query: You can use the Union operator to combine several SQL queries. Using union, multiple select statements can be provided to combine the results into a single result set. The use of Union: Union is very simple. All you need to do is to give each select statement and put the keyword union between each clause. Q: A list of all items whose prices are less than or equal to 5 is required, and you want to include all items produced by suppliers 10001 and 10002 (regardless of the price) Select vend_id, prod_id, prod_namefrom productswhere prod_price <= 5 unionselect vend_id, prod_id, prod_pricefrom productswhere vend_id in (1001,1002); ### solutions using multiple where clauses: (0.5 s) Select vend_id, prod_id, prod_namefrom productswhere prod_price <= 5 or vend_id in (1001,1002); Union rules: As you can see, they are very easy to use, but several rules need to be noted during the process.
- Union must be composed of two or more select statements, which are separated by the keyword union.
- Each query in union must contain the same column, expression, or clustering function (however, each column does not need to be listed in the same order)
- Column data types must be compatible: The types must not be identical, but must be implicitly converted by DBMS.
Rows that contain or cancel duplicates: Union automatically removes duplicate rows from the query result set (in other words, it acts the same way as using multiple WHERE clause conditions in a single SELECT statement ). If you want to return all matched rows, use Union all. Select vend_id, prod_id, prod_namefrom productswhere prod_price <= 5 Union allselect vend_id, prod_id, prod_pricefrom productswhere vend_id in (1001,1002); Union and where: As mentioned at the beginning of this chapter, union almost always completes the same work as multiple where conditions. Union all is a form of union, which can't be done by the WHERE clause. If you need to match all rows of each condition (including duplicate rows), you must use Union all instead of where. Sort the combined query results: Only one order by clause can be used for Union combination query. It must appear after the last SELECT statement. Multiple order by statements are not allowed because the result set does not sort parts in one way and the other is sorted in another way. Select vend_id, prod_id, prod_namefrom productswhere prod_price <= 5 unionselect vend_id, prod_id, prod_pricefrom productswhere vend_id in (1001,1002) order by vend_id, prod_price; tables with different combinations: you can apply different tables to Union queries.
Chapter 4 Combined Query
