CODEWITHSUNDEEP
sqlt-sql
iddqd
iddqd

Reputation: 135

INNER JOIN within a LEFT JOIN clause

I was given a query that uses some very weird syntax for a join and I need to understand how this join is being 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#

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

What I'm trying to understand is why someone would write the code this way. It doesn't make sense to me.

Upvotes: 12

Views: 10165

Answers (2)

squeedle
squeedle

Reputation: 11

An OUTER APPLY would be clearer here:

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

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

A little indenting 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 is asksing for only the records in table T2 that are also in table T3 and then left joining them to T1. I don't like it personally as it is confusing for maintenance. I would prefer a derived table as I find those much clearer and much easier to change when I need to do maintenance six months later:

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#

Upvotes: 16

Related Questions