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

What's the reason for using double inner join in this SQL statement?

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

Problem

I am looking at this legacy SQL query. The bit I am not able to get is why it's inner joining same table twice on the same columns. I am talking about Table1 and Table1 joined with alias "Table1Alias",

SELECT DISTINCT othercolumns,
                Table1Alias.columna
FROM   maintable
       INNER JOIN secondarytable
               ON maintable.id1 = secondarytable.a_id1
       INNER JOIN table1
               ON secondarytable.id2 = table1.id3
       INNER JOIN table1 Table1Alias
               ON secondarytable.id2 = Table1Alias.id3
       INNER JOIN thirdtable
               ON table1.id4 = thirdtable.id5
       INNER JOIN fourthtable
               ON thirdtable.id6 = fourthtable.id7
       INNER JOIN fivetable
               ON thirdtable.id8 = fivetable.id9
       INNER JOIN sixthtable
               ON Table1Alias.columna = sixthtable.id10
       LEFT JOIN seventhtable
              ON thirdtable.id11 = seventhtable.id12
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09'
       AND secondarytable.type456 = 'cate'
       AND table1.type = '0'
       AND Table1Alias.columna = 'conn'

Solution

It might help to rewrite the query like this, so it is obvious that the 2 joins are different, i.e. the joins are to different subsets (of the same table):

FROM   maintable 
       INNER JOIN secondarytable 
               ON maintable.id1 = secondarytable.a_id1 
       INNER JOIN table1 
               ON secondarytable.id2 = table1.id3 
              AND table1.type = '0' 
       INNER JOIN table1 Table1Alias 
               ON secondarytable.id2 = Table1Alias.id3 
              AND Table1Alias.columna = 'conn' 
       INNER JOIN
       ...
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09' 
       AND secondarytable.type456 = 'cate'

Code Snippets

FROM   maintable 
       INNER JOIN secondarytable 
               ON maintable.id1 = secondarytable.a_id1 
       INNER JOIN table1 
               ON secondarytable.id2 = table1.id3 
              AND table1.type = '0' 
       INNER JOIN table1 Table1Alias 
               ON secondarytable.id2 = Table1Alias.id3 
              AND Table1Alias.columna = 'conn' 
       INNER JOIN
       ...
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09' 
       AND secondarytable.type456 = 'cate'

Context

StackExchange Database Administrators Q#225240, answer score: 27

Revisions (0)

No revisions yet.