Tuesday 22 December 2015

JOINS in Dynamics AX and SQL



Inner: Combine records into one table only when there are matching values in a common field.
Outer: Combine records into one table even if there are no matching values in the common field.
Exists: Combine records from one table whenever a value exists in a common field in another table.
Not Exists: Combine records from one table whenever a value in a common field in another table does not exist.




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=?)))))

No comments:

Post a Comment