General
In general, the SqlBuilder package has strived to use ANSI SQL92 syntax only (with a few minor deviances).
As of the 2.1.1 release, SqlBuilder supports a syntax customization framework, with a few database specific constructs currently built-in.
As of the 2.1.2 release, some ANSI SQL99 features have been added (newer standards classes will be marked as such, e.g. "SQL 99").
As of the 2.1.7 release, some ANSI SQL2003 features have been added (newer standards classes will be marked as such, e.g. "SQL 2003").
Well, that's not really a question, but we get where you're coming from. Pretty much every method has a "custom" counterpart which can be used to inject pretty much any custom syntax you want. The queries use different converters to interpret the arguments to these methods. In most cases these converters "do the right thing", but if they do not, you generally just need to wrap your custom object in a CustomSql object.
In some SQL dialects, complex queries can be constructed where a select sub-query is used as a "table" like source for an outer query.
Example:
SELECT col1,
col2,
sum(col3) as col3,
avg(col4) as col4
FROM (
SELECT (<complex_expression>) t0.col1,
(<complex_expression>) t0.col2,
(<complex_expression>) t0.col3,
(<complex_expression>) t0.col4
from table t0
)
GROUP BY a, b
SqlBuilder does not support this type of query. However, there is a modern SQL construct known as a Common Table Expression (CTE) which is the now standardized replacement for this legacy syntax. SqlBuilder has full support for CTE queries.
Using this example code:
DbSchema schema = new DbSpec().addSchema("some_schema");
DbTable table = schema.addTable("some_table");
// It is implied that the columns of the table are defined only in one place
DbColumn col1 = table.addColumn("col1","number", null);
DbColumn col2 = table.addColumn("col2","number", null);
DbColumn col3 = table.addColumn("col3","number", null);
DbColumn col4 = table.addColumn("col4","number", null);
SelectQuery innerQuery = new SelectQuery()
.addColumns(col1, col2, col3, col4);
CommonTableExpression cte = new CommonTableExpression("cte")
.setQuery(innerQuery);
Column cteCol1 = cte.addColumn("col1");
Column cteCol2 = cte.addColumn("col2");
Column cteCol3 = cte.addColumn("col3");
Column cteCol4 = cte.addColumn("col4");
SelectQuery outerQuery = new SelectQuery()
.addCommonTableExpression(cte)
.addCustomColumns(cteCol1, cteCol2,
FunctionCall.sum().addColumnParams(cteCol3),
FunctionCall.avg().addColumnParams(cteCol4));]
You can generate this output (modern equivalent to the original query):
WITH cte (col1,col2,col3,col4) AS (SELECT t0.col1,t0.col2,t0.col3,t0.col4 FROM some_schema.some_table t0) SELECT cte0.col1,cte0.col2,SUM(cte0.col3),AVG(cte0.col4) FROM cte cte0