Nested JOINS confusion

  • 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)

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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

  • 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 😀 .

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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