Differentiate between Union, Minus and Intersect
Question
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways
Answers ( 6 )
INTERSECT gives you the rows that are found in both queries by eliminating rows that are only found in one or the other query. MINUS gives you the rows that are found in the first query and not in the second query by removing from the results all the rows that are found only in the second query.UNION merges the results of two SELECT statements
Let us explain this using 2 simple sets-
A = [1,2,3,4,5,6]
B=[4,5,6,7,8,9]
Now A union B = [1,2,3,4,5,6,7,8,9] = All the elements (no repetition) in A and B
A intersect B = [4,5,6] = elements common between A and B
A minus B = [1,2,3] = elements of A which are not present in B
Code-
A = {1,2,3,4,5,6}
B= {4,5,6,7,8,9}
print(A-B) = minus, returns {1,2,3}
print(A | B) = union, returns {1,2,3,4,5,6,7,8,9}
print(A.intersection(B)) = intersection, returns {4,5,6}
Lets understand union, intersection and minus with the help of an example-
S1 = {1,2,3}
S2 = {3,4,5}
print(S1.union(S2))
print(S1.intersection(S2))
print(S1.difference(S2))
output-
{1,2,3,4,5}
{3}
{1.2}
Here, union gives all the values in S1 and S2 with the common value occuring only once.
Intersection gives the common value between two sets
Minus(or difference) gives us all the values in S1 that are not present in S2. If we have the same values in both the sets, the difference will be a null set.
Let’s take the example of two tables-one containing a list of states in India that had an average no. of Covid cases that were higher than the national average in 2020 and the other table containing the same for 2021.
A union between the two sets would be a list of all states that had an average no. of cases above the national average in 2021 or 2020. There would not be any duplicates here
SELECT state
FROM 2021_list
UNION
SELECT state
FROM 2020_list
A Minus between the 2021 set and 2020 set would be the states that had a higher than average no. of cases in 2021, but not in 2020.
SELECT state
FROM 2021_list
MINUS
SELECT state
FROM 2020_list
An intersect between the 2021 set and 2020 set would be the states that had a higher than average no. of cases in 2021 and not in 2020.
SELECT state
FROM 2021_list
EXCEPT SELECT state
FROM 2020_list
CREATE TABLE FIRST (ID INTEGER)
INSERT INTO FIRST(ID) VALUES (1) , (2), (3), (4), (5), (6);
CREATE TABLE SECOND (ID INTEGER)
INSERT INTO SECOND(ID) VALUES (4) , (5), (6), (7), (8), (9);
SELECT ID FROM FIRST
UNION
SELECT ID FROM SECOND;
OUTPUT : (1,2,3,4,5,6,7,8)
SELECT ID FROM FIRST
INTERSECT
SELECT ID FROM SECOND;
OUTPUT : (4,5,6)
SELECT ID FROM FIRST
MINUS
SELECT ID FROM SECOND;
OUTPUT : (1,2,3)
Set1= {1,2,3,4,5,6}
Set2= {3,4,5,7,8,9}
print(Set.union(Set2))
print(Set1.intersection(Set2))
print(Set1.difference(Set2))
output-
{1,2,3,4,5, 6,7,8,9}
{3,4,5}
{1,2,6}
Union give all value of set1 & set2.
Intersection gives common value of between both set.
Minus gives us all the values in Set1 that are not present in Set2.