Hi all
I have a query that uses another query in its definition. I've edited that subquery to make it run faster, and both versions return the same data. When I investigated the new subquery, I found the only difference was the order and join type of the tables in the FROM clause (a couple of right joins became inner joins) - but both subqueries return the exact same data.
When I substitute my new subquery for the current one, the outlying query returns no rows.
How does this happen? My understanding of sql is that the subquery is looked at as if it was a table, and how it was constructed shouldn't matter.
I'd really rather use the new subquery. It returns almost instantaneously, compared to the original version that takes 3-5 seconds. Because the query is used a lot in a report we do, this gets magnified and could end up two or three minutes quicker.
any ideas?
thx,
mcalex
I have a query that uses another query in its definition. I've edited that subquery to make it run faster, and both versions return the same data. When I investigated the new subquery, I found the only difference was the order and join type of the tables in the FROM clause (a couple of right joins became inner joins) - but both subqueries return the exact same data.
When I substitute my new subquery for the current one, the outlying query returns no rows.
How does this happen? My understanding of sql is that the subquery is looked at as if it was a table, and how it was constructed shouldn't matter.
I'd really rather use the new subquery. It returns almost instantaneously, compared to the original version that takes 3-5 seconds. Because the query is used a lot in a report we do, this gets magnified and could end up two or three minutes quicker.
any ideas?
thx,
mcalex
