SQL UNION Operator

SQL UNION Operator

This is a detailed tutorial of SQL UNION Operator. Learn to combine the result set of multiple SELECT Statements into one using the UNION Operator.

Use of SQL UNION Operator

To combine the result set of multiple SQL SELECT Statements, the UNION operator is being used. But each of the SELECT Statements for which the result sets has to be combined must follow a few things. Each select statement should have the same number of columns, the same order of columns and also the columns need to have the same data type.

The UNION operator is further categorized as the simple UNION and UNION ALL operator. UNION operator will only select the distinct values among the multiple tables while UNION ALL will select all of the values from the multiple tables keeping the redundancy of the records. So, if you wanted to allow the duplicate values from the multiple tables to be there in the result set, you should use UNION ALL instead of the UNION operator.

UNION and UNION ALL Syntax

The following query will fetch records of the specified columns from table_1 and table_2 and then will combine the result maintaining the overall records distinct and therefore avoiding duplicate entries.

The following query is almost the same but just the difference is the addition of the word, ALL with UNION. Now, this query will fetch all the records for the specified columns from table_1 and table_2 such that the records in the result set will not be distinct and may contain duplicate values.

Examples

For running the example queries, I’m using two demo database tables named students and employees. The screenshot of each of these is given below.

Demo Database Table Students
Database Table students
Demo Database Table Employees
Database Table employees

Note. Carefully observe the data of the above two tables, you can see in the table employees there are two duplicate entries and the column first_name value Parminder also is there in the student_first_name column value of the students table. Now observe the result sets of the UNION and UNION ALL queries to understand the difference.

The following query will select the columns first_name & last_name from employees table and student_first_namestudent_last_name from the students table.

SQL UNION Operator Example

Notice carefully, the UNION operator only eliminates those rows for which the entire row is completely duplicate. Even if one column value is different, it will be there in the result set, assuming the record to be distinct. As you can see in the result set, the duplicate values for the column first_name are completely distinct while the last_name has duplicate values.

Now the following query of UNION ALL will keep all duplicate values. The SELECT Statements remain the same for this query as well.

SQL UNION ALL Example

Note. Apart from the plain SELECT Statements, you can also add clauses like WHERE and other operators like LIKE, AND, OR, NOT as well. Just make sure the three conditions to apply the UNION operator are satisfied that the number, order and data types of the columns should be the same.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *