SQL Operators - Epsio¶

SELECT ¶

SELECT [expression] FROM tbl, ...;

WHERE ¶

WHERE <condition> [AND|OR <condition> ...];

JOIN ¶

[LEFT|RIGHT|INNER] JOIN tbl ON <condition> [AND|OR <condition> ...];

GROUP BY ¶

GROUP BY <expression>, ...;

GROUP BY supports the following aggregations:

COUNT
SUM
MAX
MIN
AVG
ARRAY_AGG
JSON_AGG
STRING_AGG
PERCENTILE_CONT

Note that these aggregations are also available without a GROUP BY expression

UNION/UNION ALL ¶

SELECT ... UNION [ALL] SELECT ...;

ORDER BY ... LIMIT ¶

ORDER BY <expression> [ASC|DESC] [NULLS FIRST|NULLS LAST], ... LIMIT <const>;

To use ORDER BY there must be a LIMIT attached to it, as (similar to Postgres Materialized Views) Epsio does not maintain ordering within the result table since this is not possible in MySQL/Postgres. With a LIMIT, Epsio will ensure the top/bottom x records are within the table, but not their ordering.

DISTINCT ¶

SELECT DISTINCT [ON (<expression>, ...)] ...;

CTEs ¶

WITH <cte_name> AS (SELECT ...), ... SELECT ...;

Subqueries and Views ¶

SELECT ... FROM (SELECT ...) AS <alias>, ...;

SELECT ... FROM <view_name>, ...;

Window Functions ¶

SELECT ... OVER (PARTITION BY ... ORDER BY ... );

Currently supported window functions are:

ROW_NUMBER
LAG
RANK