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

Pages

Sunday, 26 November 2017

Oracle SQL MINUS Set Operator

0 comments

MINUS Operator 

Use the MINUS operator to return rows returned by the first query that are not present in the second query (the first SELECT statement MINUS the second SELECT statement).



Guidelines

         The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.
         All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work.


Using the MINUS Operator 

The MINUS operator returns rows returned by the first query from student1.

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



Output:



SrNo
Name
101
Name101
102
Name102
  






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:


Continue reading >>

Thursday, 23 November 2017

Oracle SQL INTERSECT Set Operator

0 comments

INTERSECT Operator

Use the INTERSECT operator to return all rows common to multiple queries.


SQL INTERSECT Operator
Guidelines
         The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.
         Reversing the order of the intersected tables does not alter the result.
         INTERSECT does not ignore NULL values.
 

Using the INTERSECT Operator 

The INTERSECT operator returns common records from student tables srno102 record. 

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


SrNo
Name
102
Name102



 
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:


Continue reading >>

Oracle SQL UNION ALL Set Operator

0 comments

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:


Continue reading >>

Tuesday, 21 November 2017

Oracle SQL UNION Set Operator

0 comments
   
  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:

 



Continue reading >>