Use Of Self Joins Instead Of Cursor Or While Loop

• Download execution plan - 1.2 KB
• Download script - 844 B

Introduction

This is an example and I like using it every time I have such a requirement, i.e., to get the total of amount as you read the next record by moving up or down.

This example explains how you could get the running balance for an account using self joins.

Background

Earlier and even now, many people use the Cursor and while loop to get the balance of an account by date and their transaction.

Cursor, as we all know, takes time to execute and a cumbersome code syntax.

Which was eventually replaced by while loop, though it was a performance improvement as against cursor it has to go to each record and do the operation.

So here, we are going to see the examples of each and self join.

Using the Code

Consider you have the table "Passbook" as below:

So to get the account balance by each transaction, we can use Cursor:

DECLARE @BalCursor float=0
DECLARE @TransctionNumber int
DECLARE @TransactionType Varchar(1)
DECLARE @TransactionAmount float

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR 
FOR
SELECT TransctionNumber,TransactionType,TransactionAmount
FROM   #Passbook order by TransctionNumber

OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @TransctionNumber, @TransactionType, @TransactionAmount
        WHILE @@FETCH_STATUS = 0
        BEGIN
            select @BalCursor=@BalCursor+Case when @TransactionType='C' 
                       then @TransactionAmount else -@TransactionAmount end
            

            Update #Passbook set AccountBalance=@BalCursor 
                         where TransctionNumber=@TransctionNumber
             
            FETCH NEXT FROM @MyCursor
            INTO @TransctionNumber, @TransactionType, @TransactionAmount
        END
CLOSE @MyCursor
DEALLOCATE @MyCursor

select * from #Passbook

You will get output as shown below:

Now, you can do the same thing using While loop as below:

Update #Passbook set AccountBalance=0 

DECLARE @WhileBalance float
Declare @MinTransNo int,@MaxTransNo int
select @MinTransNo=MIN(TransctionNumber),@MaxTransNo=MAX(TransctionNumber) from #Passbook

While @MinTransNo<=@MaxTransNo
BEGIN

            select @WhileBalance=SUM(Case when p1.TransactionType='C' _
            then P1.TransactionAmount else -P1.TransactionAmount end)
            From #Passbook p1 where TransctionNumber<=@MinTransNo

            Update #Passbook set AccountBalance=@WhileBalance where TransctionNumber=@MinTransNo
            
            set @MinTransNo=@MinTransNo+1

END

select * from #Passbook

and here comes the Self Join :)

select
P2.TransctionNumber,P2.AccountHolderCode,P2.TransactionType,P2.TransactionDate,_
SUM(Case when p1.TransactionType='C' _
then P1.TransactionAmount else -P1.TransactionAmount end) AccountBalance from #Passbook P1
Inner Join 
#Passbook P2
On P1.AccountHolderCode=P2.AccountHolderCode and P1.TransctionNumber<=P2.TransctionNumber
Group By P2.TransctionNumber,P2.AccountHolderCode,P2.TransactionType,P2.TransactionDate

which can achieve the same output.

Also some cool stuff with below query and output as below:

I have attached the script to create the table with all the queries as described here. Anyone interested can use that and play around with it.

New addition to the above script is the new feature of 2012 SQL version which is Over clause and the code for that is as below:

SELECT 
         TransactionDate,AccountHolderCode, TransctionNumber,
         AccountBalance=SUM(Case when p2.TransactionType='C' 
                            then P2.TransactionAmount else -P2.TransactionAmount end)
                 OVER (ORDER BY p2.TransctionNumber)
FROM  #Passbook p2
ORDER BY TransactionDate,AccountHolderCode,TransctionNumber;

which will result in the same output of running balances.

And now, I also have comparison of all the above queries with execution plan and the surprising result is that the Self join has the execution cost less than all.

And here that goes: