- GENERATE is a powerful function
that implements the SQL CROSS APPLY feature.
GENERATE is an iterator that receives two arguments.
The first argument needs to be a table.
GENERATE iterates the first argument row by row
and for each of the rows,
it evaluates the second argument
that needs to be an expression that returns a table.
It finally joins the rows in the first table
with all the rows which are coming
from the second expression.
Now, the power of GENERATE is that the expression
that you use as the second argument typically depends
on values in the first argument.
So values in the rows of the first argument.
Therefore, you can join one table
with a function that depends on the values
of the first table.
Let's see that with an example to make it clearer.
In this example, I'm defining first a variable, dates,
that contains two rows.
The first row contain a column, first date,
which is the 1st of January 2007
and last date, which is the 3rd of January 2007.
The second row contains the 9th of January
up to the 12th of January.
The result contains two rows with two columns.
Now, in the second expression,
I'm defining dates expanded.
Dates expanded uses GENERATE
that iterates over the dates table,
so over these two rows
and for each row, it evaluates DATESBETWEEN date...
and returns all the dates between the first
and the last date.
You see that this second expression depends,
because it references first date and last date,
it depends from values in the first table.
So on the first row, it will find these two dates
and DATESBETWEEN returns all the dates
between the 1st and the 3rd of January.
Then they will be joined with the values in the first row.
The result contains first date, last date
and you see that this is repeated multiple times,
three times, and we have 1st of January,
2nd of January, 3rd of January.
One, two and three are being returned by DATESBETWEEN and
first date and last date are coming from the first table.
Then we start with the second row of the first table
that contains dates from the 9th to the 12th of January
and date contains dates from the 9th to the 12th of January.
So GENERATE is extremely useful to join one table
with a function that depends on the values
in the first table.
An important note is about empty results.
If the function that you pass
as the second argument returns an empty table,
GENERATE does not generate the empty row.
And we can see that here because I changed the values.
Now the values go from the 6th of January
to the 3rd of January.
Because the second date is earlier than the first date,
what happens is that they are no dates in between
and the second expression
of GENERATE returns an empty table.
You see that the result does not contain any value coming
from the first table because GENERATE only returns values
from the first table if there are actually values coming
from the function that you specify as the second argument.
If you need to retrieve all the values of the first table,
even though the expression, the second argument,
returns an empty table,
then instead of using GENERATE, you can use GENERATEALL.
GENERATEALL implements the outer apply
and you see that if I use GENERATEALL over dates,
using the DATESBETWEEN, even though
my dates go from the 6th to the 3rd,
I still have a value coming from the first table with
a blank for the values coming from the second expression.