INNER JOIN as part of the LEFT JOIN offer

Question

I was given a request that uses some very strange syntax for a connection, and I need to understand how this connection is used:

SELECT T1.Acct# , T2.New_Acct# , T3.Pool# FROM DB.dbo.ACCT_TABLE T1 LEFT JOIN DB.dbo.CROSSREF_TABLE T2 INNER JOIN DB.dbo.POOL_TABLE T3 ON T2.Pool# = T3.Pool# ON T1.Acct# = T2.Prev_Acct# 

• T1 is a separate list of accounts
• T2 is a separate account list for each pool #
• T3 is a great pool list (account group)

I need to return the previous account number stored in T2 for each entry in T1. I also need the T3 Pool # returned for each pool.

What I'm trying to understand is why someone wrote the code this way. It makes no sense to me.

Answer 1

A little indentation will show you better what was intended

 SELECT T1.Acct# , T2.New_Acct# , T3.Pool# FROM DB.dbo.ACCT_TABLE T1 LEFT JOIN DB.dbo.CROSSREF_TABLE T2 INNER JOIN DB.dbo.POOL_TABLE T3 ON T2.Pool# = T3.Pool# ON T1.Acct# = T2.Prev_Acct# 

This is a valid syntax that forces the join order a bit. Basically, it only queries the entries in table T2, which are also in table T3, and then left them joining T1. I do not like it personally, as it is confusing for the service. I would prefer a view because I find it much clearer and much easier to change when I need to perform maintenance after six months:

 SELECT T1.Acct# , T2.New_Acct# , T3.Pool# FROM DB.dbo.ACCT_TABLE T1 LEFT JOIN (select T2.New_Acct#, T3.Pool# FROM DB.dbo.CROSSREF_TABLE T2 INNER JOIN DB.dbo.POOL_TABLE T3 ON T2.Pool# = T3.Pool#) T4 ON T1.Acct# = T4.Prev_Acct#