Common Table Expressions
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
Latest Posts
-
SQL Puzzle: How to Calculate Moving Averages
In this puzzle, we’re going to learn how to calculate moving averages by working through a hypothetical stock market example. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post you answer in the comments so we all can learn from one…
-
What is a Recursive CTE (Common Table Expression)?
Recursive CTE’s 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 the bill of materials. If you’re unfamiliar with CTE’s, then…
-
Common Table Expressions – The Ultimate Guide
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 SQL CTEs, and their uses. In addition,…
-
Use SQL to Calculate Median Values.
In this puzzle we’re going to learn how to find the person whose birthday, among others, is in the middle. Knowing how to calculate the median value is a good skill to have. As you start to explore business intelligence you’ll come across similar problems to solve. Also, be sure to check out the bonus…
-
How to Find a Gap in a Sequence using SQL SERVER
In this puzzle, we’re going to learn how to find a gap in a sequence using SQL Server. I was recently reviewing my bank statement and noticed they flagged the start of a missing check number with ***’s. I figured with would be a great example for a SQL puzzle as there are many ways…
-
SQL Percentile Rank Puzzle
In this puzzle, we’re going with with SQL Percentile Rank to work through a problem to identify the top and bottom student scores. Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. SQL Puzzle Question In today’s puzzle assume you’ve been asked by the head of the Mathematics department…