gotchasqlMinor
Why does Inner Join introduce a hidden sort?
Viewed 0 times
whyintroducehiddenjoindoessortinner
Problem
Consider the following example:
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: )
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) > @suppliedNumberAs 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
The
See the order by in the query plan
This might be faster
It breaks if the purpose is multiple accounts with same name
Or
join is producing the sort The
group by sum is producing the sortSee 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) > @suppliedNumberOr
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.IDCode 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) > @suppliedNumberSELECT 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.IDContext
StackExchange Database Administrators Q#164166, answer score: 8
Revisions (0)
No revisions yet.