SQL EXCEPT运算符

SQL中的 EXCEPT 运算符用于检索存在于第一个表中的唯一记录，而不是两个表共同的记录。该运算符作为SQL UNION运算符的相反操作。

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

SQL EXCEPT运算符

如果我们对上述两个表执行EXCEPT运算符来检索名称，它将只显示第一个表中与第二个表的记录不共有的记录。

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

语法

以下是SQL中EXCEPT运算符的语法 −

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

注意 - 两个SELECT语句中的列的数量和顺序应保持一致。

示例

首先，让我们使用以下查询创建一个名为“ 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> INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, 'Vijay', 'Cricket', 18);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(2, 'Varun', 'Football', 26);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(3, 'Surya', 'Cricket', 19);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(4, 'Karthik', 'Cricket', 25);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(5, 'Sunny', 'Football', 26);
INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(6, 'Dev', 'Cricket', 23);

让我们使用以下查询来验证“STUDENTS”表是否已创建 −

SQL> SELECT * FROM STUDENTS;

如下图所示，数据表已经在数据库中创建。

+-----+----------+--------------+-------+
| 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> INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1, 'Naina', 'Maths', 24, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(2, 'Varun', 'Physics', 26, 'Football');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(3, 'Dev', 'Maths', 23, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(4, 'Priya', 'Physics', 25, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(5, 'Aditya', 'Chemistry', 21, 'Cricket');
INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(6, 'Kalyan', 'Maths', 30, 'Football');

让我们使用以下查询验证是否已创建表“ASSOCIATES”：

SQL> SELECT * FROM ASSOCIATES;

如下输出所示，表已经在数据库中创建。

+-----+----------+---------------+-------+----------+
| 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 |
+-----+----------+--------------+-------+-----------+

让我们使用以下查询来检索仅在第一个表中唯一的记录：

SQL> SELECT NAME, HOBBY, AGE
FROM STUDENTS

EXCEPT  

SELECT NAME, HOBBY, AGE
FROM ASSOCIATES

输出

当我们执行上述查询时，输出结果如下：

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

使用BETWEEN运算符的EXCEPT

如同我们在初始语法中讨论的一样，我们也可以将EXCEPT运算符与条件运算符一起使用。在SQL中，我们可以使用EXCEPT运算符和BETWEEN运算符来排除落在指定范围内的行。

示例

让我们使用以下查询检索只在第一个表中唯一的记录。此外，我们还检索年龄在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

输出

当我们执行程序查询时，输出如下所示−

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

除了使用IN运算符外

我们还可以在SQL中使用EXCEPT运算符和IN运算符一起使用，以排除拥有指定值的行。IN运算符用于基于指定值列表过滤结果集。

示例

在这里，我们正在获取表中仅独特的记录。此外，我们使用 IN 运算符来检索爱好是“Cricket”的记录。

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

EXCEPT

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

输出

当我们执行上述查询时，输出结果如下所示 –

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

使用LIKE运算符的EXCEPT

在SQL中，EXCEPT运算符还可以与LIKE运算符一起使用，来排除与指定模式匹配的行。LIKE运算符用于对字符串进行模式匹配。

示例

让我们使用通配符’%’以及LIKE运算符，从第一个SELECT语句的结果集中检索以’v’开头的姓名。

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

EXCEPT

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

输出

以上查询的输出如下所示：

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