Skip to main content

Implicit Joins

Caché SQL supports an Implicit Join syntax, represented by the “—>” operator, which simplifies the SQL for certain types of joins. You can use the —> operator whenever a table has a reference column, that is, a column containing the ID values of another table — the referenced table. The —> operator can be used to refer to properties within the referenced table.

For example, the PhoneNumber table contains a Contact column that contains the Contact ID values. The —> operator can be used to refer to rows of the Contact table in SQL operations performed on the PhoneNumber table. The following query returns the value of the Number property for all PhoneNumber rows that both have “Fax” for a PhoneNumberType value and a Contact value that corresponds to a Contact table row with “Public,John Q.” as its Name value.

SELECT Number FROM JavaTutorial.PhoneNumber
WHERE PhoneNumberType='Fax' AND
Contact->Name='Public,John Q.'

The above implicit join query is equivalent to the following standard join:

SELECT Number FROM JavaTutorial.PhoneNumber, JavaTutorial.Contact
WHERE PhoneNumberType='Fax' AND
JavaTutorial.PhoneNumber.Contact = JavaTutorial.Contact.ID AND
JavaTutorial.Contact.Name='Public,John Q.'

To learn more about the Caché implicit join syntax read Implicit Joins (Arrow Syntax) in Using Caché SQL.

FeedbackOpens in a new tab