Difference between CTE and SubQuery?

by

In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full).

In general; A CTE can be used recursively; a sub-query cannot. This makes them especially well suited to tree structures.

More Related Contents:

  1. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  2. Update records in table from CTE
  3. Common Table Expression, why semicolon?
  4. When to use Common Table Expression (CTE)
  5. CTE error: “Types don’t match between the anchor and the recursive part”
  6. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  7. I dont know sql syntax for my table [closed]
  8. How do I UPDATE from a SELECT in SQL Server?
  9. Get top 1 row of each group
  10. How to return only the Date from a SQL Server DateTime datatype
  11. CROSS JOIN vs INNER JOIN in SQL
  12. Referring to a Column Alias in a WHERE Clause
  13. Function vs. Stored Procedure in SQL Server
  14. converting Epoch timestamp to sql server(human readable format)
  15. How to update Identity Column in SQL Server?
  16. How to assign an exec result to a sql variable?
  17. Concatenate values based on ID
  18. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  19. varbinary to string on SQL Server
  20. Scope of temporary tables in SQL Server
  21. SQL Server 2005 ROW_NUMBER() without ORDER BY
  22. Can an INNER JOIN offer better performance than EXISTS
  23. Find the real column name of an alias used in a view?
  24. Stored procedure that exports data into csv files only exports to one file
  25. Get Start and End Date from Week Number SQL Server
  26. How to get the identity of an inserted row?
  27. How to create a pivot query in sql server without aggregate function
  28. SQL Server FOR XML Path make repeating nodes
  29. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  30. Sql Server : How to use an aggregate function like MAX in a WHERE clause

Leave a Comment