You are viewing an older version of this section. View current production version.
EXCEPT and MINUS
EXCEPT and MINUS are synonymous operators which can be used to compare the results of two queries and return the distinct rows from the first query that are not output by the second. When comparing two result sets using EXCEPT or MINUS, the data types must be compatible and the number and order of the columns should match across queries.
Syntax
SELECT ...
{ EXCEPT | MINUS }
SELECT ...
Arguments
Accepts two or more queries that return data of comparable data types.
Remarks
In addition to being able to be used in queries, EXCEPT and MINUS can also be used in subqueries and common table expressions (CTEs).
The column names of the returned result set are the same as the column names returned by the query on the left side of the operator.
If using ORDER BY, column names must match those returned by the left-side query.
Examples
The following example demonstrates how to compare two result sets using EXCEPT:
memsql> create table t(a int, b varchar(30));
memsql> create table s(a int, b varchar(30));
memsql> insert into t values (1, "x"), (2, "y"), (3, "z");
memsql> insert into s values (1, "x"), (2, "y"), (4, "q");
memsql> select * from s
-> except
-> select * from t;
+------+------+
| a | b |
+------+------+
| 4 | q |
+------+------+
The following example compares the same result sets using MINUS:
memsql> select * from s
-> minus
-> select * from t;
+------+------+
| a | b |
+------+------+
| 4 | q |
+------+------+
The following example shows how to use MINUS in a subquery:
memsql> select * from (select * from t minus select * from s) as q;
+------+------+
| a | b |
+------+------+
| 3 | z |
+------+------+
Related Topics
