Set Operator Types
Take examples from Student1 and Student2 tables
Select * from Student1;
Union
|
This
will combine the records of multiple tables having the same structure with out duplicates
|
Union
all
|
This
will combine the records of multiple tables having the same structure but
including duplicates.
|
Intersect
|
This
will give the common records of multiple tables having the same structure.
|
Minus
|
This
will give the records of a table whose records are not in other tables having
the same structure.
|
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
|
UNION 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.
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.
Consider the following
example:
SQL>SELECT * FROM student1
UNION
SELECT * FROM student2;
Output:
SrNo
|
Name
|
101
|
Name101
|
102
|
Name102
|
103
|
Name103
|
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.
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.
Consider the following
example:
SQL>SELECT * FROM student1
UNION ALL
SELECT * FROM student2;
Output:
SrNo
|
Name
|
101
|
Name101
|
102
|
Name102
|
102
|
Nmae102
|
103
|
Name103
|
INTERSECT Operator
Use the INTERSECT operator to return all rows common to multiple queries.
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.
Consider the following
example:
SQL>SELECT * FROM student1
INTERSECT
SELECT * FROM student2;
Output:
SrNo
|
Name
|
102
|
Name102
|
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.
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
Order by in set Operators Example:
UNION
SELECT * FROM student2
ORDER BY SrNo;
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDeleteUsually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Oracle SOA Online Training
ReplyDelete