Table of Contents
Valentina SQL Advantages
This section shows some examples of how Valentina's SQL features allow you to resolve various tasks using simpler and shorter SQL query syntax. Simpler queries mean fewer coding errors, less time spent on debugging, faster development, etc.
Implicit Join Specification
Let you have tables T1 and T2 linked as 1:M with the help of a Foreign Key.
The standard SQL query is shown in the left column below. You should specify join and JOIN condition.
Valentina treats a Foreign Key as a kind of Link between tables, and allows you to skip the specifications of JOIN condition if only one link exists between two tables.
|Regular SQL||Valentina SQL|
FROM T1 join T2
BinaryLink M:M vs Third Table M:M
Let you have in an RDBMS two tables linked as Many to Many with help of third table:
It is easy to see that tables Person and Phone reflect objects of a real world, and the third table just plays a role of “MM link”. To do searches on both tables Person and Phone, you should execute two joins for these 3 tables:
SELECT * FROM Person JOIN PersonPhoneMM c ON Person.ID = c.PersonPtr JOIN Phone ON c.PhonePtr = Phone.ID WHERE ...
Now let`s take a look at the design with Binary Link:
It is easy to see that now it needs to specify only one join condition:
SELECT * FROM Person JOIN Phone USING linkPersonPhone WHERE ...
Or it is even simpler if use an implicit link:
SELECT * FROM Person JOIN Phone WHERE ...
Note, for INNER JOIN can be just
FROM Person, Phone.
Three Tables Linked as M:M
To get even better impression from simplification that Valentina offers take a look at the example with three tables T1, T2, T3 linked as Many to Many with help of two MM tables T12 and T23:
SELECT * FROM T1 JOIN T12 ON T1.ID = T12.T1_Ptr JOIN T2 ON T12.T2_Ptr = T2.ID JOIN T23 ON T2.ID = T23.T2_Ptr JOIN T3 ON T23.T3_Ptr = T3.ID WHERE ...
And now Valentina query:
SELECT * FROM T1 JOIN T2 JOIN T3 WHERE ...
Note, for INNER JOIN can be just
Valentina offers a set of functions based on Links. At the same time these functions allow to make queries more simple and natural and even more effective. See the details here.
Let`s assume that we have two tables linked by one of three kind of links (Binary Link, ObjectPtr, Foreign Key). Let we want to show records of table A with information about the count of related records of table B.
SELECT A.* (SELECT COUNT(*) FROM B WHERE B.ptr = A.ID ) FROM A
SELECT A.*, COUNT_LINKED(A, link_name) FROM A
Hierarchical (Recursive) Queries
There are few common approaches to query hierarchical data in SQL world. But all of them either limited to some particular cases or too complicated to be widely used.
Valentina introduces a new way of solving this kind of tasks.
- very simple, natural SQL queries with not verbose syntax
- queries are simple to write and to read
- queries are very effective and fast
- queries control every possible aspect of recursive tasks
Looking for descendants for a first record on all levels
Regular SQL (recursive CTE):
WITH w1( id, ptr, f1, level ) AS ( SELECT id, ptr, f1, 0 AS level FROM t1 WHERE ptr IS NULL UNION ALL SELECT t1.id, t1.ptr, t1.f1, level + 1 FROM t1 JOIN w1 ON t1.ptr = w1.id ) SELECT * FROM w1 WHERE id = 1
DESCENDANTS OF 1 USING l1;