Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

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

Open in new window

0
James Elliott
Asked:
James Elliott
  • 2
  • 2
  • 2
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Because you don't have an ORDER BY clause.
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
James ElliottManaging DirectorAuthor Commented:
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?


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

Open in new window


Does the primary query modify the execution of the bracketed subquery?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It will try to return records sorted by a clustered index. If no clustered index is found, I think it will sort by the first column in the SELECT clause and by default in Ascending order.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unless you specify ORDER BY, a query will never have any guaranteed "order".
neither the order of insertion, nor the order of the values nor any other order.
in practice, the DBMS engine will choose some "execution plan", and starts to fetch the data from the data pages behind.
it may even return data from pages that are (still) in memory, to avoid the disk reads...
0
 
James ElliottManaging DirectorAuthor Commented:
Thanks both
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now