Relation:From SQL to Relation

Both SQL and Relation are query languages for databases using relational algebra. Here are the main differences

  • Relation works on local data files (CSV or TSV) in a single user environment. While SQL systems can handle local files, they run generally on multi-user servers.
  • SQL is a declarative language in a long sentence. The code is then optimized by the server. Relation is a sequence of relational operations that are executed in the order you program them. This gets you more control.
  • Because Relation is sequential, it has also variables and execution control. Relation is Turing-complete.
  • SQL works with bags. Rows may not be unique. Relation works with sets and rows are always unique. To get that in SQL, you must precede each statement with DISTINCT.
  • SQL works with typed fields. Relation fields are not typed. They behave as number or string based on the operational context

Simple queries

SQL Relation
SELECT * FROM Customers read "customers.csv"
SELECT CustomerName, City FROM Customers read "customers.csv"
project CustomerName, City
SELECT DISTINCT Country FROM Customers read "customers.csv"
project City
SELECT COUNT(DISTINCT Country) FROM Customers read "customers.csv"
project City count
SELECT COUNT(DISTINCT Country) FROM Customers read "customers.csv"
project City count
SELECT * FROM Customers WHERE CustomerID=1 read "customers.csv"
select CustomerID = 1
SELECT * FROM Customers WHERE City='Berlin' OR City='München' read "customers.csv"
select City == "Berlin" or City =="München"
SELECT * FROM Customers ORDER BY Country DESC read "customers.csv"
order Country z
SELECT * FROM Customers ORDER BY ASC, CustomerName DESC read "customers.csv"
order Country a, CustomerName z

LIKE

SQL Relation
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%'
read "customers.csv"
select CustomerName regex "^a"
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%'
read "customers.csv"
select CustomerName regex "^.r"
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%o'
read "customers.csv"
select CustomerName regex "^a.*o$"

NULL

SQL Relation
ELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL
read "customers.csv"
select Address !== ''
project CustomerName, ContactName, Address

AS

SQL Relation
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers
read "customers.csv"
project CustomerID, CustomerName
rename CustomerID ID, CustomerName Customer

GROUP

SQL Relation
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country read "customers.csv"
project CustomerID count, Country

HAVING

SQL Relation
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 read "customers.csv"
project CustomerID count, Country
select CustomerID_count > 5

IN

SQL Relation
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); read "customers.csv"
select Country regex ="^Germany|France|UK$'
second solution read "customers.csv"
relation Country
insert "Germany"
insert "France"
insert "UK"
join natural

JOIN

SQL Relation
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
read "orders.csv"
project CustomerID, OrderID
read "customres.csv"
project CustomerID, CustomerName
join natural
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
read "orders.csv"
project CustomerID, OrderID
read "customres.csv"
project CustomerID, CustomerName
join outer
order CustomerName

While Relation is primarily designed to analyse relations, you can also modify relations.