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 the first name is Clark and the 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

  1. The expressions in the SELECT lists must match in number and data type.
  2. Parentheses can be used to alter the sequence of execution.
  3. 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
  4. Duplicate rows are automatically eliminated except in UNION ALL.
  5. Column names from the first query appear in the result.
  6. 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!