Common Table Expressions Archives

Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart.

There are two types to CTE’s – non-recursive and recursive.

Non-recursive CTEs are used to simplify complex queries; we love them! You can use them instead of a view or to increase readability.

Recursive CTE’s are used to perform what you though was the impossible; really! They are so kewl.

I would totally recommend you get your hands on some of these archived articles and get familiar with CTE’s. You really appreciate them once you do as they greatly extend SQL capabilities.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server

Recursive CTEs Explained

Recursive CTE’s This article will be discussing recursive Common Table Expressions (CTEs). Recursive CTEs are unique, such that they are allowed to reference their own. With this special ability, you can use recursive CTEs in solving problems where other queries cannot. Recursive CTEs are best in working with hierarchical data such as org charts for […]

Non Recursive CTEs Explained and Why to Use Them

Introduction to Non Recursive CTEs In this article we explore non recursive CTEs (Common Table Expressions). This is a broad class, and basically covers every form of CTEs except those that call themselves. This other class is called the recursive CTEs; they are covered in the next article. If you’re unfamiliar with CTEs I would […]

Common Table Expressions (Introduction to CTE’s)

The Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart. In this article, we’ll introduce you to common table expressions, the two types of the CTEs, and their uses. In addition, we’ll […]