Example HTML page

MySQL Set operators

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.

  1. UNION.
  2. UNION ALL.
  3. INTERSECT
  4. MINUS.

Let’s us check the above one by one.

Before going to check open your MySQL command line client and do as follows:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sampledb           |
| sports             |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> use sampledb;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| r1                 |
| r2                 |
+--------------------+
2 rows in set (0.00 sec)

mysql> select * from r1;
+-----------+
| elements1 |
+-----------+
|         2 |
|         3 |
|         4 |
|         5 |
+-----------+
4 rows in set (0.00 sec)

mysql> select * from r2;
+-----------+
| elements2 |
+-----------+
|         4 |
|         5 |
|         6 |
|         7 |
+-----------+
4 rows in set (0.00 sec)

Now we can perform the above operations.

1.UNION:

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.

Ex:

mysql> select * from r1 union select * from r2;
+-----------+
| elements1 |
+-----------+
|         2 |
|         3 |
|         4 |
|         5 |
|         6 |
|         7 |
+-----------+
6 rows in set (0.00 sec)

Here all the distinct values from both the tables’ r1 and r2 are displayed.

2.UNION ALL:

This combines the results of two or more SELECT statements into one result set including duplicates.

Ex:

mysql> select * from r1 union all select * from r2;
+-----------+
| elements1 |
+-----------+
|         2 |
|         3 |
|         4 |
|         5 |
|         4 |
|         5 |
|         6 |
|         7 |
+-----------+
8 rows in set (0.00 sec)

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.

3.INTERSECT:

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.

4.MINUS:

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.

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest