How to combine the results of two queries in SQL

Problem

You have two SQL queries and need to combine their results into a single result set.

Sample data

Let's assume you have two tables, Products and Orders.

Products table:

Orders table:

You want to combine the results of these two queries: 1. Get all products with a price higher than $1.00. 2. Get all orders with a quantity greater than 5.

MySQL Solution

(SELECT ProductName AS Name, Price AS Value FROM Products WHERE Price > 1.00)
UNION ALL
(SELECT OrderID AS Name, Quantity AS Value FROM Orders WHERE Quantity > 5);

PostgreSQL Solution

(SELECT ProductName AS Name, Price AS Value FROM Products WHERE Price > 1.00)
UNION ALL
(SELECT OrderID::text AS Name, Quantity AS Value FROM Orders WHERE Quantity > 5);

Explanation

In both MySQL and PostgreSQL, the UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).

Each SELECT statement within the UNION ALL must have the same number of columns. The columns must also have similar data types. Moreover, the columns in each SELECT statement must also be in the same order.

In the provided solutions for both MySQL and PostgreSQL:

1. The first query selects ProductName and Price from the Products table where the Price is greater than 1.00.
2. The second query selects OrderID and Quantity from the Orders table where Quantity is greater than 5.
3. The UNION ALL combines these two result sets into a single result set.

The only difference between the MySQL and PostgreSQL solutions is that in PostgreSQL, we need to explicitly convert OrderID to text (::text) to match the data type of ProductName in the first query. In MySQL, this conversion is implicitly done by the system.