CODEWITHSUNDEEP
sql-servert-sqlsubquery
v010dya
v010dya

Reputation: 5858

What is the difference between subquery in from and query before select in TSQL?

I have two queries:

SELECT *
FROM (
  SELECT id, name FROM t1
) AS t

and

t AS (
  SELECT id, name FROM t1
)
SELECT *;

Are they both doing the same thing? Is one more preferable than another?

Upvotes: 1

Views: 90

Answers (2)

Gaston G
Gaston G

Reputation: 396

Using a Common Table Expression (CTE) is also useful when you have to repeat the code inside. So that you juste have to call the CTE when you need it instead of duplicate code.

Upvotes: 1

Horia
Horia

Reputation: 1612

Sencond query should be:

WITH t AS (
  SELECT id, name FROM t1
)
SELECT *
FROM t;

There is no difference in what they return. As a personal option, I go for 2nd version - CTE, reason being readability. But other than that, subquery vs CTE - they should behave the same.

Hope that helps

Upvotes: 3

Related Questions