Skip to content
Home » MySQL » Minus in MySQL

Minus in MySQL

  • MySQL

In MySQL, there're UNION, UNION ALL set operators like Oracle, but no MINUS. You have to work around it by doing LEFT JOIN tricks.

In this case, there're two similar tables table1 and table2. Now, I want to know what are the unique persons in table1 by doing table1 minus table2. Therefore, the joined columns are first_name, last_name and gender, 3 columns.

Let's see content of table1 and table2.

mysql> select * from table1;                                                               +----+------------+-----------+--------+
| id | first_name | last_name | gender |
+----+------------+-----------+--------+
|  1 | Ed         | Chen      | M      |
|  2 | Tom        | Brady     | M      |
|  3 | Su         | Pollard   | F      |
|  4 | Taylor     | NULL      | F      |
|  5 | Robert     | Pattinson | M      |
+----+------------+-----------+--------+
5 rows in set (0.00 sec)

mysql> select * from table2;
+----+------------+-----------+--------+
| id | first_name | last_name | gender |
+----+------------+-----------+--------+
|  1 | Tom        | Brady     | M      |
|  2 | Su         | Pollard   | F      |
|  3 | Robert     | Pattinson | M      |
|  4 | Taylor     | Lautner   | M      |
|  5 | Kate       | Upton     | F      |
+----+------------+-----------+--------+
5 rows in set (0.00 sec)

Let's perform a LEFT JOIN on the two tables.

mysql> select t1.first_name, t1.last_name, t1.gender from table1 t1 left join table2 t2 on t1.first_name = t2.first_name and t1.last_name = t2.last_name and t1.gender = t2.gender where t2.first_name is null;
+------------+-----------+--------+
| first_name | last_name | gender |
+------------+-----------+--------+
| Ed         | Chen      | M      |
| Taylor     | NULL      | F      |
+------------+-----------+--------+
2 rows in set (0.00 sec)

The above statement is a correct usage to work around MINUS and the result is under my expectations. But I should remind you that there're many pitfalls waiting for you. Now, let see what rules that we should follow:

  1. You must list all required joined columns (depend on your logic) in ON clause. (You may use USING instead.)
  2. The selected columns in SELECT clause must be table1, not table2.
  3. You must set at least one joined column which is a NOT NULL column of table2 to be NULL in WHERE clause.
Let's see common mistakes that we may make.
  1. List the joined columns of table2 in SELECT clause. (The wrong usage is colored in red)
  2. mysql> select t2.first_name, t1.last_name, t1.gender from table1 t1 left join table2 t2 on t1.first_name = t2.first_name and t1.last_name = t2.last_name and t1.gender = t2.gender where t2.first_name is null;
    +------------+-----------+--------+
    | first_name | last_name | gender |
    +------------+-----------+--------+
    | NULL       | Chen      | M      |
    | NULL       | NULL      | F      |
    +------------+-----------+--------+
    2 rows in set (0.00 sec)

    The output is just pieces of a whole. You should use table1 instead of table2 to be listed.

  3. List one joined column of table1 to be NULL. (The wrong usage is colored in red)
  4. mysql> select t1.first_name, t1.last_name, t1.gender from table1 t1 left join table2 t2 on t1.first_name = t2.first_name and t1.last_name = t2.last_name and t1.gender = t2.gender where t1.first_name is null;
    Empty set (0.00 sec)

    There's nothing returned. You should use table2 instead of table1 to be the main filter of LEFT JOIN.

Although they are wrong usages of the equivalent MINUS, the above examples can also be helpful to us on clarifying the concept of LEFT JOIN.

The second way to do MINUS is to perform a NOT IN subquery.

mysql> select first_name, last_name, gender from table1 where (first_name, last_name, gender) not in (select first_name, last_name, gender from table2);
+------------+-----------+--------+
| first_name | last_name | gender |
+------------+-----------+--------+
| Ed         | Chen      | M      |
| Taylor     | NULL      | F      |
+------------+-----------+--------+
2 rows in set (0.01 sec)

This kind of statement is simple and understandable but it's only for the operations between small tables. Therefore, I think you should accustom to LEFT JOIN which is more powerful.

Leave a Reply

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