T-SQL Tutorials - TSQLTutorials.com

WITH - Common Table Expressions


Introduction

The WITH Common Table Expression (CTE) is used for creating temporary named result sets. The functionality was introduced in SQL Server 2005. This T-SQL Expression begins with the keyword WITH. The results from the WITH expression are stored in a temporary named result set that can be queried. Using the WITH expression, allows for the simplification of query logic, by allowing the separation of logic into separate steps. The WITH expression has a few basic parts:

  • WITH [name of temporary resultset] (columns in result set)
  • AS ( SQL Query Definition )
following the 'WITH' keyword is the name of the temporary result set being created and the names of the columns in the result set. Parentheses are placed around the column names.

following the 'AS' keyword will be the SQL Query (surrounded by parentheses). The number of columns selected in the query must match the number of columns listed in the Table Expression Definition (the columns listed after name of resultset).




Beginning Example

This example will use the following table: SELECT Name, Position, Salary
FROM Employees
Here is the resultset from the above SELECT query:

NamePositionSalary
Joe GrapeManager80000
John PlumSoftware Developer65000
Frank AppleSoftware Developer62000
Patty PineappleSoftware Developer60000
Judy PeachSoftware Developer50000
Jane OrangeProject Manager75000

Using the WITH expression we can create a temporary named result set. In this example we will limit the result set to only the Software Developers from the above Employees table. WITH Developers (Name,Salary)
AS
(
SELECT Name, Salary FROM Employees WHERE Position = 'Software Developer'
)


SELECT * FROM Developers
Here is the resultset from the above SELECT query from the 'Developers' result set:

NameSalary
John Plum65000
Frank Apple62000
Patty Pineapple60000
Judy Peach50000


Microsoft SQL Server 2014 Standard Edition Server w/20 CAL’s

Price: $641.99
Buy It Now
MSFT SQL Server 2012 Standard Edition, 32bit & 64bit, Media & License w/25CAL

Price: $499.99
Buy It Now
MSFT SQL Server 2016 Standard Edition x64 64bit | 10 CAL USERS Edition

Price: $699.99
Buy It Now
Microsoft SQL Server 2014 Standard LicensePack +Microsoft USB +BONUS 10 or 25CAL
Price: $1179
Buy It Now
Brand New - Microsoft SQL Server 2012 Standard Edition SP3 and 10 CAL License

Price: $660
Buy It Now
Microsoft SQL Server 2012 Standard +Original Microsoft USB |10 or 25CAL| LIMITED
Price: $949
Buy It Now







Copyright 2017 - TSQLTutorials.com