HiveBrain v1.2.0
Get Started
← Back to all entries
gotchasqlMinor

Why does Inner Join introduce a hidden sort?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whyintroducehiddenjoindoessortinner

Problem

Consider the following example:

DECLARE @suppliedNumber MONEY
SET @suppliedNumber = 245656.00

SELECT 
    ah.FirstName, 
    ah.LastName
FROM 
    AccountHolders AS ah
JOIN 
    [dbo].[Accounts] AS a
ON 
    ah.Id = a.AccountHolderId
GROUP BY 
    ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber


As you can see there is no ordering of any kind in the query. But looking in the execution plan I see implicit sorting:

Why is this happening? What's the technical reason behind this?

I'm using Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64) May 27 2016 15:06:08 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 14393: )

Solution

I don't think the join is producing the sort

The group by sum is producing the sort

See the order by in the query plan

This might be faster

It breaks if the purpose is multiple accounts with same name

SELECT ah.Id, ah.FirstName, ah.LastName
FROM 
    AccountHolders AS ah
JOIN 
    [dbo].[Accounts] AS a
ON 
    ah.Id = a.AccountHolderId
GROUP BY 
    ah.Id, ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber


Or

SELECT ah.Id, ah.FirstName, ah.LastName
FROM AccountHolders AS ah
JOIN ( select AccountHolderId
       from [dbo].[Accounts] A
       GROUP BY Id 
       HAVING SUM(Balance) > @suppliedNumber 
     ) a
 on a.AccountHolderId = ah.ID

Code Snippets

SELECT ah.Id, ah.FirstName, ah.LastName
FROM 
    AccountHolders AS ah
JOIN 
    [dbo].[Accounts] AS a
ON 
    ah.Id = a.AccountHolderId
GROUP BY 
    ah.Id, ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber
SELECT ah.Id, ah.FirstName, ah.LastName
FROM AccountHolders AS ah
JOIN ( select AccountHolderId
       from [dbo].[Accounts] A
       GROUP BY Id 
       HAVING SUM(Balance) > @suppliedNumber 
     ) a
 on a.AccountHolderId = ah.ID

Context

StackExchange Database Administrators Q#164166, answer score: 8

Revisions (0)

No revisions yet.