In MySQL, we can use various SET operators – UNION, UNION ALL, INTERSECT, MINUS.
For this article we have two sets – A(1,2,3) B(3,4,5)
UNION – The UNION operator is used to combine the result-set of two or more SELECT statements. MySQL Union is used to combine the output of two or more “SELECT” statements. The output of the “UNION” will be without duplicate rows.
We have lets UNION two above sets A and B, then it will (1,2,3,4,5)
UNION ALL – The UNION operator selects only distinct values by default. To allow duplicate values, we can use UNION ALL.
We have lets UNION ALL two above sets A and B, then it will (1,2,3,3,4,5)
INTERSECT – The INTERSECT operator is a set operator that returns distinct rows of two or more result sets from SELECT statements. Like Oracle SQL we found an ‘INTERSECT’ command to do this operation, but in MySQL don’t. For that we use INNER JOIN to achieve this.
We have let INTERSECT two above sets A(1,2,3) and B(3,4,5), then it will be 3.
MINUS – we can say that MINUS operator will return only those rows which are unique in only the first SELECT query and not those rows which are common to both first and second SELECT queries. Like Oracle SQL we don’t get MINUS commands directly so, for achieving this we can use LEFT JOIN in MySQL.
We have INTERSECT two above sets A(1,2,3) and B(3,4,5), then it will be (1,2,4,5).
