How to Combine Two SQL Queries in One Result

How do I merge or combine two SQL queries in one result? See the following parts of this article to learn the details.

Most in common, UNION and UNION ALL are used to combine two SQL queries in one result. These two methods allow you to easily combine two SQL queries, but what is the difference? The difference between them is that UNION ALL acceptes duplicate valuses, but UNOIN does not. That is to say, you should use UNION ALL to merge two SQL queries in one result if you allow duplicate values in your result.

Let's see examples below. In our examples, two tables in our database are used, namely "teachers" and "professors". Both of the tables contain information columns, including id, name, gender, and age.

id	name	gender	age
1	Paul	Male	31
2	Alan	Female	29
3	Jason	Male	45
4	Linda	Female	38

id	name	gender	age
1	Sarah	Female	35
2	Karl	Male	34
3	David	Male	45
4	Alan	Female	29

Combine Two SQL Queries Without Duplication

Now, we will combine these two tables into one result. But, as you can see, Alan is duplicated in the two tables. To combine these two SQL queries in one result without duplication, UNION should be used.

SELECT name, gender, age FROM teachers
UNION
SELECT name, gender, age FROM professors;

Result

name	gender	age
Paul	Male	31
Alan	Female	29
Jason	Male	45
Linda	Female	38
Sarah	Female	35
Karl	Male	34
David	Male	45

Combine Two SQL Queries with Duplication

If you want to combine two SQL queries and remain the duplicated Alan values, you should use UNION ALL. See the example and result below.

SELECT name, gender, age FROM teachers
UNION ALL
SELECT name, gender, age FROM professors;

Result

name	gender	age
Paul	Male	31
Alan	Female	29
Jason	Male	45
Linda	Female	38
Sarah	Female	35
Karl	Male	34
David	Male	45
Alan	Female	29

Combine Two SQL Queries In Another Way

In addition to above two methods of combining two SQL queries in one result, you can also try with a a temporary table.

CREATE TEMPORARY TABLE t AS SELECT a,b,c from table1 ...
INSERT INTO t SELECT a,b,c from table2
SELECT * from t;
DROP TEMPORARY TABLE t;

CREATE TEMPORARY TABLE t (PRIMARY KEY (a,b,c)) AS SELECT a,b,c from table1 ...
INSERT IGNORE INTO t select a,b,c from table2 ...
SELECT * from t;
DROP TEMPORARY TABLE t;