Syntax

Important notes to read

1. Column names and methods are case sensitive
2. Keywords are not case sensitive.
3. Queries are strongly typed. Types must much.
4. From clause for join syntax must be aliased. You must reference specific join table by using this alias.

Select and Where clause

Select statement gets datas from specific source. As a part of select query, you can use ColumnName, ComplexColumn.Property and also SomeMethod(...). Returning columns or expressions must be aliased when used with set operators or are part of common table expressions. For example:

with Dummy as (
   select 1 + 2 as 'EvaluatedExpression', SomeMethod(SomeColumn) as 'MyMethod', SomeColumn from #dummy.source()
)
select EvaluatedExpression, SomeColumn, MyMethod from Dummmy

in this particular case SomeMethod(SomeColumn) and 1 + 2 must be aliased because they represents complex expression. The other case here are set operators that columns must match on boths sides. Let's look below:

select ColumnName, 1 + 2 as 'EvaluatedExpression', SomeMethod(SomeColumn) as 'MyMethod' from #dummy.source()
union (ColumnName)
select OtherMethod(ColumnName) as ColumnName, 3 + 4 as 'EvaluatedExpression', SomeMethod2(SomeColumn) as 'MyMethod' from #dummy.source()

As you can see, set operators both requires the same name. What is not visible here but is also very important that the expressions on the both sides must return the same types.

From clause

You can see two types of different from form clauses. The first one is from #a.b(param1, ...) and the second one is SomeTableAlias. The basic usages will often use the first one sytnax, the second one you will use for Common Table Expressions. I will describe when and how to use them.

1. While typing a query, you often see syntax like from #a.b(param1, ...). This syntax specify the external data source you will use. The #a part indicates a schema name and b(...) part is indicator how to instantiate row source that feeds evaluator with the datas. As you noted, source is parametrized which allows to configure what datas you would like to receive. For example, while querying operating system drive, probably you wouldn't want to iterate over whole disc rather that only a set of folders important for you. Parametrization gives that ability to flexibly construct what exactly would you like to query. Narrow down your searches to do it faster.

Group by and Having operators

CTE

Usefull language specific features

1. Complex object accessing
2. Like / not like operators
3. RLike / not rlike operators
4. Contains operator
5. In operator

Set operators

Joins support

How do I know what parametrs can I pass to my source?

How do I know what columns can I use?

Each plugin can be queried for columns it has by typing desc #schema.method(...,params). Every single plugin supports it out of the box even if it's third party plugin. See example below.

desc #os.files('path/to/folder', 'false')

may shows

Name	Index	Type
Name	0	String
CreationTime	1	DateTime
FullName	2	String

Which means that the virtual table has 3 columns Name, CreationTime, FullName (Hint: real disk plugin has more columns).

Why must I type parameters for source?

Because the source is computed on the fly. There is no limitation for the plugin to provide different columns while passing different parameters.