Implicit Joins (Arrow Syntax)
InterSystems SQL provides a special –> operator as a shorthand for getting values from a related table without the complexity of specifying explicit JOINs in certain common cases. This arrow syntax can be used instead of explicit join syntax, or in combination with explicit join syntax. Arrow syntax performs a left outer join.
Arrow syntax can be used for a reference of a property of a class, or a relationship property of a parent table. Other types of relationships and foreign keys do not support arrow syntax. You cannot use arrow syntax (–>) in an ON clause.
You can use arrow syntax in a query involving sharded tables.
For further information, see JOIN.
Property Reference
You can use the –> operator as a shorthand for getting values from a “referenced table.”
For example, suppose you define two classes: Company:
Class Sample.Company Extends %Persistent [DdlAllowed]
{
/// The Company name
Property Name As %String;
}
and Employee:
Class Sample.Employee Extends %Persistent [DdlAllowed]
{
/// The Employee name
Property Name As %String;
/// The Company this Employee works for
Property Company As Company;
}
The Employee class contains a property that is a reference to a Company object. Within an object-based application, you can follow this reference using dot syntax. For example, to find the name of a company that an employee works for:
Set name = employee.Company.Name
You can perform the same task using an SQL statement that uses an OUTER JOIN to join the Employee and Company tables:
SELECT Sample.Employee.Name, Sample.Company.Name AS CompName
FROM Sample.Employee LEFT OUTER JOIN Sample.Company
ON Sample.Employee.Company = Sample.Company.ID
Using the –> operator, you can perform the same OUTER JOIN operation more succinctly:
SELECT Name, Company->Name AS CompName
FROM Sample.Employee
You can use the –> operator any time you have a reference column within a table; that is, a column whose value is the ID of a referenced table (essentially a special case of foreign key). In this case, the Company field of Sample.Employee contains IDs of records in the Sample.Company table. You can use the –> operator anywhere you can use a column expression within a query. For example, in a WHERE clause:
SELECT Name,Company AS CompID,Company->Name AS CompName
FROM Sample.Employee
WHERE Company->Name %STARTSWITH 'G'
This is equivalent to:
SELECT E.Name,E.Company AS CompID,C.Name AS CompName
FROM Sample.Employee AS E, Sample.Company AS C
WHERE E.Company = C.ID AND C.Name %STARTSWITH 'G'
Note that in this case, this equivalent query uses an INNER JOIN.
The following example uses arrow syntax to access the Spouse field in Sample.Person. As the example shows, the Spouse field in Sample.Employee contains the ID of a record in Sample.Person. This example returns those records where the employee has the same Home_State or Office_State as the Home_State of their spouse:
SELECT Name,Spouse,Home_State,Office_State,Spouse->Home_State AS SpouseState
FROM Sample.Employee
WHERE Home_State=Spouse->Home_State OR Office_State=Spouse->Home_State
You can use the –> operator in a GROUP BY clause:
SELECT Name,Company->Name AS CompName
FROM Sample.Employee
GROUP BY Company->Name
You can use the –> operator in an ORDER BY clause:
SELECT Name,Company->Name AS CompName
FROM Sample.Employee
ORDER BY Company->Name
or refer to a column alias for a –> operator column in an ORDER BY clause:
SELECT Name,Company->Name AS CompName
FROM Sample.Employee
ORDER BY CompName
Compound arrow syntax is supported, as shown in the following example. In this example, the Cinema.Review table includes the Film field, which contains Row IDs for the Cinema.Film table. The Cinema.Film table includes the Category field, which contains Row IDs for the Cinema.Category table. Thus Film->Category->CategoryName accesses these three tables to return the CategoryName of each film that has a ReviewScore:
SELECT ReviewScore,Film,Film->Title,Film->Category,Film->Category->CategoryName
FROM Cinema.Review
ORDER BY ReviewScore
Child Table Reference
You can use –> operator to reference a child table. For example, if LineItems is a child table of the Orders table, you can specify:
SELECT LineItems->amount
FROM Orders
Note that there is no property called LineItems in Orders; LineItems is the name of a child table that contains the amount field. This query produces multiple rows in the result set for each Order row. It is equivalent to:
SELECT L.amount
FROM Orders O LEFT JOIN LineItems L ON O.id=L.custorder
Where custorder is the parent reference field of the LineItems table.
Arrow Syntax Privileges
When using arrow syntax, you must have SELECT privileges on the referenced data in both tables. Either you must have a table-level SELECT privilege or a column-level SELECT privilege on the referenced column. With column-level privileges, you need SELECT privilege on the ID of the referenced table, as well as the referenced column.
The following example demonstrates the required column-level privileges:
SELECT Name,Company->Name AS CompanyName
FROM Sample.Employee
GROUP BY Company->Name
ORDER BY Company->Name
In the above example, you must have column-level SELECT privilege for Sample.Employee.Name, Sample.Company.Name, and Sample.Company.ID:
SET tStatement = ##class(%SQL.Statement).%New()
SET privchk1="%CHECKPRIV SELECT (Name,ID) ON Sample.Company"
SET privchk2="%CHECKPRIV SELECT (Name) ON Sample.Employee"
CompanyPrivTest
SET qStatus = tStatement.%Prepare(privchk1)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
IF rset.%SQLCODE=0 {WRITE !,"have Company privileges",! }
ELSE { WRITE !,"No privilege: SQLCODE=",rset.%SQLCODE,! }
EmployeePrivTest
SET qStatus = tStatement.%Prepare(privchk2)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
IF rset.%SQLCODE=0 {WRITE !,"have Employee privilege",! }
ELSE { WRITE !,"No privilege: SQLCODE=",rset.%SQLCODE }