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
| 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 |
| 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$" |
| SQL |
Relation |
ELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL |
read "customers.csv"
select Address !== ''
project CustomerName, ContactName, Address |
| SQL |
Relation |
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers |
read "customers.csv"
project CustomerID, CustomerName
rename CustomerID ID, CustomerName Customer |
| SQL |
Relation |
| SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country |
read "customers.csv"
project CustomerID count, Country |
| 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 |
| 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 |
| 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.
