10

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'
27

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' 
  • isn't the WHERE to be applied AFTER the joins, i.e. I would agree if those constraints were part of the join statement, i.e. connected by an AND, but the WHERE in all experience is applied to the result of the join filtering out rows from the joined table, not influencing the actual join. – Frank Hopkins Dec 18 '18 at 14:40
  • 3
    @Darkwing As far as I know, it doesn't matter where you put conditions, as it's job of query optimiser to come up with best exceution plan. However it's better to put them next to joins as it makes them more readable but it's just an opinion – Pointless-Ai Dec 18 '18 at 14:51
  • Even if it were to happen AFTER joining the results of joins are different in the end. And yes, the joined rows are usually filtered before joining as it improves performance. – Gherman Dec 18 '18 at 15:19
  • 1
    It's also equivalent to joining with a subquery, e.g. INNER JOIN (SELECT * FROM table1 WHERE type = 0) table1. That might make it even more obvious what's happening. – Barmar Dec 18 '18 at 17:58
  • 2
    @Mathematics - whether a condition is in the ON clause of a join or in the WHERE clause can matter a great deal if the join's an OUTER JOIN. If a condition fails in the ON clause, the primary row is still included (without a matching outer row); if it fails in the WHERE clause, then the primary row is excluded from the result set. – RDFozz Dec 18 '18 at 21:54
8

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?

2

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)

2

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.