[adsense id=”0514458240″ width=”468″ height=”60″]
This post is to show how we can implement Oracle’s MINUS set operator on sets of data using SQL. For an example of how the MINUS operator works in Oracle click here
The MINUS operator give the following result set. Consider the following, let’s imagine we have a result set A in blue and we want to get only those entries that are not in result set B. From the image below the resultset we are looking for is the set of data visible in blue.
Let’s start off by showing the set of data we will be working with:
mysql> SELECT n, title
-> FROM say2
-> WHERE n IN ( 1, 3, 5 )
-> ;
+------+-------+
| n | title |
+------+-------+
| 1 | Mom |
| 3 | SUN |
| 5 | Asia |
+------+-------+
3 rows in set (0.00 sec)
mysql> SELECT n, title
-> FROM say2
-> WHERE n IN ( 5, 7, 8 )
-> ;
+------+-------+
| n | title |
+------+-------+
| 5 | Asia |
| 7 | Venus |
| 8 | Korea |
+------+-------+
3 rows in set (0.00 sec)
mysql> SELECT n, title
-> FROM say2
-> WHERE n IN ( 1, 3, 5 )
-> UNION
-> SELECT n, title
-> FROM say2
-> WHERE n IN ( 5, 7, 8 )
-> ;
+------+-------+
| n | title |
+------+-------+
| 1 | Mom |
| 3 | SUN |
| 5 | Asia |
| 7 | Venus |
| 8 | Korea |
+------+-------+
5 rows in set (0.00 sec)
The UNION operator shows the result set of both A and B in the diagram above. Now to get the difference we need to show the result set from A LEFT JOINed to the result set from B. Those that have matching entries will show data for the B fields. Fields that do not have a valid join will be NULL. We need to test on the NULL.
mysql> SELECT a.n,
-> a.title
-> FROM say2 a
-> LEFT JOIN say2 b
-> ON ( a.n = b.n
-> AND a.title = b.title
-> AND b.n IN ( 5, 7, 8 ) )
-> WHERE a.n IN ( 1, 3, 5 )
-> AND b.n IS NULL;
+------+-------+
| n | title |
+------+-------+
| 1 | Mom |
| 3 | SUN |
+------+-------+
2 rows in set (0.00 sec)
[googleplus]