Use the empty string as a name
The empty string can be used as a column or table name — or both.
with""as(select 0""union select""+1 from""limit 5)select""from""
Specifying a value and column name with one string
If we write a string in double quotes it will be used as both column name and value.
select x from(select"x")
Terminating recursive CTEs (loops)
A recursive CTE will terminate when we generate a row that already exists or when the limit is reached. All of these statements will generate the values 0, 1, 2, 3, 4.
with""as(select 0 x union select-~x from""limit 5)select*from"";
with""as(select 0 x union select x+(x<4)from"")select*from"";
with""as(select 0 x union select x%4+1 from"")select*from"";
with""as(select 0 x union select-~x%5 from"")select*from"";
Division by zero to omit rows
Rows that evaluate to NULL are removed from the output. So these two statements will give the same result.
with""as(select 0 x union select x+1 from""limit 10)select*from""where x%3=0;
with""as(select 0 x union select x+1 from""limit 10)select x/(x%3=0)from"";
Question marks
Instead of writing NULL we may write ?. This is often useful when we don't care about the initial value of a column.
with""as(select?x,'golf'y union select unicode(y),substr(y,2)from"")select x from""