MINUS

Description

MINUS compares the result of two queries and returns the different rows in the first query that are not output by the second query.

Syntax

1. SELECT column_list_1 FROM table_1
2. MINUS
3. SELECT columns_list_2 FROM table_2;

Examples

• Example 1

1. CREATE TABLE t1 (id INT PRIMARY KEY);
2. CREATE TABLE t2 (id INT PRIMARY KEY);
3. INSERT INTO t1 VALUES (1),(2),(3);
4. INSERT INTO t2 VALUES (2),(3),(4);

6. mysql> SELECT id FROM t1 MINUS SELECT id FROM t2;
7. +------+
8. | id   |
9. +------+
10. |    1 |
11. +------+

• Example 2

1. drop table if exists t1;
2. drop table if exists t2;
3. create table t1 (a smallint, b bigint, c int);
4. insert into t1 values (1,2,3);
5. insert into t1 values (1,2,3);
6. insert into t1 values (3,4,5);
7. insert into t1 values (4,5,6);
8. insert into t1 values (4,5,6);
9. insert into t1 values (1,1,2);
10. create table t2 (a smallint, b bigint, c int);
11. insert into t2 values (1,2,3);
12. insert into t2 values (3,4,5);
13. insert into t2 values (1,2,1);

15. mysql> select * from t1 minus select * from t2;
16. +------+------+------+
17. | a    | b    | c    |
18. +------+------+------+
19. |    1 |    1 |    2 |
20. |    4 |    5 |    6 |
21. +------+------+------+

23. mysql> select a, b from t1 minus select b, c from t2;
24. +------+------+
25. | a    | b    |
26. +------+------+
27. |    3 |    4 |
28. |    1 |    1 |
29. |    1 |    2 |
30. +------+------+