1. Except

Except

前言

SQL中，EXCEPT 操作用于检索存在于第一个表中的唯一记录，而不是两个表中共有的记录。这个操作符与SQL的UNION操作符相反。

为了更好地理解，考虑两个具有以下记录的表，如下图所示。

如果我们对上述两个表执行EXCEPT运算符来检索Name字段，它将只显示来自第一个表的记录，这些记录与第二个表的记录不相同。

在这里，“Dev”在两个表中都是相同的。因此，EXCEPT运算符将排除它，并将“Sara”和“Jay”作为输出检索出来。

Except 语法

SELECT column1, column2,…, columnN
FROM table1,
     table2,…, tableN
    [Conditions] //optional
    
    EXCEPT
    
SELECT column1, column2,…, columnN
FROM table1,
     table2,…, tableN
    [Conditions] //optional

Note：两个 SELECT 语句中的列个数和列顺序需要保证相同。

Except 使用举例

-- 建表 STUDENTS
SQL> CREATE TABLE STUDENTS(
   ID INT NOT NULL, 
   NAME VARCHAR(20) NOT NULL, 
   HOBBY VARCHAR(20) NOT NULL, 
   AGE INT NOT NULL, 
   PRIMARY KEY(ID)
);

SQL> SELECT * FROM STUDENTS;

-- Output

+-----+----------+--------------+-------+
| ID  |  NAME    |    HOBBY     |  AGE  |
+-----+----------+--------------+-------+
| 1   |  Vijay   |   Cricket    |   18  |
| 2   |  Varun   |   Football   |   26  |
| 3   |  Surya   |   Cricket    |   19  |
| 4   |  Karthik |   Cricket    |   25  |
| 5   |  Sunny   |   Football   |   26  |
| 6   |  Dev     |   Cricket    |   23  |
+-----+----------+--------------+-------+

-- 建表 ASSOCIATES
SQL> CREATE TABLE ASSOCIATES(
   ID INT NOT NULL, 
   NAME VARCHAR(20) NOT NULL, 
   SUBJECT VARCHAR(20) NOT NULL, 
   AGE INT NOT NULL, 
   HOBBY VARCHAR(20) NOT NULL, 
   PRIMARY KEY(ID)
);

SQL> SELECT * FROM ASSOCIATES;

-- Output

+-----+----------+---------------+-------+----------+
| ID  |  NAME    |    SUBJECT    |  AGE  |  HOBBY   |
+-----+----------+---------------+-------+----------+
| 1   |  Naina   |   Mathematics |   24  | Cricket  |
| 2   |  Varun   |   Physics     |   26  | Football | 
| 3   |  Dev     |   Mathematics |   23  | Cricket  |
| 4   |  Priya   |   Physics     |   25  | Cricket  |
| 5   |  Adithya |   Chemistry   |   21  | Cricket  |
| 6   |  Kalyan  |   Mathematics |   30  | Football |
+-----+----------+--------------+-------+-----------+

接下来，使用 Except 查询检索出仅出现在第一个表中的记录。

SQL> SELECT NAME, HOBBY, AGE
FROM STUDENTS

EXCEPT 	

SELECT NAME, HOBBY, AGE
FROM ASSOCIATES

-- Output

+-----------+--------------+-------+
|   NAME    |    HOBBY     |  AGE  |
+-----+----------+---------+-------+
|   Karthik |   Cricket    |   25  |
|   Sunny   |   Football   |   26  |
|   Surya   |   Cricket    |   19  |
|   Vijay   |   Cricket    |   18  |
+-----------+--------------+-------+

结合其他关键字使用

EXCEPT with BETWEEN operator

检索出仅出现在第一个表中的记录，此外，保证检索出记录的AGE在 20 岁到 30 岁之间。

SQL> SELECT NAME, HOBBY, AGE
FROM STUDENTS
WHERE AGE BETWEEN 20 AND 30

EXCEPT 

SELECT NAME, HOBBY, AGE 
FROM ASSOCIATES
WHERE AGE BETWEEN 20 AND 30

-- Output

+----------+----------+-----+
|   NAME   |  HOBBY   | AGE |
+----------+----------+-----+
|  Karthik | Cricket  | 25  |
|  Sunny   | Football | 26  |
+----------+----------+-----+

Except with IN operator

检索出仅出现在第一个表中的记录，此外，保证检索出记录的HOBBY是 “Cricket”。

SQL> SELECT NAME, HOBBY, AGE FROM STUDENTS
WHERE HOBBY IN('Cricket')

EXCEPT

SELECT NAME, HOBBY, AGE FROM ASSOCIATES
WHERE HOBBY IN('Cricket')

-- Output

+-----------+--------------+-------+
|   NAME    |    HOBBY     |  AGE  |
+-----+----------+---------+-------+
|   Karthik |   Cricket    |   25  |
|   Surya   |   Cricket    |   19  |
|   Vijay   |   Cricket    |   18  |
+-----------+--------------+-------+

EXCEPT with LIKE operator

检索出仅出现在第一个表中的记录，此外，保证检索出记录的NAME以 ‘v’ 开头。

SQL> SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE 'v%'

EXCEPT

SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE NAME LIKE 'v%'

-- Output

+-----------+-------+----------+
|   NAME    |  AGE  |  HOBBY   |
+-----------+-------+----------+
|   Vijay   |   18  | Cricket  |
+-----------+-------+----------+

文章参考

SQL - EXCEPT