Union & Union All

By Manish Sharma

Union and Union All Set Operators

Set operators allow us to combine rows returned from two or more queries. In SQL we have four set operators.

  1. Union
  2. Union All
  3. Intersect
  4. Minus

In this tutorial we will learn the concept of Union and Union All set operator. For the demonstration purposes I have created and populated two tables Cricket and Football. Both of these have two columns first name and last name with the same data type which is Varchar2. I have also inserted some rows into the tables, here are the screenshots of the data in these tables.

Both these tables have 3 rows of data and if you will look carefully you will notice that there is a duplicate row in both of them. This is row 1 where first name is Clark and last name is Kent.

Well now that we have terminologies straightened out let’s come to the topic and see what are Union and Union All Set Operators in Oracle SQL.

Union Set Operator

The UNION set operator returns results from all the participating queries after eliminating duplications. Let’s see the example

 SELECT first_name, last_name FROM cricket
 SELECT first_name, last_name FROM football;

Here we have 2 separate queries. In the first query we are selecting rows from first name and last name column of Cricket table and in the second query we are retrieving rows from first and last name column of Football table and both these queries are bound using union set operator.

On executing this query all the rows of both the columns of both the tables will be returned except the duplicate one.

Union All Set Operator

The UNION ALL operator returns results from all the participating queries, including the duplicate rows. This means that if we will execute the same query then we will get all the rows returned from both the tables along with the duplicate one.

Let me demonstrate this to you. For that I will copy this query and modify it accordingly.

 SELECT first_name, last_name FROM cricket
 SELECT first_name, last_name FROM football;

Restrictions of Set Operators in Oracle Database

  • The expressions in the SELECT lists must match in number and data type.

  • Parentheses can be used to alter the sequence of execution.

  • The ORDER BY clause:

    • Can appear only at the very end of the statement

    • Will accept the column name, aliases from the first SELECT statement, or the positional notation

  • Duplicate rows are automatically eliminated except in UNION ALL.

  • Column names from the first query appear in the result.

  • The output is sorted in ascending order by default except in UNION ALL.

That’s all about Union and Union All set operators. Hope this article was helpful. Kindly please share it on your social media and help me reach out to more people. Thanks & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link
https://copy.com?r=j7eYO7 You will get 5GB extra free cloud storage means total 20GB free cloud storage.

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn