Bottom-line NOT EXISTS typically will perform best, followed by NOT IN, then LEFT JOIN and EXCEPT.
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'Empl_FK_Dept'))
ALTER TABLE Empl DROP CONSTRAINT Empl_FK_Dept;
GO
IF OBJECT_ID( 'Dept' ) IS NOT NULL DROP TABLE Dept;
GO
CREATE TABLE Dept
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Dept_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'ProjEmpl_FK_Proj'))
ALTER TABLE ProjEmpl DROP CONSTRAINT ProjEmpl_FK_Proj;
GO
IF OBJECT_ID( 'Proj' ) IS NOT NULL DROP TABLE Proj;
GO
CREATE TABLE Proj
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Proj_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'ProjEmpl_FK_Empl'))
ALTER TABLE ProjEmpl DROP CONSTRAINT ProjEmpl_FK_Empl;
GO
IF OBJECT_ID( 'Empl' ) IS NOT NULL DROP TABLE Empl;
GO
CREATE TABLE Empl
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
DeptID int NULL,
MgrID int NULL,
CONSTRAINT Empl_PK PRIMARY KEY CLUSTERED ( ID ),
CONSTRAINT Empl_FK_Dept FOREIGN KEY ( DeptID ) REFERENCES Dept ( ID ),
CONSTRAINT Empl_FK_Empl FOREIGN KEY ( MgrID ) REFERENCES Empl ( ID )
);
GO
IF OBJECT_ID( 'ProjEmpl' ) IS NOT NULL DROP TABLE ProjEmpl;
GO
CREATE TABLE ProjEmpl
(
ProjID int NOT NULL,
EmplID int NOT NULL,
CONSTRAINT ProjEmpl_PK PRIMARY KEY CLUSTERED ( ProjID, EmplID ),
CONSTRAINT ProjEmpl_FK_Proj FOREIGN KEY ( ProjID ) REFERENCES Proj ( ID ),
CONSTRAINT ProjEmpl_FK_Empl FOREIGN KEY ( EmplID ) REFERENCES Empl ( ID )
);
GO
SET NOCOUNT ON;
DECLARE @CNT int = 0;
WHILE @CNT < 100
BEGIN
SELECT @CNT += 1
INSERT INTO Dept ( Name ) SELECT 'Dept ' + CONVERT( varchar, @CNT, 0 )
INSERT INTO Proj ( Name ) SELECT 'Proj ' + CONVERT( varchar, @CNT, 0 )
INSERT INTO Empl ( Name, DeptID, MgrID ) SELECT 'Empl ' + CONVERT( varchar, @CNT, 0 ), (@CNT+1)/2, case when @CNT%2 = 1 THEN NULL ELSE @CNT - 1 END
END
SELECT @CNT = 0;
WHILE @CNT < 100
BEGIN
SELECT @CNT += 1
INSERT INTO ProjEmpl ( ProjID, EmplID )
SELECT @CNT, @CNT WHERE @CNT%10 IN ( 1, 3, 4, 6, 9 )
UNION ALL
SELECT @CNT, @CNT - 1 WHERE @CNT%10 = 7
UNION ALL
SELECT @CNT, @CNT + 1 WHERE @CNT%10 = 9
END
SET NOCOUNT OFF;
select * from ProjEmpl
SET SHOWPLAN_ALL ON
GO
select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl )
select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID )
select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null
select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID
GO
SET SHOWPLAN_ALL OFF
GO
/*
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl ) 1 1 0 NULL NULL 1 NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 1 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Top(TOP EXPRESSION:((1))) 1 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID])) 1 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 1 10 2 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50
select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID ) 2 11 0 NULL NULL 2 NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 2 12 11 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 2 13 12 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 2 14 13 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Top(TOP EXPRESSION:((1))) 2 16 13 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID])) 2 17 16 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 2 20 12 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50
select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null 3 21 0 NULL NULL 3 NULL 49.5 NULL NULL NULL 0.04512281 NULL NULL SELECT 0 NULL
|--Filter(WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL)) 3 22 21 Filter Filter WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL) NULL 49.5 0 4.9896E-05 285 0.04512281 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
|--Hash Match(Right Outer Join, HASH:([pe].[EmplID])=([e].[ID])) 3 23 22 Hash Match Right Outer Join HASH:([pe].[EmplID])=([e].[ID]) NULL 103.95 0 0.01896592 289 0.04507292 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name], [pe].[EmplID] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe])) 3 24 23 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]) [pe].[EmplID] 70 0.003125 0.000234 11 0.003359 [pe].[EmplID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 3 25 23 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 99 0 0.000418 285 0.022745 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 3 26 25 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 3 27 25 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100
select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID 4 28 0 NULL NULL 4 NULL 99 NULL NULL NULL 0.04415589 NULL NULL SELECT 0 NULL
|--Merge Join(Left Anti Semi Join, MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL)) 4 29 28 Merge Join Left Anti Semi Join MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL) NULL 99 0 0.0060143 151 0.04415589 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID])) 4 30 29 Nested Loops Inner Join OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID]) NULL 99 0 0.000418 151 0.022745 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD) 4 31 30 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] 100 0.003125 0.000267 15 0.003392 [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
| |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD) 4 32 30 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100
|--Sort(ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC)) 4 33 29 Sort Sort ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC) NULL 70 0.01126126 0.0007693287 11 0.01538959 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK])) 4 34 33 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]) [Test].[dbo].[ProjEmpl].[EmplID] 70 0.003125 0.000234 11 0.003359 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1
(28 row(s) affected)
*/
SET STATISTICS PROFILE ON
GO
select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl )
select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID )
select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null
select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID
GO
SET STATISTICS PROFILE OFF
GO
/*
ID Name ID Name DeptID MgrID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 2 Empl 2 1 1
3 Dept 3 5 Empl 5 3 NULL
4 Dept 4 7 Empl 7 4 NULL
4 Dept 4 8 Empl 8 4 7
6 Dept 6 12 Empl 12 6 11
8 Dept 8 15 Empl 15 8 NULL
9 Dept 9 17 Empl 17 9 NULL
9 Dept 9 18 Empl 18 9 17
11 Dept 11 22 Empl 22 11 21
13 Dept 13 25 Empl 25 13 NULL
14 Dept 14 27 Empl 27 14 NULL
14 Dept 14 28 Empl 28 14 27
16 Dept 16 32 Empl 32 16 31
18 Dept 18 35 Empl 35 18 NULL
19 Dept 19 37 Empl 37 19 NULL
19 Dept 19 38 Empl 38 19 37
21 Dept 21 42 Empl 42 21 41
23 Dept 23 45 Empl 45 23 NULL
24 Dept 24 47 Empl 47 24 NULL
24 Dept 24 48 Empl 48 24 47
26 Dept 26 52 Empl 52 26 51
28 Dept 28 55 Empl 55 28 NULL
29 Dept 29 57 Empl 57 29 NULL
29 Dept 29 58 Empl 58 29 57
31 Dept 31 62 Empl 62 31 61
33 Dept 33 65 Empl 65 33 NULL
34 Dept 34 67 Empl 67 34 NULL
34 Dept 34 68 Empl 68 34 67
36 Dept 36 72 Empl 72 36 71
38 Dept 38 75 Empl 75 38 NULL
39 Dept 39 77 Empl 77 39 NULL
39 Dept 39 78 Empl 78 39 77
41 Dept 41 82 Empl 82 41 81
43 Dept 43 85 Empl 85 43 NULL
44 Dept 44 87 Empl 87 44 NULL
44 Dept 44 88 Empl 88 44 87
46 Dept 46 92 Empl 92 46 91
48 Dept 48 95 Empl 95 48 NULL
49 Dept 49 97 Empl 97 49 NULL
49 Dept 49 98 Empl 98 49 97
(40 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
40 1 select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl ) 1 1 0 NULL NULL NULL NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
40 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
40 1 |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 1 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
100 1 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
60 100 | |--Top(TOP EXPRESSION:((1))) 1 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
60 100 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID])) 1 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
40 40 |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 1 10 2 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50
(7 row(s) affected)
ID Name ID Name DeptID MgrID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 2 Empl 2 1 1
3 Dept 3 5 Empl 5 3 NULL
4 Dept 4 7 Empl 7 4 NULL
4 Dept 4 8 Empl 8 4 7
6 Dept 6 12 Empl 12 6 11
8 Dept 8 15 Empl 15 8 NULL
9 Dept 9 17 Empl 17 9 NULL
9 Dept 9 18 Empl 18 9 17
11 Dept 11 22 Empl 22 11 21
13 Dept 13 25 Empl 25 13 NULL
14 Dept 14 27 Empl 27 14 NULL
14 Dept 14 28 Empl 28 14 27
16 Dept 16 32 Empl 32 16 31
18 Dept 18 35 Empl 35 18 NULL
19 Dept 19 37 Empl 37 19 NULL
19 Dept 19 38 Empl 38 19 37
21 Dept 21 42 Empl 42 21 41
23 Dept 23 45 Empl 45 23 NULL
24 Dept 24 47 Empl 47 24 NULL
24 Dept 24 48 Empl 48 24 47
26 Dept 26 52 Empl 52 26 51
28 Dept 28 55 Empl 55 28 NULL
29 Dept 29 57 Empl 57 29 NULL
29 Dept 29 58 Empl 58 29 57
31 Dept 31 62 Empl 62 31 61
33 Dept 33 65 Empl 65 33 NULL
34 Dept 34 67 Empl 67 34 NULL
34 Dept 34 68 Empl 68 34 67
36 Dept 36 72 Empl 72 36 71
38 Dept 38 75 Empl 75 38 NULL
39 Dept 39 77 Empl 77 39 NULL
39 Dept 39 78 Empl 78 39 77
41 Dept 41 82 Empl 82 41 81
43 Dept 43 85 Empl 85 43 NULL
44 Dept 44 87 Empl 87 44 NULL
44 Dept 44 88 Empl 88 44 87
46 Dept 46 92 Empl 92 46 91
48 Dept 48 95 Empl 95 48 NULL
49 Dept 49 97 Empl 97 49 NULL
49 Dept 49 98 Empl 98 49 97
(40 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
40 1 select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID ) 2 1 0 NULL NULL NULL NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
40 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 2 2 1 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
40 1 |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 2 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
100 1 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 2 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
60 100 | |--Top(TOP EXPRESSION:((1))) 2 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
60 100 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID])) 2 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
40 40 |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 2 10 2 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50
(7 row(s) affected)
ID Name ID Name DeptID MgrID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 2 Empl 2 1 1
3 Dept 3 5 Empl 5 3 NULL
4 Dept 4 7 Empl 7 4 NULL
4 Dept 4 8 Empl 8 4 7
6 Dept 6 12 Empl 12 6 11
8 Dept 8 15 Empl 15 8 NULL
9 Dept 9 17 Empl 17 9 NULL
9 Dept 9 18 Empl 18 9 17
11 Dept 11 22 Empl 22 11 21
13 Dept 13 25 Empl 25 13 NULL
14 Dept 14 27 Empl 27 14 NULL
14 Dept 14 28 Empl 28 14 27
16 Dept 16 32 Empl 32 16 31
18 Dept 18 35 Empl 35 18 NULL
19 Dept 19 37 Empl 37 19 NULL
19 Dept 19 38 Empl 38 19 37
21 Dept 21 42 Empl 42 21 41
23 Dept 23 45 Empl 45 23 NULL
24 Dept 24 47 Empl 47 24 NULL
24 Dept 24 48 Empl 48 24 47
26 Dept 26 52 Empl 52 26 51
28 Dept 28 55 Empl 55 28 NULL
29 Dept 29 57 Empl 57 29 NULL
29 Dept 29 58 Empl 58 29 57
31 Dept 31 62 Empl 62 31 61
33 Dept 33 65 Empl 65 33 NULL
34 Dept 34 67 Empl 67 34 NULL
34 Dept 34 68 Empl 68 34 67
36 Dept 36 72 Empl 72 36 71
38 Dept 38 75 Empl 75 38 NULL
39 Dept 39 77 Empl 77 39 NULL
39 Dept 39 78 Empl 78 39 77
41 Dept 41 82 Empl 82 41 81
43 Dept 43 85 Empl 85 43 NULL
44 Dept 44 87 Empl 87 44 NULL
44 Dept 44 88 Empl 88 44 87
46 Dept 46 92 Empl 92 46 91
48 Dept 48 95 Empl 95 48 NULL
49 Dept 49 97 Empl 97 49 NULL
49 Dept 49 98 Empl 98 49 97
(40 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
40 1 select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null 3 1 0 NULL NULL NULL NULL 49.5 NULL NULL NULL 0.04512281 NULL NULL SELECT 0 NULL
40 1 |--Filter(WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL)) 3 2 1 Filter Filter WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL) NULL 49.5 0 4.9896E-05 285 0.04512281 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
110 1 |--Hash Match(Right Outer Join, HASH:([pe].[EmplID])=([e].[ID])) 3 3 2 Hash Match Right Outer Join HASH:([pe].[EmplID])=([e].[ID]) NULL 103.95 0 0.01896592 289 0.04507292 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name], [pe].[EmplID] NULL PLAN_ROW 0 1
70 1 |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe])) 3 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]) [pe].[EmplID] 70 0.003125 0.000234 11 0.003359 [pe].[EmplID] NULL PLAN_ROW 0 1
100 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 3 5 3 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 99 0 0.000418 285 0.022745 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
100 1 |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 3 6 5 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
100 100 |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 3 7 5 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100
(7 row(s) affected)
ID Name ID DeptID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 1 1
1 Dept 1 2 1
2 Dept 2 3 2
2 Dept 2 4 2
3 Dept 3 5 3
3 Dept 3 6 3
4 Dept 4 7 4
4 Dept 4 8 4
5 Dept 5 9 5
5 Dept 5 10 5
6 Dept 6 11 6
6 Dept 6 12 6
7 Dept 7 13 7
7 Dept 7 14 7
8 Dept 8 15 8
8 Dept 8 16 8
9 Dept 9 17 9
9 Dept 9 18 9
10 Dept 10 19 10
10 Dept 10 20 10
11 Dept 11 21 11
11 Dept 11 22 11
12 Dept 12 23 12
12 Dept 12 24 12
13 Dept 13 25 13
13 Dept 13 26 13
14 Dept 14 27 14
14 Dept 14 28 14
15 Dept 15 29 15
15 Dept 15 30 15
16 Dept 16 31 16
16 Dept 16 32 16
17 Dept 17 33 17
17 Dept 17 34 17
18 Dept 18 35 18
18 Dept 18 36 18
19 Dept 19 37 19
19 Dept 19 38 19
20 Dept 20 39 20
20 Dept 20 40 20
21 Dept 21 41 21
21 Dept 21 42 21
22 Dept 22 43 22
22 Dept 22 44 22
23 Dept 23 45 23
23 Dept 23 46 23
24 Dept 24 47 24
24 Dept 24 48 24
25 Dept 25 49 25
25 Dept 25 50 25
26 Dept 26 51 26
26 Dept 26 52 26
27 Dept 27 53 27
27 Dept 27 54 27
28 Dept 28 55 28
28 Dept 28 56 28
29 Dept 29 57 29
29 Dept 29 58 29
30 Dept 30 59 30
30 Dept 30 60 30
31 Dept 31 61 31
31 Dept 31 62 31
32 Dept 32 63 32
32 Dept 32 64 32
33 Dept 33 65 33
33 Dept 33 66 33
34 Dept 34 67 34
34 Dept 34 68 34
35 Dept 35 69 35
35 Dept 35 70 35
36 Dept 36 71 36
36 Dept 36 72 36
37 Dept 37 73 37
37 Dept 37 74 37
38 Dept 38 75 38
38 Dept 38 76 38
39 Dept 39 77 39
39 Dept 39 78 39
40 Dept 40 79 40
40 Dept 40 80 40
41 Dept 41 81 41
41 Dept 41 82 41
42 Dept 42 83 42
42 Dept 42 84 42
43 Dept 43 85 43
43 Dept 43 86 43
44 Dept 44 87 44
44 Dept 44 88 44
45 Dept 45 89 45
45 Dept 45 90 45
46 Dept 46 91 46
46 Dept 46 92 46
47 Dept 47 93 47
47 Dept 47 94 47
48 Dept 48 95 48
48 Dept 48 96 48
49 Dept 49 97 49
49 Dept 49 98 49
50 Dept 50 99 50
50 Dept 50 100 50
(100 row(s) affected)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
100 1 select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID 4 1 0 NULL NULL NULL NULL 99 NULL NULL NULL 0.04415589 NULL NULL SELECT 0 NULL
100 1 |--Merge Join(Left Anti Semi Join, MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL)) 4 2 1 Merge Join Left Anti Semi Join MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL) NULL 99 0 0.0060143 151 0.04415589 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
100 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID])) 4 3 2 Nested Loops Inner Join OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID]) NULL 99 0 0.000418 151 0.022745 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
100 1 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD) 4 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] 100 0.003125 0.000267 15 0.003392 [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
100 100 | |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD) 4 5 3 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100
70 1 |--Sort(ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC)) 4 6 2 Sort Sort ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC) NULL 70 0.01126126 0.0007693287 11 0.01538959 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1
70 1 |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK])) 4 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]) [Test].[dbo].[ProjEmpl].[EmplID] 70 0.003125 0.000234 11 0.003359 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1
(7 row(s) affected)
*/
This entry was posted
on Wednesday, May 06, 2009
and is filed under
SQL Server
.
You can leave a response
and follow any responses to this entry through the
Subscribe to:
Post Comments (Atom)
.
Filed under
SQL Server
?l'ta?m
0 comments