Page Comparison

...

Table of Contents

In the simplest case, the FROM clause consists of just one class type. However, it can consist of several class types separated by commas.

...

The order in the FROM clause does not play a role. The way the types should be linked is clearly defined in the WHERE clause.

JOIN

In the second example you can already see that the link of several types in the FROM clause can quickly lead to a long and confusing WHERE clause. In addition, there is the keyword JOIN that is essentially only an abbreviating notation for the ID comparison between relation and object types. It is set between two class types of the FROM list instead of a comma. One of the class types must be a relation type and the other an object type. Using JOIN, the WHERE clause is added implicitly by a comparison of the ID of the object type with the first or second ID of the relation type, depending on the order of the object type and relation type. The example just seen can be reformulated in the following way.

...

that is indeed a permissible eSQL that still delivers an empty results table.

Modifiers

If you access a class type with an eSQL query, you can add modifiers to it. These are written directly after the class types in brackets, and separated by a comma in case of several modifiers.

...

The modifiers may only be used in statements in the FROM clause directly after the type name. They must not be used after an alias or a reference.

All Versions

When querying versioned object types, by default only the current versions are taken into account. The ALL VERSIONS modifier is used if you also want to include obsolete versions.

...

SELECT [c]
FROM [dms]:[sysFolder] (ALL VERSIONS) AS [p]
JOIN [dms]:[sysParent2Child] (ALL VERSIONS)
JOIN [dms]:[sysObject] AS [c]
WHERE [p].[sysId] = id'1234567890123456789012345678901A'

No Inheritance

When querying class types, by default all subtypes are also searched. NO INHERITANCE is used if you only want to search in the specified object type.

...

The modifier can only be used for non-abstract types and only has an effect if this type does not have further subtypes. However, non-abstract types that have subtypes are uncommon.

Include Recycle Bins

Normally recycle bins and objects in the recycle bin are not visible. With the modifier INCLUDE RECYCLE BINS, however, an individual recycle bin or all recycle bins can be made visible, if the user has the right to see other users' recycle bins.

Subselects

In previous examples, the FROM clause consisted of a list of class types. As well as class types, subselects are also permitted as a component of a FROM clause. The subselect itself is a valid eSQL query. It must be in brackets and must have an alias.

...