CODEWITHSUNDEEP
sqlansi-sql
lucky
lucky

Reputation: 41

What is a Common Table Expression used for?

WITH Extracted_Sessions ( a,b ) AS 
(
   select a,b from db.tblnm
)

What does this SQL do? Could some one explain please?

Upvotes: 2

Views: 1827

Answers (2)

Mikeb
Mikeb

Reputation: 6361

Going to guess that that's a Common Table Expression : it's a construction that is a lot like a temp table :

Using the name Extracted_Sessions with columns a and b in place of the query select a, b from db.tblnm ... do stuff on Extracted_Sessions as though it was a real thing.

Commonly used to build recursive queries.

Upvotes: 0

user330315
user330315

Reputation:

This is called a "common table expression". A SQL feature which has been introduced in SQL 99 if I'm not mistaken.

It is very similar to a derived table. Your example is equivalent to

select a,b
from (
  select a,b from db.tblnm
) as Extracted_Sessions

The main difference is that you can use a CTE more than once in a query, whereas with a derived table you need to repeat the underlying query each time, e.g:

WITH Extracted_Sessions ( a,b ) AS 
(
   select a,b from db.tblnm
)
select *
from Extracted_Sessions e1
  join Extracted_Sessions e2 on e1.a = e.2b

(Note this probably doesn't make sense, it's just to demonstrate that you can access the CTE more than once).

The Postgres manual has a nice tutorial on CTEs: http://www.postgresql.org/docs/current/static/queries-with.html

Upvotes: 4

Related Questions