Skip to content
JQuickJQuick

JQuick-SQL

JQuick-SQL is a lightweight SQL parsing and in-memory query engine driven by a self-developed grammar parser. It parses standard SQL text into a strongly-typed AST, optimizes it through a logical plan, and executes the query directly in-process against datasets you register — no external database connection, no JDBC driver, and no database server to deploy. Built for embedded Java dynamic queries, report-platform computing, and ad-hoc dynamic query scenarios.

Table of Contents

Section titled “Table of Contents”

Introduction

Section titled “Introduction”

JQuick-SQL solves a straightforward problem: your data is already inside the Java process (in-memory rows, API responses, parsed file results), and you want to query it with standard SQL instead of hand-written loops and filters.

It is not a JDBC driver for external databases — it is an in-memory query engine: you register data as tables (registerTable), then use standard SQL for filtering, aggregation, sorting, pagination, and multi-table joins, with results returned as a JQuickDataSet. Typical scenarios:

Scenario Description
Embedded dynamic queries in Java Assemble SQL inside the application and query in-memory datasets directly, no database deployment required
Report platform computing Fetch data first, then run GROUP BY / aggregation / sorting / pagination in-process for report computation
Dynamic query scenarios Frequently changing query conditions (operations, risk control, back-office search) expressed as SQL instead of hard-coded logic
Unit testing Replace a real database with in-memory tables to test SQL logic
SQL auditing & parsing Parse SQL text into a strongly-typed AST and inspect it clause by clause

Workflow: SQL text → self-developed grammar parsing → AST → logical plan → rule-based optimization → physical plan → in-process execution → JQuickDataSet.

Core Features

Section titled “Core Features”
  • Standard SQL support: SELECT, DISTINCT, WHERE, ORDER BY, LIMIT/OFFSET, GROUP BY/HAVING, aggregate functions (COUNT/SUM/AVG/MIN/MAX), JOIN (INNER/LEFT/RIGHT/FULL/CROSS/NATURAL), set operations (UNION/MINUS/INTERSECT), subqueries (EXISTS/IN/scalar subqueries), and CASE WHEN conditional expressions.
  • Rich expression evaluation: comparison, logic (AND/OR/NOT), arithmetic, LIKE, REGEXP, IN, BETWEEN, IS NULL, boolean columns used directly as conditions, constant expressions, and column aliases.
  • 200+ built-in functions: provided by jquick-transform-function, covering string, math, date, conditional, conversion, business-validation, encryption/decryption categories and more. Function names are case-insensitive.
  • Function SPI extension: implement JQuickBaseFunctionFunctionProvider and register via ServiceLoader; custom functions can then be used in any SQL clause just like built-in functions, with @Priority-based overriding.
  • Query optimization: a rule-based optimizer with 12 rules including predicate pushdown, projection pushdown, constant folding, expression simplification, limit pushdown, aggregate pushdown, join reordering, and subquery-to-join conversion.
  • Embedded execution: create the engine with one line — JQuickSQL.embedded(). Coordinator-Worker architecture with hash join, nested-loop join, two-phase aggregation, and Top-N support.
  • XML dynamic SQL: iBatis style — SQL lives in XML files, bound to Java interface methods via #{param} placeholders, with implementations generated by JDK dynamic proxy.
  • Standalone AST parsing: JQuickSQLExecutor parses a SQL string into a strongly-typed AST (30+ node types) in one line, for SQL auditing, statement splitting, and other static analysis.

Quick Start

Section titled “Quick Start”

Create an embedded engine, register an in-memory table, execute SQL — three steps:

// Create embedded SQL engine
JQuickSQL sql = JQuickSQL.embedded();
// Register test data
List<JQuickColumnMeta> columns = Arrays.asList(
    new JQuickColumnMeta("id", Integer.class, "users"),
    new JQuickColumnMeta("name", String.class, "users"),
    new JQuickColumnMeta("age", Integer.class, "users")
);
List<JQuickRow> rows = Arrays.asList(
    createRow("id", 1, "name", "Alice", "age", 25),
    createRow("id", 2, "name", "Bob", "age", 30)
);
sql.registerTable("users", columns, rows);
// Execute SQL
JQuickDataSet result = sql.execute("SELECT * FROM users");
result.printTable();
// Shutdown
sql.shutdown();

Maven Dependency

Section titled “Maven Dependency”
<dependency>
    <groupId>io.github.paohaijiao</groupId>
    <artifactId>jquick-sql-runtime</artifactId>
    <version>5.0.0</version>
</dependency>

jquick-sql-runtime is the entry artifact and transitively brings in jquick-sql-query, jquick-sql-plan, jquick-sql-parser, and jquick-sql-core.

Requirements: JDK 1.8+, Maven 3.x.

Syntax Examples

Section titled “Syntax Examples”

All examples below come from the official demo’s actual execution results. Each example has an Input SQL block and an Output block; the output is the table log printed by JQuickDataSet.printTable().

Basic Queries

Section titled “Basic Queries”

Input data for this section (users table):

id name age status enable addr birthday
1 Alice 25 active true beijing 2020-04-09
2 Bob 30 active true shanghai 1991-08-09
3 Charlie 20 pending false chengdu 1988-07-12
4 David 35 inactive true xian 1955-11-29
5 Eve 28 active true chongqing 2003-07-12
6 Martin 30 active true guangzhou 1978-06-30

Example 1.1: Select all rows and columns

Section titled “Example 1.1: Select all rows and columns”

Returns all columns and all rows of the users table, for inspecting the full dataset.

Input SQL

SELECT * FROM users

Output

[2026-07-23 11:19:19.052] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:19:19.052] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:19:19.052] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:19:19.053] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+

Example 1.2: Select specific columns

Section titled “Example 1.2: Select specific columns”

Returns only the specified columns (id, name, age, status, enable, addr) of the users table.

Input SQL

SELECT id, name,age, status,enable,addr FROM users

Output

[2026-07-23 11:21:39.973] [INFO] +----+---------+-----+----------+--------+-----------+
[2026-07-23 11:21:39.973] [INFO] | id | name    | age | status   | enable | addr      |
[2026-07-23 11:21:39.973] [INFO] +----+---------+-----+----------+--------+-----------+
[2026-07-23 11:21:39.973] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   |
[2026-07-23 11:21:39.973] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  |
[2026-07-23 11:21:39.974] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   |
[2026-07-23 11:21:39.974] [INFO] | 4  | David   | 35  | inactive | true   | xian      |
[2026-07-23 11:21:39.974] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing |
[2026-07-23 11:21:39.974] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou |
[2026-07-23 11:21:39.974] [INFO] +----+---------+-----+----------+--------+-----------+

Example 1.3: Using built-in functions in SELECT

Section titled “Example 1.3: Using built-in functions in SELECT”

Built-in functions such as toUpper() are provided by jquick-transform-function and can be extended via SPI.

Input SQL

SELECT id, toUpper(name) as upperName,age, status,enable,addr,birthday FROM users

Output

[2026-07-23 11:22:48.126] [INFO] +----+-----------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:22:48.126] [INFO] | id | upperName | age | status   | enable | addr      | birthday             |
[2026-07-23 11:22:48.126] [INFO] +----+-----------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:22:48.126] [INFO] | 1  | ALICE     | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 2  | BOB       | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 3  | CHARLIE   | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 4  | DAVID     | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 5  | EVE       | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 6  | MARTIN    | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] +----+-----------+-----+----------+--------+-----------+----------------------+

Example 1.4: Arithmetic expressions and aliases

Section titled “Example 1.4: Arithmetic expressions and aliases”

Supports nested arithmetic expressions, such as computing (age + 1) * 3 on the age column, with column aliasing.

Input SQL

SELECT id, name as upperName,(age+1)*3 as age, status,enable,addr,birthday FROM users

Output

[2026-07-23 11:23:35.632] [INFO] +----+-----------+-------+----------+--------+-----------+----------------------+
[2026-07-23 11:23:35.632] [INFO] | id | upperName | age   | status   | enable | addr      | birthday             |
[2026-07-23 11:23:35.632] [INFO] +----+-----------+-------+----------+--------+-----------+----------------------+
[2026-07-23 11:23:35.632] [INFO] | 1  | Alice     | 78.0  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:23:35.632] [INFO] | 2  | Bob       | 93.0  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 3  | Charlie   | 63.0  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 4  | David     | 108.0 | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 5  | Eve       | 87.0  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 6  | Martin    | 93.0  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:23:35.633] [INFO] +----+-----------+-------+----------+--------+-----------+----------------------+

Example 1.5: CASE WHEN conditional expression

Section titled “Example 1.5: CASE WHEN conditional expression”

Uses CASE WHEN to classify rows by age group.

Input SQL

SELECT id, name, age, CASE WHEN age >= 30 THEN '中年'      WHEN age >= 20 THEN '青年'      ELSE '少年' END AS age_group FROM users

Output

[2026-07-23 11:24:12.889] [INFO] +----+---------+-----+-----------+
[2026-07-23 11:24:12.890] [INFO] | id | name    | age | age_group |
[2026-07-23 11:24:12.890] [INFO] +----+---------+-----+-----------+
[2026-07-23 11:24:12.890] [INFO] | 1  | Alice   | 25  | 青年        |
[2026-07-23 11:24:12.890] [INFO] | 2  | Bob     | 30  | 中年        |
[2026-07-23 11:24:12.890] [INFO] | 3  | Charlie | 20  | 青年        |
[2026-07-23 11:24:12.890] [INFO] | 4  | David   | 35  | 中年        |
[2026-07-23 11:24:12.890] [INFO] | 5  | Eve     | 28  | 青年        |
[2026-07-23 11:24:12.890] [INFO] | 6  | Martin  | 30  | 中年        |
[2026-07-23 11:24:12.890] [INFO] +----+---------+-----+-----------+

Example 1.6: DISTINCT deduplication

Section titled “Example 1.6: DISTINCT deduplication”

Returns deduplicated age values.

Input SQL

SELECT distinct age FROM users

Output

[2026-07-23 11:25:44.641] [INFO] +-----+
[2026-07-23 11:25:44.641] [INFO] | age |
[2026-07-23 11:25:44.641] [INFO] +-----+
[2026-07-23 11:25:44.641] [INFO] | 25  |
[2026-07-23 11:25:44.641] [INFO] | 30  |
[2026-07-23 11:25:44.641] [INFO] | 20  |
[2026-07-23 11:25:44.641] [INFO] | 35  |
[2026-07-23 11:25:44.641] [INFO] | 28  |
[2026-07-23 11:25:44.641] [INFO] +-----+
[2026-07-23 11:25:44.641] [INFO] Total: 5 rows

Example 1.7: Boolean negation expression

Section titled “Example 1.7: Boolean negation expression”

Supports ! to negate a boolean column (e.g. !enable).

Input SQL

SELECT id, toUpper(name) as upperName,age, status,!enable,addr,birthday FROM users

Output

[2026-07-23 11:26:55.089] [INFO] +----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:26:55.089] [INFO] | id | upperName | age | status   | NOT enable | addr      | birthday             |
[2026-07-23 11:26:55.089] [INFO] +----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:26:55.089] [INFO] | 1  | ALICE     | 25  | active   | false      | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:26:55.089] [INFO] | 2  | BOB       | 30  | active   | false      | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:26:55.089] [INFO] | 3  | CHARLIE   | 20  | pending  | true       | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:26:55.090] [INFO] | 4  | DAVID     | 35  | inactive | false      | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:26:55.090] [INFO] | 5  | EVE       | 28  | active   | false      | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:26:55.090] [INFO] | 6  | MARTIN    | 30  | active   | false      | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:26:55.090] [INFO] +----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:26:55.090] [INFO] Total: 6 rows

Example 1.8: Constant expression as an output column

Section titled “Example 1.8: Constant expression as an output column”

Supports constant expressions (e.g. 0 as index) as SELECT fields.

Input SQL

SELECT 0 as index,id, toUpper(name) as upperName,age, status,!enable,addr,birthday FROM users

Output

[2026-07-23 11:27:41.668] [INFO] +-------+----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:27:41.668] [INFO] | index | id | upperName | age | status   | NOT enable | addr      | birthday             |
[2026-07-23 11:27:41.669] [INFO] +-------+----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 1  | ALICE     | 25  | active   | false      | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 2  | BOB       | 30  | active   | false      | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 3  | CHARLIE   | 20  | pending  | true       | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 4  | DAVID     | 35  | inactive | false      | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 5  | EVE       | 28  | active   | false      | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:27:41.670] [INFO] | 0.0   | 6  | MARTIN    | 30  | active   | false      | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:27:41.670] [INFO] +-------+----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:27:41.670] [INFO] Total: 6 rows

WHERE Filtering

Section titled “WHERE Filtering”

Filters rows by specified conditions. Supports comparison operators (=, >, >=, <, <=, <>), logical operators (AND, OR, NOT), NULL checks (IS NULL, IS NOT NULL), range queries (BETWEEN), set membership (IN), pattern matching (LIKE), regular expressions (REGEXP), and subqueries (EXISTS).

Input data for this section (users table):

id name age status enable addr birthday
1 Alice 25 active true beijing 2020-04-09
2 Bob 30 active true shanghai 1991-08-09
3 Charlie 20 pending false chengdu 1988-07-12
4 David 35 inactive true xian 1955-11-29
5 Eve 28 active true chongqing 2003-07-12
6 Martin 30 active true guangzhou 1978-06-30
7 Davila 39 active true null 1999-06-30

Example 2.1: Equality condition

Section titled “Example 2.1: Equality condition”

WHERE column = value — filters by an equality condition, e.g. status = 'active'.

Input SQL

SELECT * FROM users WHERE status = 'active'

Output

[2026-07-23 11:32:33.878] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:32:33.878] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-23 11:32:33.878] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:32:33.878] [INFO] | 1  | Alice  | 25  | active | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 5  | Eve    | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 7  | Davila | 39  | active | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:32:33.878] [INFO] Total: 5 rows

Example 2.2: AND conditions

Section titled “Example 2.2: AND conditions”

WHERE condition1 AND condition2 — returns active users older than 25.

Input SQL

SELECT * FROM users WHERE age > 25 AND status = 'active'

Output

[2026-07-23 11:33:27.498] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:33:27.498] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-23 11:33:27.498] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:33:27.498] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:33:27.498] [INFO] | 5  | Eve    | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:33:27.498] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:33:27.498] [INFO] | 7  | Davila | 39  | active | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:33:27.498] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:33:27.498] [INFO] Total: 4 rows

Example 2.3: OR condition

Section titled “Example 2.3: OR condition”

WHERE condition1 OR condition2 — returns users whose status is pending or who are enabled.

Input SQL

SELECT * FROM users WHERE status = 'pending' OR enable = true

Output

[2026-07-23 11:34:40.825] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:34:40.825] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:34:40.825] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:34:40.825] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:34:40.825] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:34:40.825] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:34:40.825] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:34:40.826] [INFO] Total: 7 rows

Example 2.4: Nested logic with parentheses

Section titled “Example 2.4: Nested logic with parentheses”

WHERE condition OR (condition OR condition) — parentheses group complex logic. Returns users older than 30, or with pending status, or living in chengdu.

Input SQL

SELECT * FROM users WHERE age > 30 OR (status = 'pending' OR addr = 'chengdu')

Output

[2026-07-23 11:35:36.293] [INFO] +----+---------+-----+----------+--------+---------+----------------------+
[2026-07-23 11:35:36.293] [INFO] | id | name    | age | status   | enable | addr    | birthday             |
[2026-07-23 11:35:36.293] [INFO] +----+---------+-----+----------+--------+---------+----------------------+
[2026-07-23 11:35:36.293] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu | 1988-07-11T15:00:00Z |
[2026-07-23 11:35:36.294] [INFO] | 4  | David   | 35  | inactive | true   | xian    | 1955-11-28T16:00:00Z |
[2026-07-23 11:35:36.294] [INFO] | 7  | Davila  | 39  | active   | true   | null    | 1999-06-29T16:00:00Z |
[2026-07-23 11:35:36.294] [INFO] +----+---------+-----+----------+--------+---------+----------------------+
[2026-07-23 11:35:36.294] [INFO] Total: 3 rows

Example 2.5: Constant boolean condition

Section titled “Example 2.5: Constant boolean condition”

WHERE true — a constant boolean condition that returns all rows.

Input SQL

SELECT * FROM users WHERE true

Output

[2026-07-23 11:36:17.974] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:36:17.975] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:36:17.975] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:36:17.975] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:36:17.975] [INFO] Total: 7 rows

Example 2.6: Boolean column as the condition

Section titled “Example 2.6: Boolean column as the condition”

WHERE column — rows whose boolean column evaluates to true are kept; returns all enabled users.

Input SQL

SELECT * FROM users WHERE enable

Output

[2026-07-23 11:37:11.555] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:37:11.555] [INFO] | id | name   | age | status   | enable | addr      | birthday             |
[2026-07-23 11:37:11.555] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:37:11.555] [INFO] | 1  | Alice  | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 2  | Bob    | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 4  | David  | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 5  | Eve    | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 6  | Martin | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:37:11.556] [INFO] | 7  | Davila | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:37:11.556] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:37:11.556] [INFO] Total: 6 rows

Example 2.7: Calling a function in the filter condition

Section titled “Example 2.7: Calling a function in the filter condition”

WHERE function(column) = value — functions can be called in filter conditions. Matches 'ALICE' after converting name to uppercase.

Input SQL

SELECT * FROM users WHERE toUpper(name)='ALICE'

Output

[2026-07-23 11:37:58.226] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:37:58.226] [INFO] | id | name  | age | status | enable | addr    | birthday             |
[2026-07-23 11:37:58.226] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:37:58.226] [INFO] | 1  | Alice | 25  | active | true   | beijing | 2020-04-08T16:00:00Z |
[2026-07-23 11:37:58.226] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:37:58.226] [INFO] Total: 1 rows

Example 2.8: IS NULL

Section titled “Example 2.8: IS NULL”

WHERE column IS NULL — returns users whose addr is null.

Input SQL

SELECT * FROM users WHERE addr is null

Output

[2026-07-23 11:38:36.946] [INFO] +----+--------+-----+--------+--------+------+----------------------+
[2026-07-23 11:38:36.946] [INFO] | id | name   | age | status | enable | addr | birthday             |
[2026-07-23 11:38:36.946] [INFO] +----+--------+-----+--------+--------+------+----------------------+
[2026-07-23 11:38:36.946] [INFO] | 7  | Davila | 39  | active | true   | null | 1999-06-29T16:00:00Z |
[2026-07-23 11:38:36.946] [INFO] +----+--------+-----+--------+--------+------+----------------------+
[2026-07-23 11:38:36.946] [INFO] Total: 1 rows

Example 2.9: IS NOT NULL

Section titled “Example 2.9: IS NOT NULL”

WHERE column IS NOT NULL — returns users whose addr has a value.

Input SQL

SELECT * FROM users WHERE addr is not null

Output

[2026-07-23 11:39:15.906] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:15.906] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:39:15.906] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:15.906] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+

Example 2.10: Comparison operators

Section titled “Example 2.10: Comparison operators”

WHERE column > value — supports >, >=, <, <=, =, <>; returns users older than 25.

Input SQL

SELECT * FROM users WHERE age >25

Output

[2026-07-23 11:39:59.745] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:59.745] [INFO] | id | name   | age | status   | enable | addr      | birthday             |
[2026-07-23 11:39:59.745] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:59.745] [INFO] | 2  | Bob    | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:39:59.745] [INFO] | 4  | David  | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:39:59.745] [INFO] | 5  | Eve    | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:39:59.746] [INFO] | 6  | Martin | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:39:59.746] [INFO] | 7  | Davila | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:39:59.746] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:59.746] [INFO] Total: 5 rows

Example 2.11: BETWEEN range

Section titled “Example 2.11: BETWEEN range”

WHERE column BETWEEN min AND max — returns users aged between 25 and 30.

Input SQL

SELECT * FROM users WHERE age  between 25 and 30

Output

[2026-07-26 15:24:44.552] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-26 15:24:44.552] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-26 15:24:44.552] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-26 15:24:44.552] [INFO] | 1  | Alice  | 25  | active | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-26 15:24:44.553] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-26 15:24:44.554] [INFO] | 5  | Eve    | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-26 15:24:44.554] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-26 15:24:44.554] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-26 15:24:44.554] [INFO] Total: 4 rows

Example 2.12: IN set

Section titled “Example 2.12: IN set”

WHERE column IN (value1, value2, ...) — returns users aged 25 or 30.

Input SQL

SELECT * FROM users WHERE age  in ( 25 , 30)

Output

[2026-07-23 11:41:31.655] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:41:31.655] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-23 11:41:31.655] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:41:31.655] [INFO] | 1  | Alice  | 25  | active | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:41:31.655] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:41:31.655] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:41:31.655] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:41:31.655] [INFO] Total: 3 rows

Example 2.13: NOT IN set

Section titled “Example 2.13: NOT IN set”

WHERE column NOT IN (value1, value2, ...) — returns users whose age is neither 25 nor 30.

Input SQL

SELECT * FROM users WHERE age not in ( 25 , 30)

Output

[2026-07-23 11:42:21.731] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:42:21.731] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:42:21.731] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:42:21.731] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:42:21.731] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:42:21.731] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:42:21.731] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:42:21.731] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:42:21.731] [INFO] Total: 4 rows

Example 2.14: LIKE pattern matching

Section titled “Example 2.14: LIKE pattern matching”

WHERE column LIKE pattern (or NOT LIKE) — % matches any sequence, _ matches a single character; returns users whose name contains ‘Davi’.

Input SQL

SELECT * FROM users WHERE name like '%Davi%'

Output

[2026-07-23 11:43:10.536] [INFO] +----+--------+-----+----------+--------+------+----------------------+
[2026-07-23 11:43:10.536] [INFO] | id | name   | age | status   | enable | addr | birthday             |
[2026-07-23 11:43:10.536] [INFO] +----+--------+-----+----------+--------+------+----------------------+
[2026-07-23 11:43:10.536] [INFO] | 4  | David  | 35  | inactive | true   | xian | 1955-11-28T16:00:00Z |
[2026-07-23 11:43:10.536] [INFO] | 7  | Davila | 39  | active   | true   | null | 1999-06-29T16:00:00Z |
[2026-07-23 11:43:10.536] [INFO] +----+--------+-----+----------+--------+------+----------------------+
[2026-07-23 11:43:10.536] [INFO] Total: 2 rows

Example 2.15: REGEXP matching

Section titled “Example 2.15: REGEXP matching”

WHERE column REGEXP pattern (or NOT REGEXP) — returns users whose name starts with ‘A’.

Input SQL

SELECT * FROM users WHERE name REGEXP '^A.*'

Output

[2026-07-23 11:43:45.932] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:43:45.933] [INFO] | id | name  | age | status | enable | addr    | birthday             |
[2026-07-23 11:43:45.933] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:43:45.933] [INFO] | 1  | Alice | 25  | active | true   | beijing | 2020-04-08T16:00:00Z |
[2026-07-23 11:43:45.933] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:43:45.933] [INFO] Total: 1 rows

Example 2.16: EXISTS subquery

Section titled “Example 2.16: EXISTS subquery”

WHERE EXISTS (subquery) — filters by whether a matching record exists in the subquery; returns users who have at least one order.

Input SQL

SELECT * FROM users u WHERE EXISTS (   SELECT 1 FROM orders o WHERE o.user_id = u.id)

Output

[2026-07-23 12:13:18.994] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 12:13:18.994] [INFO] | id | name    | age | status  | enable | addr     | birthday             |
[2026-07-23 12:13:18.994] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 12:13:18.994] [INFO] | 1  | Alice   | 25  | active  | true   | beijing  | 2020-04-08T16:00:00Z |
[2026-07-23 12:13:18.994] [INFO] | 2  | Bob     | 30  | active  | true   | shanghai | 1991-08-08T15:00:00Z |
[2026-07-23 12:13:18.994] [INFO] | 3  | Charlie | 20  | pending | false  | chengdu  | 1988-07-11T15:00:00Z |
[2026-07-23 12:13:18.994] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 12:13:18.994] [INFO] Total: 3 rows

GROUP BY Aggregation

Section titled “GROUP BY Aggregation”

GROUP BY groups rows by the specified columns and computes per-group values with aggregate functions (COUNT, SUM, AVG, MIN, MAX); HAVING filters the grouped results.

Input data for this section (users table):

id name age status enable addr birthday
1 Alice 25 active true beijing 2020-04-09
2 Bob 30 active true shanghai 1991-08-09
3 Charlie 20 pending false chengdu 1988-07-12
4 David 35 inactive true xian 1955-11-29
5 Eve 28 active true chongqing 2003-07-12
6 Martin 30 active true guangzhou 1978-06-30
7 Davila 39 active true null 1999-06-30

Example 5.1: GROUP BY + aggregates + sorting

Section titled “Example 5.1: GROUP BY + aggregates + sorting”

GROUP BY column, aggregate_function(column) — groups by status, computes the count and average age per group, and sorts by status.

Input SQL

SELECT status, COUNT(*) as count, AVG(age) as avg_age FROM users GROUP BY status ORDER BY status

Output

[2026-07-23 17:02:47.779] [INFO] +----------+-------+---------+
[2026-07-23 17:02:47.780] [INFO] | status   | count | avg_age |
[2026-07-23 17:02:47.780] [INFO] +----------+-------+---------+
[2026-07-23 17:02:47.780] [INFO] | active   | 5     | 30.4    |
[2026-07-23 17:02:47.780] [INFO] | inactive | 1     | 35.0    |
[2026-07-23 17:02:47.780] [INFO] | pending  | 1     | 20.0    |
[2026-07-23 17:02:47.780] [INFO] +----------+-------+---------+
[2026-07-23 17:02:47.780] [INFO] Total: 3 rows

Example 5.2: HAVING filters groups

Section titled “Example 5.2: HAVING filters groups”

GROUP BY ... HAVING condition ORDER BY ... — groups by status, keeps only groups with more than one row, and sorts by count descending.

Input SQL

SELECT status, COUNT(age) as count, AVG(age) as avg_age FROM users GROUP BY status HAVING COUNT(age) >1 ORDER BY count DESC

Output

[2026-07-23 17:04:55.056] [INFO] +--------+-------+---------+
[2026-07-23 17:04:55.056] [INFO] | status | count | avg_age |
[2026-07-23 17:04:55.056] [INFO] +--------+-------+---------+
[2026-07-23 17:04:55.056] [INFO] | active | 5     | 30.4    |
[2026-07-23 17:04:55.056] [INFO] +--------+-------+---------+
[2026-07-23 17:04:55.056] [INFO] Total: 1 rows

Input data for the following 5 examples (users table with a salary column):

id name age status enable addr birthday salary
1 Alice 25 active true beijing 2020-04-09 5000.0
2 Bob 30 active true shanghai 1991-08-09 6000.0
3 Charlie 20 pending false chengdu 1988-07-12 4500.0
4 David 35 inactive true xian 1955-11-29 7000.0
5 Eve 28 active true chongqing 2003-07-12 5500.0
6 Martin 30 active true guangzhou 1978-06-30 6500.0
7 Davila 39 active true null 1999-06-30 8000.0

Example 8.1: COUNT

Section titled “Example 8.1: COUNT”

Groups by status and returns the row count of each group.

Input SQL

SELECT status, COUNT(*) AS count FROM users GROUP BY status

Output

[2026-07-27 07:03:18.201] [INFO] +----------+-------+
[2026-07-27 07:03:18.201] [INFO] | status   | count |
[2026-07-27 07:03:18.201] [INFO] +----------+-------+
[2026-07-27 07:03:18.201] [INFO] | inactive | 1     |
[2026-07-27 07:03:18.201] [INFO] | pending  | 1     |
[2026-07-27 07:03:18.201] [INFO] | active   | 5     |
[2026-07-27 07:03:18.201] [INFO] +----------+-------+
[2026-07-27 07:03:18.201] [INFO] Total: 3 rows

Example 8.2: SUM

Section titled “Example 8.2: SUM”

Groups by status and returns the sum of ages in each group.

Input SQL

SELECT status, SUM(age) AS age_sum FROM users GROUP BY status

Output

[2026-07-27 07:07:19.828] [INFO] +----------+---------+
[2026-07-27 07:07:19.828] [INFO] | status   | age_sum |
[2026-07-27 07:07:19.830] [INFO] +----------+---------+
[2026-07-27 07:07:19.830] [INFO] | inactive | 35.0    |
[2026-07-27 07:07:19.830] [INFO] | pending  | 20.0    |
[2026-07-27 07:07:19.830] [INFO] | active   | 152.0   |
[2026-07-27 07:07:19.830] [INFO] +----------+---------+
[2026-07-27 07:07:19.830] [INFO] Total: 3 rows

Example 8.3: AVG

Section titled “Example 8.3: AVG”

Groups by status and returns the average age of each group.

Input SQL

SELECT status,AVG(age) AS avg_age FROM users GROUP BY status

Output

[2026-07-27 07:10:15.850] [INFO] +----------+---------+
[2026-07-27 07:10:15.850] [INFO] | status   | avg_age |
[2026-07-27 07:10:15.850] [INFO] +----------+---------+
[2026-07-27 07:10:15.850] [INFO] | inactive | 35.0    |
[2026-07-27 07:10:15.850] [INFO] | pending  | 20.0    |
[2026-07-27 07:10:15.850] [INFO] | active   | 30.4    |
[2026-07-27 07:10:15.850] [INFO] +----------+---------+
[2026-07-27 07:10:15.851] [INFO] Total: 3 rows

Example 8.4: MIN

Section titled “Example 8.4: MIN”

Groups by status and returns the minimum age of each group.

Input SQL

SELECT status,MIN(age) AS active_min_age FROM users GROUP BY status

Output

[2026-07-27 07:12:58.509] [INFO] +----------+----------------+
[2026-07-27 07:12:58.509] [INFO] | status   | active_min_age |
[2026-07-27 07:12:58.509] [INFO] +----------+----------------+
[2026-07-27 07:12:58.509] [INFO] | inactive | 35             |
[2026-07-27 07:12:58.511] [INFO] | pending  | 20             |
[2026-07-27 07:12:58.511] [INFO] | active   | 25             |
[2026-07-27 07:12:58.512] [INFO] +----------+----------------+
[2026-07-27 07:12:58.512] [INFO] Total: 3 rows

Example 8.5: MAX

Section titled “Example 8.5: MAX”

Groups by status and returns the maximum age of each group.

Input SQL

SELECT status,Max(age) AS active_max_age FROM users GROUP BY status

Output

[2026-07-27 07:14:15.781] [INFO] +----------+----------------+
[2026-07-27 07:14:15.782] [INFO] | status   | active_max_age |
[2026-07-27 07:14:15.782] [INFO] +----------+----------------+
[2026-07-27 07:14:15.782] [INFO] | inactive | 35             |
[2026-07-27 07:14:15.782] [INFO] | pending  | 20             |
[2026-07-27 07:14:15.782] [INFO] | active   | 39             |
[2026-07-27 07:14:15.782] [INFO] +----------+----------------+
[2026-07-27 07:14:15.782] [INFO] Total: 3 rows

Sorting & Pagination

Section titled “Sorting & Pagination”

ORDER BY supports single-column / multi-column and mixed ascending (ASC) / descending (DESC) sorting. LIMIT n caps the number of returned rows; LIMIT offset, count skips offset rows first and then takes count rows — combinable with ORDER BY for pagination.

Input data for this section (users table):

id name age status enable addr birthday
1 Alice 25 active true beijing 2020-04-09
2 Bob 30 active true shanghai 1991-08-09
3 Charlie 20 pending false chengdu 1988-07-12
4 David 35 inactive true xian 1955-11-29
5 Eve 28 active true chongqing 2003-07-12
6 Martin 30 active true guangzhou 1978-06-30
7 Davila 39 active true null 1999-06-30

Example 3.1: Single-column ascending

Section titled “Example 3.1: Single-column ascending”

ORDER BY column ASC — sorts by age from smallest to largest.

Input SQL

SELECT * FROM users ORDER BY age ASC

Output

[2026-07-23 16:53:29.684] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:53:29.684] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:53:29.684] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:53:29.684] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:53:29.684] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:53:29.684] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:53:29.684] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:53:29.685] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:53:29.685] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:53:29.685] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:53:29.685] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:53:29.685] [INFO] Total: 7 rows

Example 3.2: Single-column descending

Section titled “Example 3.2: Single-column descending”

ORDER BY column DESC — sorts by age from largest to smallest.

Input SQL

SELECT * FROM users ORDER BY age DESC

Output

[2026-07-23 16:54:29.749] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:54:29.749] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:54:29.749] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:54:29.749] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:54:29.749] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:54:29.750] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:54:29.750] [INFO] Total: 7 rows

Example 3.3: Multi-column mixed sorting (ASC + DESC)

Section titled “Example 3.3: Multi-column mixed sorting (ASC + DESC)”

ORDER BY column1 ASC, column2 DESC — sorts by status ascending first, then by age descending within each group.

Input SQL

SELECT * FROM users ORDER BY status ASC, age DESC

Output

[2026-07-23 16:55:13.550] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:55:13.550] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:55:13.550] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:55:13.550] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:55:13.551] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:55:13.551] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:55:13.551] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:55:13.551] [INFO] Total: 7 rows

Example 3.4: Multi-column mixed sorting (DESC + ASC)

Section titled “Example 3.4: Multi-column mixed sorting (DESC + ASC)”

ORDER BY column1 DESC, column2 ASC — sorts by enable descending first, then by age ascending within each group.

Input SQL

SELECT * FROM users ORDER BY enable DESC, age ASC

Output

[2026-07-23 16:56:03.295] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:56:03.295] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:56:03.295] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:56:03.295] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:56:03.296] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:56:03.296] [INFO] Total: 7 rows

Example 3.5: LIMIT offset, count

Section titled “Example 3.5: LIMIT offset, count”

Skips the first 2 rows (offset), then returns 3 rows.

Input SQL

SELECT * FROM users LIMIT  2, 3

Output

[2026-07-28 16:58:34.179] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-28 16:58:34.179] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-28 16:58:34.179] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-28 16:58:34.179] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-28 16:58:34.180] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-28 16:58:34.180] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-28 16:58:34.180] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-28 16:58:34.180] [INFO] Total: 3 rows

Example 4.1: LIMIT n

Section titled “Example 4.1: LIMIT n”

LIMIT n — caps the number of returned rows; returns the first 3 rows.

Input SQL

SELECT * FROM users LIMIT 3

Output

[2026-07-23 16:59:29.806] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 16:59:29.806] [INFO] | id | name    | age | status  | enable | addr     | birthday             |
[2026-07-23 16:59:29.807] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 16:59:29.807] [INFO] | 1  | Alice   | 25  | active  | true   | beijing  | 2020-04-08T16:00:00Z |
[2026-07-23 16:59:29.807] [INFO] | 2  | Bob     | 30  | active  | true   | shanghai | 1991-08-08T15:00:00Z |
[2026-07-23 16:59:29.807] [INFO] | 3  | Charlie | 20  | pending | false  | chengdu  | 1988-07-11T15:00:00Z |
[2026-07-23 16:59:29.807] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 16:59:29.807] [INFO] Total: 3 rows

Example 4.2: LIMIT offset, limit (only the remaining row matches)

Section titled “Example 4.2: LIMIT offset, limit (only the remaining row matches)”

Skips the first 2 rows and returns the following rows (in this run, only 1 row remains after the preceding filter).

Input SQL

SELECT * FROM users LIMIT  2, 3

Output

[2026-07-23 17:00:04.403] [INFO] +----+------+-----+--------+--------+-----------+----------------------+
[2026-07-23 17:00:04.403] [INFO] | id | name | age | status | enable | addr      | birthday             |
[2026-07-23 17:00:04.403] [INFO] +----+------+-----+--------+--------+-----------+----------------------+
[2026-07-23 17:00:04.403] [INFO] | 5  | Eve  | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 17:00:04.403] [INFO] +----+------+-----+--------+--------+-----------+----------------------+
[2026-07-23 17:00:04.403] [INFO] Total: 1 rows

Example 4.3: ORDER BY + LIMIT pagination

Section titled “Example 4.3: ORDER BY + LIMIT pagination”

ORDER BY ... LIMIT offset, limit — sorts first, then paginates. Sorts by age ascending and takes the first 3 rows.

Input SQL

SELECT * FROM users order by age asc LIMIT  0, 3

Output

[2026-07-23 17:00:32.950] [INFO] +----+---------+-----+---------+--------+-----------+----------------------+
[2026-07-23 17:00:32.950] [INFO] | id | name    | age | status  | enable | addr      | birthday             |
[2026-07-23 17:00:32.950] [INFO] +----+---------+-----+---------+--------+-----------+----------------------+
[2026-07-23 17:00:32.951] [INFO] | 3  | Charlie | 20  | pending | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 17:00:32.951] [INFO] | 1  | Alice   | 25  | active  | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 17:00:32.951] [INFO] | 5  | Eve     | 28  | active  | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 17:00:32.951] [INFO] +----+---------+-----+---------+--------+-----------+----------------------+
[2026-07-23 17:00:32.951] [INFO] Total: 3 rows

Multi-Table JOIN

Section titled “Multi-Table JOIN”

Supports INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, NATURAL JOIN, and can be combined with WHERE for filtering.

Input data for this section:

users table

id name age status enable addr birthday
1 Alice 25 active true beijing 2020-04-09
2 Bob 30 active true shanghai 1991-08-09
3 Charlie 20 pending false chengdu 1988-07-12
4 David 35 inactive true xian 1955-11-29
5 Eve 28 active true chongqing 2003-07-12
6 Martin 30 active true guangzhou 1978-06-30
7 Davila 39 active true null 1999-06-30

orders table

id user_id amount
101 1 100.0
102 1 200.0
103 2 150.0
104 3 300.0

Example 6.1: INNER JOIN

Section titled “Example 6.1: INNER JOIN”

INNER JOIN table ON condition — returns only rows whose keys match in both tables; returns users with their order details.

Input SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id

Output

[2026-07-23 22:26:39.908] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:26:39.909] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:26:39.909] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:26:39.909] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:26:39.909] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:26:39.909] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:26:39.910] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:26:39.910] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:26:39.910] [INFO] Total: 4 rows

Example 6.2: INNER JOIN + WHERE

Section titled “Example 6.2: INNER JOIN + WHERE”

Applies additional filtering after the join; returns active users whose order amount is at least 150.

Input SQL

SELECT u.name, u.age, u.status, o.id as order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' AND o.amount >= 150

Output

[2026-07-23 22:27:57.249] [INFO] +--------+-------+----------+----------+----------+
[2026-07-23 22:27:57.249] [INFO] | u.name | u.age | u.status | order_id | o.amount |
[2026-07-23 22:27:57.249] [INFO] +--------+-------+----------+----------+----------+
[2026-07-23 22:27:57.250] [INFO] | Alice  | 25    | active   | 1        | 200.0    |
[2026-07-23 22:27:57.250] [INFO] | Bob    | 30    | active   | 2        | 150.0    |
[2026-07-23 22:27:57.250] [INFO] +--------+-------+----------+----------+----------+
[2026-07-23 22:27:57.251] [INFO] Total: 2 rows

Example 6.3: LEFT JOIN

Section titled “Example 6.3: LEFT JOIN”

LEFT JOIN table ON condition — returns all rows of the left table, with NULL on the right side when there is no match; returns all users with their orders (if any).

Input SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id

Output

[2026-07-23 22:28:58.433] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:28:58.434] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:28:58.434] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:28:58.434] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:28:58.434] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:28:58.434] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:28:58.435] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:28:58.435] [INFO] | 4    | David   | 35    | 4        | null     |
[2026-07-23 22:28:58.435] [INFO] | 5    | Eve     | 28    | 5        | null     |
[2026-07-23 22:28:58.435] [INFO] | 6    | Martin  | 30    | 6        | null     |
[2026-07-23 22:28:58.435] [INFO] | 7    | Davila  | 39    | 7        | null     |
[2026-07-23 22:28:58.435] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:28:58.436] [INFO] Total: 8 rows

Example 6.4: RIGHT JOIN

Section titled “Example 6.4: RIGHT JOIN”

RIGHT JOIN table ON condition — returns all rows of the right table, with NULL on the left side when there is no match; returns all orders with their users (if any).

Input SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id

Output

[2026-07-23 22:30:04.935] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:30:04.935] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:30:04.935] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:30:04.935] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:30:04.935] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:30:04.935] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:30:04.935] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:30:04.936] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:30:04.936] [INFO] Total: 4 rows

Example 6.5: FULL JOIN

Section titled “Example 6.5: FULL JOIN”

FULL JOIN table ON condition — returns all rows of both tables; matching rows are joined, and the non-matching side is NULL.

Input SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u FULL JOIN orders o ON u.id = o.user_id

Output

[2026-07-23 22:31:13.677] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:31:13.679] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:31:13.679] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:31:13.679] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:31:13.680] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:31:13.680] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:31:13.680] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:31:13.681] [INFO] | 4    | David   | 35    | 4        | null     |
[2026-07-23 22:31:13.681] [INFO] | 5    | Eve     | 28    | 5        | null     |
[2026-07-23 22:31:13.681] [INFO] | 6    | Martin  | 30    | 6        | null     |
[2026-07-23 22:31:13.682] [INFO] | 7    | Davila  | 39    | 7        | null     |
[2026-07-23 22:31:13.682] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:31:13.682] [INFO] Total: 8 rows

Example 6.6: FULL JOIN (re-execution check)

Section titled “Example 6.6: FULL JOIN (re-execution check)”

Same FULL JOIN semantics as 6.5; re-executing produces the identical result.

Input SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u FULL JOIN orders o ON u.id = o.user_id

Output

[2026-07-24 08:49:12.518] [INFO] +------+---------+-------+----------+----------+
[2026-07-24 08:49:12.518] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-24 08:49:12.518] [INFO] +------+---------+-------+----------+----------+
[2026-07-24 08:49:12.518] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-24 08:49:12.519] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-24 08:49:12.519] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-24 08:49:12.519] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-24 08:49:12.519] [INFO] | 4    | David   | 35    | 4        | null     |
[2026-07-24 08:49:12.519] [INFO] | 5    | Eve     | 28    | 5        | null     |
[2026-07-24 08:49:12.519] [INFO] | 6    | Martin  | 30    | 6        | null     |
[2026-07-24 08:49:12.519] [INFO] | 7    | Davila  | 39    | 7        | null     |
[2026-07-24 08:49:12.519] [INFO] +------+---------+-------+----------+----------+
[2026-07-24 08:49:12.519] [INFO] Total: 8 rows

Example 6.7: CROSS JOIN

Section titled “Example 6.7: CROSS JOIN”

CROSS JOIN table — returns the cartesian product of both tables (all row combinations), pairing every user with every order.

Input SQL

SELECT u.name, u.age, o.id, o.amount FROM users u CROSS JOIN orders o

Output

[2026-07-24 08:53:28.324] [INFO] +---------+-------+------+----------+
[2026-07-24 08:53:28.324] [INFO] | u.name  | u.age | o.id | o.amount |
[2026-07-24 08:53:28.324] [INFO] +---------+-------+------+----------+
[2026-07-24 08:53:28.324] [INFO] | Alice   | 25    | 101  | 100.0    |
[2026-07-24 08:53:28.324] [INFO] | Alice   | 25    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Alice   | 25    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Alice   | 25    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 104  | 300.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 101  | 100.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 102  | 200.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 103  | 150.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 104  | 300.0    |
[2026-07-24 08:53:28.328] [INFO] +---------+-------+------+----------+
[2026-07-24 08:53:28.328] [INFO] Total: 28 rows

Example 6.8: NATURAL JOIN

Section titled “Example 6.8: NATURAL JOIN”

NATURAL JOIN table — automatically joins on columns with the same name (e.g. id), returning only rows whose same-named column values are equal. In this example the only same-named column is id, which has no equal values across the two tables, so the result is empty.

Input SQL

SELECT u.name, u.age, o.id, o.amount FROM users u NATURAL JOIN orders o

Output

[2026-07-24 14:02:03.903] [INFO] +--------+-------+------+----------+
[2026-07-24 14:02:03.903] [INFO] | u.name | u.age | o.id | o.amount |
[2026-07-24 14:02:03.903] [INFO] +--------+-------+------+----------+
[2026-07-24 14:02:03.903] [INFO] +--------+-------+------+----------+
[2026-07-24 14:02:03.903] [INFO] Total: 0 rows

Built-in Functions

Section titled “Built-in Functions”

JQuick-SQL ships with 200+ built-in functions provided by jquick-transform-function, covering string, date, aggregate, encryption/decryption, type conversion, business validation, and more; custom functions can also be added via SPI. Once registered, functions can be called directly in any clause — SELECT, WHERE, GROUP BY, HAVING, ORDER BY — and function names are case-insensitive.

Category Description Representative functions
String String processing toUpper, toLower, concat, substring, replace, length, trim, mask, md5, base64Encode, reverse, split
Math Math & aggregation abs, ceil, floor, round, max, min, sum, avg, count, sqrt, pow, log, toInt, toDouble, stdDev, median
Date Date & time now, today, addDays, formatDate, day, month, year, isWeekend, daysBetween, startOfMonth, endOfYear
Conditional Conditional logic caseWhen, if, ifElse, coalesce, nvl, between, switch, defaultIfNull
Conversion Type conversion toBoolean, toDate, toDateTime, toShort
Business Business validation & masking bankCardMask, bankCardValidate, idCardValidate, idCardInfo, phoneInfo, phoneMask, emailMask, isEmail
Encryption Encryption/decryption (AES / RSA / ECC) aesEncrypt, aesDecrypt, rsaEncrypt, rsaDecrypt, eccEncrypt, eccDecrypt, aesGenerateKey
Collection Collection operations isEmpty, size, join
Bitwise Bit operations bitAnd, bitOr, bitXor
Geometry Geometry & combinatorics areaCircle, areaRectangle, distance, factorial, gcd, lcm, permutation
Random Random generation randomInt, randomDouble, randomUUID, randomString, randomChoice, randomSample
Extended Formatting & casting cast, formatNumber, parseNumber, toCurrency, toPercentage, typeOf, toArray, toList
JSON JSON processing toJson

All built-in functions can be called directly in SQL:

Input SQL

SELECT toUpper(name) AS upper_name,
       length(addr) AS addr_len,
       formatDate(birthday, 'yyyy-MM') AS birth_month,
       caseWhen(age >= 30, 'senior', 'junior') AS level,
       mask(phoneInfo(addr), '***') AS masked_addr
FROM users

Output

[INFO] +-----------+----------+-------------+--------+-------------+
[INFO] | upper_name | addr_len | birth_month | level  | masked_addr |
[INFO] +-----------+----------+-------------+--------+-------------+
[INFO] | ALICE     | 7        | 2020-04      | junior | ***         |
[INFO] | BOB       | 8        | 1991-08      | senior | ***         |
[INFO] +-----------+----------+-------------+--------+-------------+
[INFO] Total: 7 rows

Java API Usage

Section titled “Java API Usage”

The execution engine: JQuickSQL

Section titled “The execution engine: JQuickSQL”
Method Description
JQuickSQL.embedded(int parallelism) Creates an embedded SQL engine with the given parallelism
JQuickSQL.builder() Creates a Builder for custom configuration
registerTable(String name, List<JQuickColumnMeta> columns, List<JQuickRow> rows) Registers a table
execute(String sql) Executes a SQL query and returns a JQuickDataSet
getTable(String name) Gets a registered table’s data
hasTable(String name) Checks whether a table exists
getRegisteredTables() Gets all registered table names
shutdown() Shuts down the engine

Builder configuration

Section titled “Builder configuration”
JQuickSQL sql = JQuickSQL.builder()
    .embedded(2)           // Use embedded mode with 2 workers
    .parallelism(2)        // Set parallelism
    .table("products", columns, rows)  // Register table
    .build();

Parsing SQL into an AST

Section titled “Parsing SQL into an AST”

When you only need parsing without execution, JQuickSQLExecutor produces a strongly-typed AST in one line:

JQuickSQLExecutor executor = new JQuickSQLExecutor();
JQuickQueryNode ast = executor.execute(
        "SELECT dept, COUNT(*) FROM emp WHERE salary > 20000 GROUP BY dept HAVING COUNT(*) > 1"
);
System.out.println(ast);

XML dynamic SQL

Section titled “XML dynamic SQL”

iBatis style: SQL lives in XML files, the interface is implemented by JDK dynamic proxy, and #{param} placeholders bind method parameters.

XML configuration file (e.g. jquick-sql.xml on the classpath):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqls PUBLIC "-//PAOHAIJIAO//DTD API JAVA 1.0//EN"
        "classpath:paohaijiao/dtd/Jquick-sql.dtd">
<sqls namespace="com.example.service.UserService">
    <sql name="getUsers" returnClass="java.util.List">
        SELECT * FROM users LIMIT #{limit}
    </sql>
</sqls>

Service interface (method names match <sql> name attributes; parameters bind to placeholders via @Param):

package com.example.service;


import com.github.paohaijiao.statement.JQuickDataSet;
import com.github.paohaijiao.xml.param.Param;


public interface UserService {
    JQuickDataSet getUsers(@Param("limit") Integer limit);
}

Register table data and create the service:

// Create table data
JQuickTable table = new JQuickTable("users", getUserColumns(), getUserRows());
JQuickJavaXmlParseFactory handler = new JQuickJavaXmlParseFactory(Arrays.asList(table));


// Create factory with XML configuration
JQuickFactory factory = new JQuickXmlFactory(handler, "jquick-sql.xml");


// Generate service API dynamically
UserService userService = factory.createApi(UserService.class);


// Execute queries
JQuickDataSet dataSet = userService.getUsers(2);
dataSet.printTable();  // Print the result table

XML element reference:

XML element Attribute Description
<sqls> namespace Fully-qualified name of the service interface
<sql> name Method name in the service interface
<sql> returnClass Method return type (e.g. java.util.List, java.lang.Integer)

Parameters bind with the #{paramName} syntax:

<sql name="findUsers" returnClass="java.util.List">
    SELECT * FROM users
    WHERE age > #{minAge}
    AND status = #{status}
    LIMIT #{limit}
</sql>

Custom functions (SPI extension)

Section titled “Custom functions (SPI extension)”

The function manager JQuickMethodInvocationManager is a singleton. At startup it loads all function providers via SPI and sorts them by the @Priority annotation; the startup log prints the loaded count:

[INFO] Loaded 259 functions via SPI

Extending with a custom function takes three steps. Below we implement a jsonPath(json, path) function as an example.

Step 1: Implement the function provider

Extend JQuickBaseFunctionFunctionProvider and implement the function logic in invoke:

package com.github.paohaijiao.provider;


import com.github.paohaijiao.builder.JSONPathQueryBuilder;
import com.github.paohaijiao.exception.JAssert;
import com.github.paohaijiao.function.domain.JQuickBaseFunctionFunctionProvider;
import com.github.paohaijiao.model.JSONObject;
import com.github.paohaijiao.model.JSONPathResult;
import com.github.paohaijiao.spi.anno.Priority;
import com.github.paohaijiao.spi.constants.PriorityConstants;


import java.util.List;


@Priority(PriorityConstants.SYSTEM_HIGH)
public class JQuickJSonPathFunctionFunctionProvider extends JQuickBaseFunctionFunctionProvider {


    public JQuickJSonPathFunctionFunctionProvider() {
        super("jsonPath", "Extract data by JSON path expression - usage: jsonPath(jsonField, pathExpression)");
    }


    @Override
    public Object invoke(List<Object> args) {
        validateArgCount(args, 2);
        Object object = args.get(0);
        JAssert.notNull(object, "para1 require not null");
        String path = (String) args.get(1);
        JSONObject obj;
        if (object instanceof JSONObject) {
            obj = (JSONObject) object;
        } else if (object instanceof java.util.Map) {
            @SuppressWarnings("unchecked")
            java.util.Map<String, Object> map = (java.util.Map<String, Object>) object;
            obj = new JSONObject(map);
        } else {
            throw new IllegalArgumentException(
                    "jsonPath first argument must be a JSON object or row, but was: "
                            + (object == null ? "null" : object.getClass().getName()));
        }
        JSONPathResult result = JSONPathQueryBuilder.from(obj)
                .path(path)
                .execute();
        return result.getRawData();
    }
}

Key points:

  • The constructor super(methodName, description) registers the function name and description; the function name is what SQL calls (case-insensitive).
  • JQuickBaseFunctionFunctionProvider provides argument conversion and validation helpers: asString, asInt, asLong, asDouble, asBoolean, validateArgCount, validateArgCountRange.
  • @Priority(PriorityConstants.SYSTEM_HIGH) declares the priority; other implementations of the same-named function (if any) rank after it.

Step 2: SPI registration

Create a file named com.github.paohaijiao.function.core.JQuickMethodFunctionProvider under src/main/resources/META-INF/services/, containing the fully-qualified implementation class name (one per line, # comments supported):

com.github.paohaijiao.provider.JQuickJSonPathFunctionFunctionProvider

Step 3: Use it in SQL

Once registered, the function is called just like a built-in one:

Input SQL

SELECT name, age, status, enable, jsonPath(detail, '$.position') FROM users

Output

[INFO] +----+---------+-----+----------+--------+--------------------+
[INFO] | id | name    | age | status   | enable | position            |
[INFO] +----+---------+-----+----------+--------+--------------------+
[INFO] | 1  | Alice   | 25  | active   | true   | Senior Developer   |
[INFO] | 2  | Bob     | 30  | active   | true   | Marketing Manager  |
[INFO] | 3  | Charlie | 20  | pending  | false  | Junior Accountant  |
[INFO] | 4  | David   | 35  | inactive | true   | HR Director        |
[INFO] | 5  | Eve     | 28  | active   | true   | Frontend Developer |
[INFO] | 6  | Martin  | 30  | active   | true   | Architect          |
[INFO] | 7  | Davila  | 39  | active   | true   | Tech Lead          |
[INFO] +----+---------+-----+----------+--------+--------------------+
[INFO] Total: 7 rows

Besides SPI auto-discovery, you can also register functions manually in code (useful when registration must happen at runtime):

JQuickMethodInvocationManager manager = JQuickMethodInvocationManager.getInstance();
manager.registerInvoker(new JQuickJSonPathFunctionFunctionProvider());


// Or register a simple function directly as a lambda
manager.registerInvoker("greet", args -> "Hello, " + args.get(0), "Concatenate a greeting", PriorityConstants.USER_HIGH);

Manual registration and SPI loading share the same registry: a later registration does not override an earlier same-named function (use registerOrReplaceInvoker to override). When multiple same-named functions exist, @Priority decides which one is invoked — smaller value wins; the default is 5000 (PriorityConstants.USER_HIGHEST).

Custom Data Sources

Section titled “Custom Data Sources”

JQuick-SQL does not connect to external databases directly. External data is first converted into a JQuickDataSet (JQuickColumnMeta column metadata + JQuickRow rows), then registered into the engine via registerTable / JQuickTable for querying.

With jquick-connector, heterogeneous data sources — relational databases, files, NoSQL, REST APIs — can be ingested as JQuickDataSets, then queried, joined, and aggregated with JQuick-SQL, all without data migration:

<dependency>
    <groupId>io.github.paohaijiao</groupId>
    <artifactId>jquick-connector</artifactId>
    <version>${latest.version}</version>
</dependency>

FAQ

Section titled “FAQ”

Q: Is JQuick-SQL a JDBC driver? Can it connect to MySQL / Oracle?

No. It is an in-memory query engine and provides neither a JDBC dialect layer nor database drivers. Data is registered by the application as datasets (in-memory rows, or row data produced by components like jquick-connector), and SQL executes against those datasets in-process.

Q: Where does the data come from?

Two ways: construct JQuickColumnMeta + JQuickRow in code and call registerTable; or ingest external data sources as JQuickDataSet via jquick-connector.

Q: Are function names case-sensitive?

No. toUpper(name) and TOUPPER(name) are equivalent.

Q: I wrote a custom function but it doesn’t take effect?

The most common cause is a wrong SPI registration file name: the file must be named exactly after the interface FQCN com.github.paohaijiao.function.core.JQuickMethodFunctionProvider, placed under META-INF/services/, with the implementation class FQCN as its content. A wrong file name means ServiceLoader cannot find the implementation.

Q: When multiple implementations of a same-named function exist, which one is called?

They are sorted by the @Priority value — smaller value wins; the default is 5000 (PriorityConstants.USER_HIGHEST) when unannotated. A manually registered same-named function does not override an existing one; use registerOrReplaceInvoker when overriding is needed.

Q: What should I watch out for in embedded mode?

The embedded worker occupies local ports starting from 19001. Always call sql.shutdown() when finished to release them.

Q: Which SQL syntax is supported?

SELECT (DISTINCT, aliases, expressions, CASE WHEN), WHERE (comparison, AND/OR/NOT, BETWEEN, IN, LIKE, REGEXP, IS NULL, EXISTS), JOIN (INNER/LEFT/RIGHT/FULL/CROSS/NATURAL), GROUP BY/HAVING, ORDER BY, LIMIT/OFFSET, set operations (UNION/MINUS/INTERSECT), subqueries, and function calls.

Version Notes

Section titled “Version Notes”
Item Description
Current version 5.0.0
Maven coordinates io.github.paohaijiao:jquick-sql-runtime:5.0.0 (transitively brings in jquick-sql-query, jquick-sql-plan, jquick-sql-parser, jquick-sql-core)
Runtime JDK 1.8+, Maven 3.x
Execution-layer dependencies gRPC 1.80.0 + protobuf-java 3.21.12 + grpc-netty-shaded; serialization via snappy-java

License & Repository

Section titled “License & Repository”

JQuick-SQL is open source under the Apache License 2.0 and free for commercial use.