If you want to combine information from multiple tables or queries, there is a useful tool in MySQL called “Set operators”. Let’s see what those are!!!… in this post.
MySql supports some of the set operations on the table data. Set operations are often called as “Vertical joins”. Those are used to retrieve the desired results from the data we have stored in the table based on different cases. In this post we are going to cover four different kinds of SET operations including examples.
- UNION ALL.
Let’s us check the above one by one.
Before going to check open your MySQL command line client and do as follows:
Now we can perform the above operations.
This is used to combine the results of two or more SELECT statements into one result set and then eliminates any duplicates from that result set.
Here all the distinct values from both the tables’ r1 and r2 are displayed.
This combines the results of two or more SELECT statements into one result set including duplicates.
If you compare the result sets of both the union and union all statements; in union statement only the distinct values are included but no duplicate values whereas in the result set of union all statement it includes the duplicates also.
In brief about UNION and UNION ALL just Click here.
It combines two SELECT statements but returns the result set which are common in from both select statements. Notice that, whenever you are performing ‘intersect’ the number of columns and datatype must be same.
Note: MySql does not support INTERSECT operator.
In brief about Intersect just Click here.
Minus compares the results of two queries and returns distinct rows from the first query that aren’t output by the second query.
Note: MySql does not support MINUS operator.
But we can stimulate them(intersect and minus operators) in another way. We can see them in upcoming posts.