Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 23 November 2017

Oracle SQL UNION ALL Set Operator


UNION ALL Operator

This will combine the records of multiple tables having the same structure but including duplicates.


Use the UNION ALL operator to return all rows from multiple queries. 

SQL UNION ALL Operator
Guidelines

         Unlike UNION, duplicate rows are not eliminated and the output is not sorted by default.
         The DISTINCT keyword cannot be used.

Note: With the exception of the above, the guidelines for UNION and UNION ALL are the same.

Using the UNION ALL SET Operator 

The UNION ALL operator returns all records along with duplicate records.

Take examples from Student1 and Student2 tables 
 
Select * from Student1;



SrNo
Name
101
Name101
102
Name102
 


Select * from Student2;
 


SrNo
Name
102
Name102
103
Name103
 



Consider the following example:

SQL>SELECT * FROM student1
          UNION ALL
          SELECT * FROM student2 
          ORDER BY SrNo;

Output:



SrNo
Name
101
Name101
102
Name102
102
Nmae102
103
Name103
 






SET Operator Guidelines

         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


You May Like:


Share this article :

0 comments:

Post a Comment