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

Pages

Tuesday, 21 November 2017

Oracle SQL UNION Set Operator

   
  The UNION operator returns all rows selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.

SQL UNION Operator

Guidelines
         The number of columns and the datatypes of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.
         UNION operates over all of the columns being selected.
         NULL values are not ignored during duplicate checking.
         The IN operator has a higher precedence than the UNION operator.
         By default, the output is sorted in ascending order of the first column of the SELECT clause.

Using the UNION SET Operator 

The UNION operator eliminates any duplicate records. If there are records that occur both in the  Student1 and the Studnet2 tables and are identical, the records will be displayed only once.

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
          SELECT * FROM student2;


Output:


SrNo
Name
101
Name101
102
Name102
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