Detailed explanation of MySQL combined query

Using UNION

Most SQL queries consist of 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 unions.

There are two situations where combined queries are required:

• Return similarly structured data from different tables in a single table query;
• Execute multiple queries on a single table, returning data as a single query.

Combining queries and multiple WHERE conditions: In most cases, combining two queries on the same table accomplishes the same work as a single query with multiple WHERE clause conditions.

You can use the UNION operator to combine several SQL queries. All you need to do is give each SELECT statement and put the keyword UNION between each statement.

For example:

Suppose we need a list of all items with a price less than or equal to 5, and we also want to include all items produced by suppliers 1001 and 1002. Of course, we can use the WHERE clause to accomplish this, but here we use UNION

• Here is a combination of two statements

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

• You can use the following statement

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

• Similarly, you can use the WHERE clause

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001, 1002);

UNION Rules

• UNION must consist of two or more SELECT statements, separated by the keyword UNION.
• Each query in a UNION must contain the same columns, expressions, or aggregate functions (the columns do not need to be listed in the same order).
• Column data types must be compatible: they do not have to be of the same type, but they must be types that the DBMS can implicitly convert (different numeric types or different date types).

Include or suppress duplicate rows

UNION will automatically remove duplicate rows from the query result set. If you want to return all matching rows, use UNION ALL.

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

Sorting combined query results

The output of the SELECT statement is sorted using the ORDER BY clause. When using UNION to combine queries, only one ORDER BY clause can be used. It must appear after the last SELECT statement. Multiple ORDER BY clauses are not allowed.

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) ORDER BY vend_id, prod_price;

The above is a detailed explanation of MySQL combined query. For more information about MySQL combined query, please pay attention to other related articles on 123WORDPRESS.COM!