Wiki: SQL

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 it 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""