SakaQL vs SQL

SakaQL is a relational programming language like SQL, but its query design differs in a few key aspects:

• Universal multisets, no table vs scalar split
• Empty multisets, no NULL or three-valued logic
• Pipelined expressions, not clauses and arcane syntactic rules
• Variables, not CTEs

The examples below show how this affects query ergonomics. They query two tables, authors (id, name) and books (title, genre, author_id), which SakaQL writes as Bags of Records:

def authors = {
  (id: 1, name: "Mina")
  (id: 2, name: "Oren")
  (id: 3, name: "Tala")
}

def books = {
  (title: "Ash and Iron", genre: "Fantasy", author_id: 1)
  (title: "Northwind", genre: "Fantasy", author_id: 2)
  (title: "Glass Harbor", genre: "Mystery", author_id: 1)
}

Literals

SQL

VALUES (1), (2), (3);

SakaQL

{1, 2, 3}

Records

SQL

SELECT 1.5 as x, 2.0 as y
UNION ALL SELECT 3.0, 4.5
UNION ALL SELECT 5.1, 6.8;

SakaQL

{
  (x: 1.5, y: 2.0)
  (x: 3.0, y: 4.5)
  (x: 5.1, y: 6.8)
}

Map

SQL

SELECT b.title, b.genre
FROM books b;

SakaQL

books map (title: .title, genre: .genre)

Filter

SQL

SELECT *
FROM books
WHERE genre = 'Fantasy';

SakaQL

books if .genre == "Fantasy"

Aggregate

SQL

SELECT COUNT(*)
FROM books
WHERE genre = 'Fantasy';

SakaQL

count(books if .genre == "Fantasy")

Indexed Aggregate

SQL

SELECT genre, COUNT(*)
FROM books
GROUP BY 1;

SakaQL

books by .genre aggv count(.)

Views

SQL

CREATE VIEW a(x) AS
VALUES (1), (2), (3);

SELECT x + 1 FROM a;

SakaQL

def a = {1, 2, 3}

a + 1

Subqueries

SQL

SELECT c.v + 1
FROM (
    VALUES (1), (2), (3)
) AS c(v);

SakaQL

{1, 2, 3} map . + 1

Arithmetic over sets

SQL

WITH a(v) AS (VALUES (1), (2), (3)),
     b(v) AS (VALUES (1), (10), (100))
SELECT a.v * b.v
FROM a, b;

SakaQL

{
  let a = {1, 2, 3}
  let b = {1, 10, 100}
  a * b
}

Correlated Map

SQL

SELECT
  a.name,
  (
    SELECT COUNT(*)
    FROM books b
    WHERE b.author_id = a.id
  ) AS total_books
FROM authors a;

SakaQL

authors map a => (
  name: a.name
  total_books: count(books if .author_id == a.id)
)

Correlated Filter

SQL

SELECT a.id, a.name
FROM authors a
WHERE EXISTS (
    SELECT 1
    FROM books b
    WHERE b.author_id = a.id
);

SakaQL

authors
  if a => any(books map .author_id == a.id)
  map (id: .id, name: .name)