James Elliott
asked on
Subquery vs Join
Can someone please explain why these produce different results:
SELECT s1.category_ID
FROM
(SELECT TOP(10) *
FROM tblRequestLines) s1
SELECT s1.category_id
FROM tblRequestLines s1
INNER JOIN
(SELECT
TOP(10) *
FROM tblRequestLines) s2
ON s1.ID = s2.ID
that's the main reason, but also that with the JOIN, if the ID has "duplicates", it will do a cross-tab of the results...
ASKER
In terms of solution, then the Order By clause has solved my problem, but it doesn't really explain why the results are different. Surely they should be the same?
Does the primary query modify the execution of the bracketed subquery?
SELECT TOP(10) * FROM Table
ColumnA ---- ColumnB
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1
SELECT s1.ColumnB FROM (SELECT TOP(10) * FROM Table) s1
I expect to give me
10
9
8
7
6
5
4
3
2
1
but instead, gives me
1
2
3
4
5
6
7
8
9
10
Does the primary query modify the execution of the bracketed subquery?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks both
TOP and BOTTOM keywords should work always with an ORDER BY clause otherwise you have the risk to get different results as you could saw by your own experience.