Except用法

EXCEPT 子句/运算符用于将两个 SELECT 语句结合在一起，
并返回第一个 SELECT 语句的结果中那些不存在于第二个 SELECT 语句结果的记录。这就意味着，EXCEPT 仅返回那些不存在于第二个 SELECT 语句结果的记录（差集）。
EXCEPT 运算符遵循同 UNION 运算符一样的规则。MySQL 不支持 EXCEPT 运算符。
EXCEPT子句的基本语法如下所示：

SELECT a,b  FROM  t1
where 条件

EXCEPT

SELECT a,b  FROM t2 where 条件
这里给定的条件可以是任何根据你自己的需要而得出的表达式。

示例

两个表格，（a）CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

表：（b）ORDERS

+-----+---------------------+-------------+--------+
| OID | DATE                |          ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

现在，让我将这两个表的 EXCEPT 查询的结果结合在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT
     SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

其结果如下所示：

±—±--------±-------±--------------------+
| ID | NAME | AMOUNT | DATE |
±—±--------±-------±--------------------+
| 1 | Ramesh | NULL | NULL |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
±—±--------±-------±--------------------+

12345678910111213141516-- 测试表，与测试数据

CREATE TABLE union_tab_1 ( id INT, val VARCHAR(10));
CREATE TABLE union_tab_2 ( id INT, val VARCHAR(10));
INSERT INTO union_tab_1 VALUES(1, ‘A’);
INSERT INTO union_tab_1 VALUES(2, ‘B’);
INSERT INTO union_tab_1 VALUES(3, ‘C’);
INSERT INTO union_tab_2 VALUES(1, ‘A’);
INSERT INTO union_tab_2 VALUES(1, ‘A’);
INSERT INTO union_tab_2 VALUES(2, ‘B’);
INSERT INTO union_tab_2 VALUES(4, ‘D’);

  EXCEP
T– 返回第一个表中有、第二个表中没有的数据  SQL  Server   支持 123456789101112131415161> 

SELECT * FROM union_tab_12
> EXCEPT3
> > SELECT * FROM union_tab_2;
> 4
> > goid          val----------- ----------          3
>  C(1 行受影响)1
>  > SELECT * FROM union_tab_22
>  > EXCEPT3
>  > SELECT * FROM union_tab_1;
>  4
>  > goid          val----------- ----------          
>  4 D(1 行受影响)  
>  MySQL  不支持实现相同功能的 SQL 如下：  1234567891011121314
>  SELECT  DISTINCT union_tab_1.*FROM 
>   union_tab_1 LEFT JOIN union_tab_2    
>   ON (union_tab_1.id = union_tab_2.id        
>   AND union_tab_1.val = union_tab_2.val)
>   WHERE  union_tab_2.id  IS  NULL;
>   +------+------+
>   | id   | val  |
>   +------+------+
>   |    3 | C    |
>   +------+------+
>   1 row in set (0.00 sec)

https://www.cnblogs.com/951106Nancy/p/9290539.html