Why a New Open‑Source Language Is Needed to Replace SQL

Key limitations of SQL

Unordered result sets – SQL treats tables as bags without native ordered collection semantics, making operations such as intersecting an arbitrary number of top‑N lists cumbersome.

Lack of direct ordered access – Ranking or "top N" queries require window functions or rank‑based subqueries, and extracting a range (e.g., rows 6‑10) or a relative offset still needs elaborate logic.

No object‑reference mechanism – Relationships are expressed only through foreign‑key equality, forcing multi‑table joins or nested subqueries to retrieve related attributes.

Illustrative SQL queries that become verbose

To find salespeople who rank in the top 10 for both air‑conditioners and TVs, the straightforward steps are:

Sort by AC sales and select the top 10.

Sort by TV sales and select the top 10.

Intersect the two result sets.

Because early SQL lacked stepwise composition, the query must be written as a single nested statement:

select *
    ( select top 10 sales from sales_amount where product='AC' order by amount desc )
    intersect
    ( select top 10 sales from sales_amount where product='TV' order by amount desc )

When the number of products is unknown, a CTE can only handle a fixed number of intermediate results, so the same logic cannot be expressed without resorting to window functions:

with A as (
    select top 10 sales from sales_amount where product='AC' order by amount desc
),
B as (
    select top 10 sales from sales_amount where product='TV' order by amount desc
)
select * from A intersect B;

Even with window functions, computing the intersection of top‑10 lists for an arbitrary product set requires a query such as:

select sales
from (
    select sales, product,
           rank() over (partition by product order by amount desc) as ranking
    from sales_amount
) t
where ranking <= 10
group by sales
having count(*) = (select count(distinct product) from sales_amount);

Additional examples that illustrate SQL’s difficulty include:

Finding the median birthday by creating a row number and filtering on the middle position.

Calculating the longest consecutive rise in stock prices by counting non‑gain days with windowed CASE expressions.

Counting gender distribution of “good” salespeople, which requires joining the sales result with an employee roster.

All of these queries involve multiple nested subqueries, CTEs, or window functions, and the code quickly expands to dozens of lines.

Structured Process Language (SPL)

SPL is an open‑source language designed to address the three SQL pain points:

True ordered collections that allow direct access by position.

Full collection‑of‑collections semantics, enabling a set of sets without manual flattening.

Object‑reference support, allowing attributes of related entities to be accessed directly.

With these features, the same tasks become concise, readable expressions.

Example: top‑10 AC and TV salespeople

select *
    ( select top 10 sales from sales_amount where product='AC' order by amount desc )
    intersect
    ( select top 10 sales from sales_amount where product='TV' order by amount desc )

Other SPL snippets

Median birthday:

=employee.sort(birthday)
= A1((A1.len()+1)/2)

Longest consecutive stock‑price rise:

=stock_price.sort(trade_date)
=0
=A1.max(A2=if(close_price>close_price[-1],A2+1,0))

Gender filter using object references:

=employee.select(gender=="male" && department.manager.gender=="female")

SPL also provides an IDE with step‑by‑step debugging, a standard JDBC driver for Java integration, and the source repository at https://github.com/SPLWare/esProc.