0

Plz tell me what is better between 2 query in mssql ?

;with ab as (select a.*,b.CategoryName from Products a 
left join Categories b on a.CategoryID = b.CategoryID where b.CategoryID = 2)
select * from ab left join Suppliers c on ab.SupplierID = c.SupplierID

or

select * from Products a left join Categories b on a.CategoryID = b.CategoryID
left join Suppliers c on a.SupplierID = c.SupplierID
where b.CategoryID = 2
doveandrose
  • 26
  • 2
  • are the same, WITH only help to make the code more readable. – Juan Carlos Oropeza Apr 01 '17 at 12:59
  • Not enough info to tell. You'd have to test both and "Better" is subjective. I personally would do the 2nd one with the two left joins. I only materialize the data in advance in extremely complex queries to simplify the maintenance. – xQbert Apr 01 '17 at 12:59
  • 1
    The best way to compare them is to compare the execution plans: http://stackoverflow.com/a/7359705. They'll tell you exactly how the query optimizer will execute the queries. – aschmied Apr 01 '17 at 13:01

1 Answers1

1

are the same, WITH only help to make the code more readable.

You can test it with EXPLAIN QUERY How do I obtain a Query Execution Plan?

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118