July 11, 2008 at 9:41 am
Let's get back on track here. I think the question at hand is, 'Does moving the filtering columns from the JOIN to the WHERE clause have an impact on the execution plan?'. The answer is absolutely!! When working with OUTER joins, where you place the filter is critical. If you move the filter to the WHERE clause, the query optimizer will treat the query like an INNER JOIN. Think about it, placing the filter in the WHERE clause is saying that that value in the OUTER table must exist to satisfy the query. If the value must exist in the OUTER table, it is no longer an OUTER JOIN as both tables must have a matching row to satisfy the query.
Here's a simple proof:
--*******************************************************
--Prepare test schema objects
--*******************************************************
SET NOCOUNT ON
IF OBJECT_ID('dbo.Child') IS NOT NULL
DROP TABLE dbo.Child
IF OBJECT_ID('dbo.Parent') IS NOT NULL
DROP TABLE dbo.Parent
CREATE TABLE dbo.Parent (
ParentID int IDENTITY(1,1) NOT NULL,
FirstName varchar(20) NOT NULL,
LastName varchar(30) NOT NULL,
Gender char(1) CONSTRAINT CK_Parent_Gender CHECK(Gender IN ('M','F'))
CONSTRAINT PK_Parent PRIMARY KEY (ParentID)
)
CREATE TABLE dbo.Child (
ChildID int IDENTITY(1,1) NOT NULL,
ParentID int NOT NULL,
FirstName varchar(20) NOT NULL,
LastName varchar(30) NOT NULL,
Gender char(1) CONSTRAINT CK_Child_Gender CHECK(Gender IN ('M','F')),
Age int
CONSTRAINT PK_Child PRIMARY KEY (ChildID)
CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID) REFERENCES dbo.Parent(ParentID)
)
CREATE INDEX IDX_Child_ParentID ON dbo.Child (ParentID)
--*******************************************************
--Create test data
--*******************************************************
INSERT INTO dbo.Parent (FirstName, LastName, Gender)
SELECT 'John', 'Rowan', 'M' UNION ALL
SELECT 'Ben', 'Rowan', 'M'
INSERT INTO dbo.Child (ParentID, FirstName, LastName, Gender, Age)
SELECT 1, 'Katie', 'Rowan', 'F', 7 UNION ALL
SELECT 1, 'Krissy', 'Rowan', 'F', 6 UNION ALL
SELECT 1, 'Emma', 'Rowan', 'F', 3 UNION ALL
SELECT 1, 'John', 'Rowan', 'M', 2 UNION ALL
SELECT 1, 'Sam', 'Rowan', 'M', 1
--*******************************************************
--Run sample querires
--*******************************************************
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_TEXT ON
GO
--Filter OUTER table in JOIN clause
SELECT P.*
FROM dbo.Parent P
LEFT JOIN dbo.Child C
ON P.ParentID = C.ParentID AND C.Gender = 'F'
GO
SET SHOWPLAN_TEXT OFF
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_TEXT ON
GO
--Filter OUTER table in WHERE clause
SELECT P.*
FROM dbo.Parent P
LEFT JOIN dbo.Child C
ON P.ParentID = C.ParentID
WHERE C.Gender = 'F'
GO
SET SHOWPLAN_TEXT OFF
GO
And here's the query plans for each. You'll see that placing the filter in the WHERE clause turned the query into an INNER JOIN
StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------
--Filter OUTER table in JOIN clause
SELECT P.*
FROM dbo.Parent P
LEFT JOIN dbo.Child C
ON P.ParentID = C.ParentID AND C.Gender = 'F'
StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Outer Join, WHERE:([TestDB].[dbo].[Parent].[ParentID] as [P].[ParentID]=[TestDB].[dbo].[Child].[ParentID] as [C].[ParentID]))
|--Clustered Index Scan(OBJECT:([TestDB].[dbo].[Parent].[PK_Parent] AS [P]))
|--Clustered Index Scan(OBJECT:([TestDB].[dbo].[Child].[PK_Child] AS [C]), WHERE:([TestDB].[dbo].[Child].[Gender] as [C].[Gender]='F'))
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
--Filter OUTER table in WHERE clause
SELECT P.*
FROM dbo.Parent P
LEFT JOIN dbo.Child C
ON P.ParentID = C.ParentID
WHERE C.Gender = 'F'
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([C].[ParentID]))
|--Clustered Index Scan(OBJECT:([TestDB].[dbo].[Child].[PK_Child] AS [C]), WHERE:([TestDB].[dbo].[Child].[Gender] as [C].[Gender]='F'))
|--Clustered Index Seek(OBJECT:([TestDB].[dbo].[Parent].[PK_Parent] AS [P]), SEEK:([P].[ParentID]=[TestDB].[dbo].[Child].[ParentID] as [C].[ParentID]) ORDERED FORWARD)
July 11, 2008 at 9:45 am
There was absolute agreement on the OUTER JOIN being an issue. The question was, did placing filters on the INNER JOIN make a difference? Several people anecdotally believe(d) so (myself included) but no one had a working example.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2008 at 9:53 am
Well then. I guess the whole point of my post was to let the world know that I have 5 kids and my younger brother has none 😀 .
July 11, 2008 at 11:02 am
OUTER JOIN WHERE conditions acting like INNER JOIN..ON condiitons is documented and intended behavior, per the ANSI SQL standard. In fact, having the option to use both "inner" and "outer" type conditions on the same join is one of the big advantages of the SQL-92 JOIN syntax. (The other being vastly improved readability).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply