Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

From this post How to use ROW_NUMBER in the following procedure?

There are two versions of answers where one uses a SubQuery and the other uses a CTE to solve the same problem.

Now then, what is the advantage of using a CTE (Common Table Expression) over a sub-query(thus, more readable what the query is actually doing)

The only advantage of using a CTE over sub select is that I can actually name the subquery. Are there any other differences between those two when a CTE is used as a simple (non-recursive) CTE?

1 Answer

0 votes
by (40.7k points)

In the sub-query vs simple (non-recursive) CTE versions, they will be almost similar. 

To spot any differences, you just have to use the profiler and actual execution plan, and that will be specific to your setup.

Note: A CTE can be used recursively but a sub-query cannot. This makes them especially well suited for tree structures.

Related questions

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...