SQL Sever

WITH Query in SQL

In the sql most of the new developers don’t know how to use  WITH in SQL query.  Its simple and use full for writing big query in the stored procedure. WITH will provide a way of using sub query in the larger select query. It’s a kind of temp table in the sql query.  One use of this feature is to break down complex query into similar parts. The below is a simple example for with in querying.


;with table1 as
(
      select * from MYTable
),
Table2 as
 (
      select * from MyTable2
),
.
.
.
Table10 as
 (
      select * from MyTable99
)

select * from table1
inner join Table2.ID=table1.ID
Left Outer join table1.ID=Table13.ID



In the final select query we can use sum , group by and etc…

The WITH Common Table Expression (CTE) is used for creating temporary resultsets. This functionality indroudeced in SQL SERVER 2005.

                        

No comments: