Argthtjtr

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'

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' 

Looking at the where clause, the row being pointed to by table1 requires the column type to = '0' and the row being pointed to by table1alias requires the column columna to = 'conn'.

Perhaps there are multiple rows on table1 for the same id3?

Without seeing the table structure - the approach could be to make use of a smaller non-covering index and then joining into the table on a larger covering index to get remainder of rows to avoid a 'Key Lookup' operation and to avoid modifying existing indexes (or if you cannot modify the indexes)

Whenever a table appears more than once in a complex join, it is usually because there is an entity that participates in more than one relationship. That appears to be the case here, judging by the answer that @Ypercube gave.

Entities and relationships are generally understood through the semantics of the data, and the connection to the underlying subject matter. If your legacy system was carefully built, they probably took some care to analyze the subject matter, and carefully define each of the data elements. They may even have built an Entity-Relationship model. All of that careful work may have been lost, and you are stuck reconstructing it by digging into the past. This is a little like archaeology.

With table names like Table1, we haven't got a clue about how your subject matter works. And even if the names were descriptive, our understanding of your system's subject matter may be very different from what is needed in your case. It's going to be up to you.