Question

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

Answer 1

我来普及一下知识

这里的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>

Answer 2

其实并不一定，在实际测试中，except的执行计划需要先排序，然后再执行比较，not in直接比较，如果，索引没有用到，都是全文的，那么not in快一些的，如果无法避免，not in是可以使用的，而且再10万数据左右，速度都不会特别慢

Answer 3

not in 的编译过程比较长，你打*，和把所有名字都打出来，肯定是打名字的效率高。
但是如果放在存储过程中，那2者效率基本相等。

Answer 4

not in不可以用索引

Answer 5

not exists 吧 你是什么数据库有 except ?

学习中....