Why SQL Struggles with Complex Queries and How SPL Offers a Solution

SQL’s Original Intent and Limitations

SQL was created to make data querying easy for non‑technical users, using English‑like syntax. Simple queries read like English, but as soon as the problem requires several steps, SQL statements become long, nested, and hard to write even for experienced programmers.

Four Fundamental Difficulties of SQL

Lack of Stepwise Execution – Complex calculations must be expressed in a single statement; intermediate results cannot be stored without temporary tables or repeated sub‑queries.

Incomplete Set‑Based Semantics – SQL treats collections as unordered sets and does not provide a native “set of sets” type, forcing extra grouping and aggregation.

Missing Ordered Operations – Ranking, top‑N, or “nth‑record” queries require window functions or self‑joins, making otherwise trivial ordered calculations cumbersome.

No Object Reference Mechanism – Relationships between tables rely on foreign‑key equality, requiring explicit joins or sub‑queries instead of direct attribute navigation.

Illustrative SQL Examples

Finding salespeople who rank in the top 10 for both air‑conditioners and televisions:

select top 10 sales
from sales_amount
where product='AC'
order by amount desc

select top 10 sales
from sales_amount
where product='TV'
order by amount desc

select *
from (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)

Without temporary tables the same logic must be repeated, leading to verbose statements.

Why Ordered and Object‑Oriented Queries Are Hard

Even with window functions, queries become nested sub‑queries. For example, ranking employees by birthday to find the median employee requires a row_number window and a separate sub‑query.

select name, birthday
from (
    select name, birthday,
           row_number() over (order by birthday) as ranking
    from employee
) where ranking = (select floor((count(*)+1)/2) from employee)

Introducing Structured Process Language (SPL)

SPL is an open‑source language designed to address the four SQL shortcomings. It supports explicit stepwise execution, ordered collections, true set‑of‑sets semantics, and direct object references.

SPL Sample Code

Task 1 – Count salespeople in the sales department, then those from Beijing, then female employees:

=employee.select(department=="sales")
=A1.len()
=A1.select(native_place=="Beijing")
=A2.len()
=A2.select(gender=="female")
=A3.len()

Task 2 – Pair a male employee with a female manager in the same department:

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

These examples show how SPL lets developers write clear, step‑by‑step logic without the boiler‑plate required by SQL.

Conclusion

SQL’s design as a set‑based, unordered language makes many everyday analytical tasks verbose and error‑prone. SPL offers a more intuitive, procedural alternative that preserves the power of relational data while simplifying complex queries.