Using Common Table Expressions
Common Table Expressions (CTEs) are a powerful way to compartmentalize and organize your complex queries into bite-sized chunks that you can then manipulate the final output required by your process. CTEs can also be used to create recursive/hierarchal relationships to report on common parent/child scenarios, such as manager/employee.
In this chapter, we will learn how to use CTEs as one of the techniques to solve complex business and data problems. We’ll cover the best practices for creating and organizing CTEs, creating recursive queries for use in hierarchies, and reviewing other situations where CTEs can be used.
We are going to cover the following main topics in this chapter:
- Creating CTEs
- Using CTEs in recursive relationships
- Recursive alternatives for Synapse
By the end of this chapter, you will understand why CTEs are important for query creators, how to create and use them, and how to utilize them to create...
