Q 23. What are Union, minus, and Interact commands ?
Ans : Set Operations in SQL eliminate duplicate tuples and can be applied only to the relations which are union compatible. Set Operations available in SQL are :-
➢ Set Union
➢ Set Intersection
➢ Set Difference
UNION Operation: This operation includes all the tuples which are present in either of the relations.
For example: To find all the customers who have a loan or an account or both in a bank.
| SELECT CustomerName FROM Depositor UNION SELECT CustomerName FROM Borrower ; |
The union operation automatically eliminates duplicates. If all the duplicates are supposed to be retained, UNION ALL is used in place of UNION.
INTERSECT Operation: This operation includes the tuples which are present in both of the relations.
For example: To find the customers who have a loan as well as an account in the bank:
| SELECT CustomerName FROM Depositor INTERSECT SELECT CustomerName FROM Borrower ; |
The Intersect operation automatically eliminates duplicates. If all the duplicates are supposed to be retained, INTERSECT ALL is used in place of INTERSECT.
EXCEPT for Operation: This operation includes tuples that are present in one relationship but should not be present in another relationship.
For example: To find customers who have an account but no loan at the bank:
| SELECT CustomerName FROM Depositor EXCEPT SELECT CustomerName FROM Borrower ; |
The Except operation automatically eliminates the duplicates. If all the
duplicates are supposed to be retained, EXCEPT ALL is used in place of
EXCEPT.
No comments:
Post a Comment