Imagine that you have a more or less complex query that allows you to
obtain cross information from several tables with complicated conditions,
but what you really want is not exactly that data, but to be able to use
it later to make more filters with it in the same query.
For example, you pull a bunch of data from several related tables, with
combined or calculated fields, some extracted from XML fields, and then
you want to find out only which records have a certain different field, or
you want to make additional conditions based on the calculated fields.
Something similar to this:
SELECT Field1, Field2 FROM (SELECT complex)WHERE Conditions ORDER
BY Fields
This would be, in essence, a compound query, mathematically equivalent to
F(fx) or a function of a function.
It seems like the most natural thing in the world to be able to do it.
The problem is that SQL Server doesn’t let you do it most of the time.
Most likely, when trying to do so, you will get the following error
message:
Msg 102, Level 15, State 1, Line x Incorrect syntax near ‘)’.
Which is, of course, not a great clue as to what’s going on.
Also, if we are using it from the SQL Server Management Studio, it
underlines the name of the fields of the outer query as “invalid field
name”.
Common Table Expressions
Now, SQL Server offers us a quite interesting functionality called Common
Table Expressions or, according to its initials, CTE.
It is a way of defining a temporary data set (something like a temporary
table) that only lasts while our query is executed and is not stored
anywhere, that is, it does not take up space.
However, it allows us to consult it and work with it as if it were a real
database table.
These CTEs can refer to themselves, and can be used multiple times in the
same query.
One of the benefits of the CTE is that it opens the possibility of
creating recursive queries, a very interesting concept that gives us a lot
of power.
In addition to being used in recursive queries, they can be used, among
other things, to:
- Filter in a simple way by fields that did not exist before.
- Replace the use of views, which are often not necessary.
- Referencing the same table multiple times in the same expression.
- Group or filter by fields that are derived from subqueries.
- Group or filter by fields that result from functions that are not
deterministic (that is, they return a different value each time they are
called, even passing the same arguments).
The basic syntax of a CTE is as follows:
WITH CTEName [(return fields)] AS (Subquery)SELECT * FROM CTEName
That is, we specify a name for the “temporary table” (CTE), an alias with
‘AS’, and then in parentheses the complex query we want to use as the
basis for the CTE.
Right after that we can enter the query to be carried out using the name
that we have given to the CTE as if it were another table.
If you want to change the default names, you can indicate in parentheses
the name of the fields that the subquery will return in order.
Although this is not used often, since we usually leave the default names
that they have or have been assigned.
Guidelines for creating and using common table expressions
The following guidelines apply to non-recursive common table expressions:
- Note that recursive common table expressions allow us to reference
themselves and can be used multiple times in the same query. - A CTE can be used in only one statement at a time (Select, Update,
Insert, or Delete) that references some or all of the columns defined
within the CTE. - A CTE can be specified within a view.
- Specifying more than one WITH clause in a CTE is not allowed.
- The following clauses cannot be used in a CTE: ORDER BY (except when
the TOP clause is specified) , INTO, FOR BROWSE. - A query that references a CTE can be used to define a cursor.
Example
We want to obtain the list of the first 10 products that have the largest
quantity per Outer in the Warehouse.
As you can see we are defining a common table expression called
Quantity_outer , this CTE allows to return the quantity per outer for each
product.
With Quantity_outer As (Select Warehouse.StockItems.StockItemID,
SUM(QuantityPerOuter) QuantityPerOuter FROM [WareHouse].[StockItems]
GROUP by StockItemID) Select Top 10 A.StockItemID, A.StockItemName,
IsNull(B.QuantityPerOuter,0) QuantityPerOuter from
[Warehouse].StockItems A Left join Quantity_outer B on
(A.StockItemID=B.StockItemID) Order by QuantityPerOuter Desc
Obtaining this
Supplies to follow this tutorial
To follow and run the instructions in this tutorial, you must have the
following programs installed