How joins in X++ select statement are translated into T-SQL – D365FFO – AX

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let’s analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.

1. join in X++:

select AccountNum from custTable join TaxGroupId from custGroup where custGroup.CustGroup == custTable.CustGroup;

CROSS JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.TAXGROUPID, T2.RECID FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T2.CUSTGROUP=T1.CUSTGROUP))

2. outer join in X++:

select AccountNum from custTable outer join AccountID from custBankAccount where custBankAccount.CustAccount == custTable.AccountNum;

LEFT OUTER JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.ACCOUNTID, T2.RECID FROM CUSTTABLE T1 LEFT OUTER JOIN CUSTBANKACCOUNT T2 ON (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT)) WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))

3. exists join in X++:

select AccountNum from custBankAccount exists join custTable where custBankAccount.CustAccount == custTable.AccountNum;

EXISTS (SELECT 'x'...) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID FROM CUSTBANKACCOUNT T1 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND EXISTS (SELECT ‘x’ FROM CUSTTABLE T2 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)))

4. notexists join in X++:

select AccountNum from custBankAccount notexists join custTable where custBankAccount.CustAccount == custTable.AccountNum;

NOT (EXISTS (SELECT 'x'...)) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID FROM CUSTBANKACCOUNT T1 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND NOT (EXISTS (SELECT ‘x’ FROM CUSTTABLE T2 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM))))

5. join after exists join in X++:

select AccountNum from custBankAccount exists join custTable where custBankAccount.CustAccount == custTable.AccountNum join TaxGroupId from custGroup where custGroup.CustGroup == custTable.CustGroup;

EXISTS (SELECT 'x'...CROSS JOIN...) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID FROM CUSTBANKACCOUNT T1 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND EXISTS (SELECT ‘x’ FROM CUSTTABLE T2 CROSS JOIN CUSTGROUP T3 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)) AND (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) AND (T3.CUSTGROUP=T2.CUSTGROUP)))

6. exists join after exists join in X++:

select AccountNum from custBankAccount exists join custTable where custBankAccount.CustAccount == custTable.AccountNum exists join custGroup where custGroup.CustGroup == custTable.CustGroup && custGroup.TaxGroupId == “Std”;

EXISTS (SELECT 'x'... EXISTS (SELECT 'x'...)) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID FROM CUSTBANKACCOUNT T1 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND EXISTS (SELECT ‘x’ FROM CUSTTABLE T2 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)) AND EXISTS (SELECT ‘x’ FROM CUSTGROUP T3 WHERE (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) AND ((T3.CUSTGROUP=T2.CUSTGROUP) AND (T3.TAXGROUPID=?)))))

Conclusion
each exists join is considered as a separate statement, but any join after exists join is coupled or nested.