Both CTE's and Sub-Queries are ways of using the results of one query and using them in another query. We will start off by looking at CTE's
CTE's
Lets start with defining what CTE stands for Common Table Expression. I prefer to use CTE's because I think of it like creating a new table that I will then join onto my original table later on.
Below is how to structure a CTE:
Sub-Queries
Sub-Queries are another way of using the results of one query within another query. Using the format shown below you can do a join on a common column between the table a and b.
I personally find that using Common Table Expressions are a little easier to understands. I like to bring in all the tables at the start of the query and then refer to them as and when I need them. The choice between the two is very much up to personal preference of how you prefer to work. My final reason for preferring a CTE is that I feel it easier to document. You can always add a comment above or below the CTE to explain its purpose.