Relation:From SQL to Relation
- 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" |
SELECT DISTINCT Country FROM Customers | read "customers.csv" |
SELECT COUNT(DISTINCT Country) FROM Customers | read "customers.csv" |
SELECT COUNT(DISTINCT Country) FROM Customers | read "customers.csv" |
SELECT * FROM Customers WHERE CustomerID=1 | read "customers.csv" |
SELECT * FROM Customers WHERE City='Berlin' OR City='München' | read "customers.csv" |
SELECT * FROM Customers ORDER BY Country DESC | read "customers.csv" |
SELECT * FROM Customers ORDER BY ASC, CustomerName DESC | read "customers.csv" |
LIKE
SQL | Relation |
---|---|
SELECT * FROM Customers WHERE CustomerName LIKE 'a%' |
read "customers.csv" |
SELECT * FROM Customers WHERE CustomerName LIKE '_r%' |
read "customers.csv" |
SELECT * FROM Customers WHERE CustomerName LIKE 'a%o' |
read "customers.csv" |
NULL
SQL | Relation |
---|---|
ELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL |
read "customers.csv" |
AS
SQL | Relation |
---|---|
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers |
read "customers.csv" |
GROUP
SQL | Relation |
---|---|
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country | read "customers.csv" |
HAVING
SQL | Relation |
---|---|
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 | read "customers.csv" |
IN
SQL | Relation |
---|---|
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); | read "customers.csv" |
second solution | read "customers.csv" |
JOIN
SQL | Relation |
---|---|
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID |
read "orders.csv" |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; |
read "orders.csv" |
While Relation is primarily designed to analyse relations, you can also modify relations.