在sql查询中except为什么比not in效率高

我来普及一下知识

这里的SQL，使用以下的测试表，与测试数据

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');

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

Oracle

SQL> SELECT * FROM union_tab_1
2 MINUS
3 SELECT * FROM union_tab_2;

ID VAL
---------- --------------------
3 C

SQL> SELECT * FROM union_tab_2
2 MINUS
3 SELECT * FROM union_tab_1;

ID VAL
---------- --------------------
4 D

SQL Server

1> SELECT * FROM union_tab_1
2> EXCEPT
3> SELECT * FROM union_tab_2;
4> go
id val
----------- ----------
3 C

(1 行受影响)

1> SELECT * FROM union_tab_2
2> EXCEPT
3> SELECT * FROM union_tab_1;
4> go
id val
----------- ----------
4 D

(1 行受影响)

通过 SET SHOWPLAN_TEXT ON 查看查询计划.

我这里的测试表记录数量很小, 还没有索引, 因此没有参考价值.

1> SET SHOWPLAN_TEXT ON
2> go
1> SELECT * FROM union_tab_1
2> WHERE
3> id NOT IN
4> (SELECT id FROM union_tab_2)
5> go
StmtText

--------------------------------------------------------------------------------
-
SELECT * FROM union_tab_1
WHERE
id NOT IN
(SELECT id FROM union_tab_2)

(1 行受影响)
StmtText

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------
|--Nested Loops(Left Anti Semi Join, WHERE:([Test].[dbo].[union_tab_1].[id] IS
NULL OR [Test].[dbo].[union_tab_2].[id] IS NULL OR [Test].[dbo].[union_tab_1].[
id]=[Test].[dbo].[union_tab_2].[id]))
|--Table Scan(OBJECT:([Test].[dbo].[union_tab_1]))

|--Table Scan(OBJECT:([Test].[dbo].[union_tab_2]))

(3 行受影响)
1> SELECT * FROM union_tab_1
2> EXCEPT
3> SELECT * FROM union_tab_2;
4> go
StmtText
----------------------------------------------------------------
SELECT * FROM union_tab_1
EXCEPT
SELECT * FROM union_tab_2;

(1 行受影响)
StmtText

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Test].[dbo].[union_tab
_1].[id], [Test].[dbo].[union_tab_1].[val]))

|--Sort(DISTINCT ORDER BY:([Test].[dbo].[union_tab_1].[id] ASC, [Test].[d
bo].[union_tab_1].[val] ASC))

| |--Table Scan(OBJECT:([Test].[dbo].[union_tab_1]))

|--Top(TOP EXPRESSION:((1)))

|--Table Scan(OBJECT:([Test].[dbo].[union_tab_2]), WHERE:([Test].[db
o].[union_tab_1].[id] = [Test].[dbo].[union_tab_2].[id] AND [Test].[dbo].[union_
tab_1].[val] = [Test].[dbo].[union_tab_2].[val]))

(5 行受影响)
1> SET SHOWPLAN_TEXT OFF
2> go
1>

您可能感兴趣问答

Collapsible

热门标签

热点问答