EXCEPT 子句

EXCEPT 子句/运算符用于将两个 SELECT 语句结合在一起，并返回第一个 SELECT 语句的结果中那些不存在于第二个 SELECT 语句结果的记录。这就意味着，EXCEPT 仅返回那些不存在于第二个 SELECT 语句结果的记录(差集)。

EXCEPT 运算符遵循同 UNION 运算符一样的规则。MySQL 不支持 EXCEPT 运算符。

EXCEPT子句的基本语法如下所示：

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

这里给定的条件可以是任何根据你自己的需要而得出的表达式。

示例：

考虑如下两个表格，(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 |

+----+---------+--------+---------------------+

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 分割线+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

EXCEPT– 返回第一个表中有、第二个表中没有的数据

SQL  Server   支持

MySQL  不支持

实现相同功能的 SQL 如下：