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.

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.

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:

Regular SQL:

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:

Valentina SQL:

SELECT * 
FROM Person JOIN Phone USING linkPersonPhone
WHERE ...

Or it is even simpler if use an implicit link:

Valentina SQL:

SELECT * 
FROM Person JOIN Phone
WHERE ...

Note, for INNER JOIN can be just FROM Person, Phone.

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:

Regular SQL:

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:

Valentina SQL:

SELECT * 
FROM T1 JOIN T2 JOIN T3           
WHERE ...

Note, for INNER JOIN can be just FROM T1,T2,T3.

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.

Regular SQL

SELECT A.* (SELECT COUNT(*) FROM B WHERE B.ptr = A.ID )
FROM A

Valentina SQL

SELECT A.*, COUNT_LINKED(A, link_name)
FROM A

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.

Valentina provides

• 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

For instance:

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

Valentina SQL

DESCENDANTS OF 1 USING l1;

Read more ...