Referencing Relationships in SQL Statements with the RELATE Keyword

USoft has extended the SQL language with the RELATE keyword, which enables you to refer to relationships between tables without having to specify their matching columns. Internally, this functionality is used to display relationships in the SQL Definer.

The syntax is:

RELATE <parent1> "<role1>" <child1> [,<parentn> "<rolen>" <childn>]

The advantage is that you do not have to synchronize these two tables in your SQL statements. Moreover, if the relationship changes (e.g. because columns are added/removed), you do not have to rewrite your constraints.

So instead of having to write:

SELECT e.name

FROM company c, department d, employee e

WHERE c.id = d.company_id

AND e.company_id = d.company

AND (e.dept_id = d.id

OR (e.dept_id IS NULL

AND d.id IS NULL))

AND e.gender = 'F'

AND c.id = 'USOFT'

You can write:

SELECT e.name

FROM company c, department d, employee e

RELATE c HAS d, d "EMPLOYS" e

WHERE e.gender = 'F'

AND c.id = 'USOFT'