No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this:

SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i

I changed it to following

;WITH cte_count
AS
(
    SELECT COUNT(1) c, OrderId FROM Orders Group By ItemId
)
SELECT a.c [Count], i.Name
FROM Items i
INNER JOIN cte_count c ON (c.ItemId = i.ItemId)

But the execution plan for both is same as shown below:

Similarly there was another query selecting TOP 1 Order By Id. I tried moving this one to CROSS APPLY But for this one as well I got same execution plans.

Of course there were other joins and columns in query.

My dilemma is about utility and advantages of using CROSS APPLY and CTE. Are there any or its just exotic?

But the execution plan for both is same as shown below:

The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact.

There are usually many ways to express the same requirement (or similar requirement, as in your example) in SQL. Which you use is initially a question of preference and style. In some cases, using one or the other will produce important performance differences because the declarative SQL takes a different code path through the optimizer. In this particular case, the outer join may play less well with the optimizer's exploration abilities (it has fewer tools to use with outer joins than inner joins).

Rewriting a query to define the same results using different syntax can be a valid tuning method, but it requires careful attention to detail and retesting whenever SQL Server is patched or upgraded. There is generally no reason to prefer one way of expressing a requirement in SQL over another.

In addition, as Andriy mentioned in a comment on the other copy of your question, "in the more general case, your inline queries will give you only one result per row, while joining to a CTE (which doesn't have to be a CTE, it could be a normal subselect) or CROSS APPLYing a row set can give you access to more than one column."

StackExchange Database Administrators Q#106534, answer score: 24