How to combine multiple query results using MySQL's UNION function

How to use MySQL's UNION function to merge multiple query results

MySQL is a commonly used relational database management system that provides rich functions and syntax for data query and operation. Among them, the UNION function is a commonly used method for merging multiple query results, which can effectively merge and integrate data sets. This article will introduce how to use MySQL's UNION function to implement this function and provide corresponding code examples.

1. Basic usage and syntax of UNION function

UNION is a function used to merge multiple query results. The basic syntax is as follows:

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2
...

Where:

• column1, column2, ... are the column names to be selected.
• table1, table2, ... are the names of the tables to be queried.

Note that the UNION function requires that the merged query results must have the same number of columns and similar data types.

2. Use the UNION function to merge multiple query results

The following is a simple example. Suppose there are two tables: table1 and table2.

-- 创建表table1
CREATE TABLE table1 (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 创建表table2
CREATE TABLE table2 (
  id INT PRIMARY KEY,
  salary FLOAT
);

-- 向表table1插入数据
INSERT INTO table1 (id, name) VALUES (1, 'John');
INSERT INTO table1 (id, name) VALUES (2, 'Lisa');

-- 向表table2插入数据
INSERT INTO table2 (id, salary) VALUES (1, 5000);
INSERT INTO table2 (id, salary) VALUES (2, 6000);

Now, we need to merge the query results of these two tables, which can be achieved using the UNION function.

SELECT id, name FROM table1
UNION
SELECT id, CAST(salary AS CHAR) AS name FROM table2;

Through the above code, we can get the following results:

id  | name
-------------
1   | John
2   | Lisa
1   | 5000
2   | 6000

3. Other uses of the UNION function

In addition to the basic usage, the UNION function has some other uses Usage and precautions.

1. UNION ALL: UNION ALL has similar functions to UNION, but it does not perform deduplication operations. If you need to keep duplicate rows, you can use UNION ALL.

SELECT id, name FROM table1
UNION ALL
SELECT id, CAST(salary AS CHAR) AS name FROM table2;

2. ORDER BY clause: You can add an ORDER BY clause after the UNION function to sort the results.

SELECT id, name FROM table1
UNION
SELECT id, CAST(salary AS CHAR) AS name FROM table2
ORDER BY id;

3. Things to note:
4. The UNION function requires that the merged query results must have the same number of columns and similar data types.
5. The UNION function removes duplicates by default. If you need to retain duplicate rows, you can use UNION ALL.
6. The UNION function will automatically sort the query results. If you need to specify the sorting rules, you can use the ORDER BY clause.

4. Summary

MySQL's UNION function is a convenient method for merging multiple query results. Through the UNION function, we can flexibly integrate and merge data sets. This article introduces the basic usage and syntax of the UNION function and provides corresponding code examples. I hope that through the introduction of this article, readers can become more proficient in using the UNION function for data query and operation.

The above is the detailed content of How to combine multiple query results using MySQL's UNION function. For more information, please follow other related articles on the PHP Chinese website!