What's the reason for using double inner join in this SQL statement?
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'
sql-server
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
add a comment |
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'
sql-server
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
What is a self join for? (in english)
– philipxy
Dec 24 '18 at 22:58
add a comment |
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'
sql-server
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
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'
sql-server
sql-server
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
edited Dec 18 '18 at 20:34
Peter Mortensen
23119
23119
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
asked Dec 18 '18 at 9:52
MathematicsMathematics
1922312
1922312
What is a self join for? (in english)
– philipxy
Dec 24 '18 at 22:58
add a comment |
What is a self join for? (in english)
– philipxy
Dec 24 '18 at 22:58
What is a self join for? (in english)
– philipxy
Dec 24 '18 at 22:58
What is a self join for? (in english)
– philipxy
Dec 24 '18 at 22:58
add a comment |
4 Answers
4
active
oldest
votes
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'
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
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.
– Darkwing
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
– Mathematics
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 theONclause of a join or in theWHEREclause can matter a great deal if the join's anOUTER JOIN. If a condition fails in theONclause, the primary row is still included (without a matching outer row); if it fails in theWHEREclause, then the primary row is excluded from the result set.
– RDFozz
Dec 18 '18 at 21:54
add a comment |
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?
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
add a comment |
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)
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
add a comment |
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.
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225240%2fwhats-the-reason-for-using-double-inner-join-in-this-sql-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
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'
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
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.
– Darkwing
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
– Mathematics
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 theONclause of a join or in theWHEREclause can matter a great deal if the join's anOUTER JOIN. If a condition fails in theONclause, the primary row is still included (without a matching outer row); if it fails in theWHEREclause, then the primary row is excluded from the result set.
– RDFozz
Dec 18 '18 at 21:54
add a comment |
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'
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
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.
– Darkwing
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
– Mathematics
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 theONclause of a join or in theWHEREclause can matter a great deal if the join's anOUTER JOIN. If a condition fails in theONclause, the primary row is still included (without a matching outer row); if it fails in theWHEREclause, then the primary row is excluded from the result set.
– RDFozz
Dec 18 '18 at 21:54
add a comment |
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'
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
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'
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
answered Dec 18 '18 at 10:17
yper-crazyhat-cubeᵀᴹyper-crazyhat-cubeᵀᴹ
74.7k11126207
74.7k11126207
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.
– Darkwing
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
– Mathematics
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 theONclause of a join or in theWHEREclause can matter a great deal if the join's anOUTER JOIN. If a condition fails in theONclause, the primary row is still included (without a matching outer row); if it fails in theWHEREclause, then the primary row is excluded from the result set.
– RDFozz
Dec 18 '18 at 21:54
add a comment |
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.
– Darkwing
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
– Mathematics
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 theONclause of a join or in theWHEREclause can matter a great deal if the join's anOUTER JOIN. If a condition fails in theONclause, the primary row is still included (without a matching outer row); if it fails in theWHEREclause, then the primary row is excluded from the result set.
– RDFozz
Dec 18 '18 at 21:54
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.
– Darkwing
Dec 18 '18 at 14:40
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.
– Darkwing
Dec 18 '18 at 14:40
3
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
– Mathematics
Dec 18 '18 at 14:51
@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
– Mathematics
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
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
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
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
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
@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
add a comment |
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?
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
add a comment |
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?
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
add a comment |
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?
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
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?
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
answered Dec 18 '18 at 10:08
Scott HodginScott Hodgin
16.9k21534
16.9k21534
add a comment |
add a comment |
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)
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
add a comment |
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)
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
add a comment |
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)
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
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)
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
answered Dec 18 '18 at 14:07
Allan S. HansenAllan S. Hansen
77148
77148
add a comment |
add a comment |
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.
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
add a comment |
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.
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
add a comment |
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.
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
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.
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
edited Dec 18 '18 at 19:25
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
answered Dec 18 '18 at 14:30
Walter MittyWalter Mitty
3,2651317
3,2651317
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225240%2fwhats-the-reason-for-using-double-inner-join-in-this-sql-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is a self join for? (in english)
– philipxy
Dec 24 '18 at 22:58