Joins In Microsoft Dynamics Ax

There are basically four types of joins in Ax,

1.  Inner Join
2. Outer Join 
3. Exists Join
4. Notexists Join

Inner Join : Inner Join will return records from both Outer table and Inner table, only the records which are available in Inner table. Inner Join will also return duplicate records.

Outer Join : Outer Join will return all the records from both Outer table and Inner table. Outer Join will also return duplicate records.

Exists Join : Exists Join will return records only from Outer table which are available in Inner Table. It will not return any duplicate records. 

NotExists Join : NotExists Join will return records only from Outer table which are not available in Inner Table. It will not return any duplicate records.

So now you are clear with the definitions of multiple types of joins in ax. To understand how practically it works use the following steps.

• First of all create two tables in Dynamics Ax Demo_Outer and Demo_Inner.
• In Demo_Outer table create two fields
• Account (PK)
• Name
• In Demo_Inner table create two fields
• Account (FK)
• Phone
• Insert some records in both the tables

To test the joins create a job and fetch records from tables by using code.

Inner Join : Write the following code to test inner join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
          join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }

}

Inner Join Output :

Inner Join

Outer Join : Write the following code to test outer join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       outer join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }
}

Outer Join Output : 

Outer Join

Exists Join :  Write the following code to test exists join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       exists join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }
}

Exists Join Output : 

Exists Join

NotExists Join : Write the following code to test notexists join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       notexists join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }

}

NotExists Join Output : 

NotExists Join

                  Thank You !