For an instructor lead, in-depth look at learning SQL click below.
Welcome to this beginner’s guide for using the UNION operator in SQL! UNION is a very useful SQL operand that lets you combine the result sets of two or more SQL queries.
What is UNION?
UNION is a SQL command that combines the results of two SELECT queries into a single result set. The two SELECT queries that UNION combines must return the same number of columns, and corresponding columns must be of compatible data types.
Basic Syntax
|
1 2 3 4 5 |
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; |
How does UNION Work?
UNION eliminates duplicate rows from the results. If you have duplicate rows in your result set and you want to keep them, you should use UNION ALL instead.
UNION vs UNION ALL
The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table of all results.
|
1 2 3 4 5 |
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2; |
Example: Using UNION
Let’s see how UNION works through an example. Assuming we have two tables: Students and Teachers. Both the tables have similar columns: name, age, and city.
|
1 2 3 4 5 |
SELECT name, age, city FROM Students UNION SELECT name, age, city FROM Teachers; |
This query will return a list of all the names, ages, and cities from both the Students and Teachers tables, but it will eliminate any duplicates.
Example: Using UNION ALL
If we want to keep the duplicates, we should use UNION ALL.
|
1 2 3 4 5 |
SELECT name, age, city FROM Students UNION ALL SELECT name, age, city FROM Teachers; |
This query will return a list of all the names, ages, and cities from both the Students and Teachers tables, with any duplicates included.
Concluding Thoughts
As you can see, UNION and UNION ALL are very powerful tools in SQL. They allow you to combine data from multiple tables, which is essential for creating complex reports and performing advanced analytics. Remember that practicing is the key to mastering these commands, so keep experimenting with different queries!