D365 Joins

D365 Joins are essential for querying/fetching data in Microsoft Dynamics 365 Finance & Operations. D365 Joins allow developers to combine data from two or more tables based on a relation.

Types of D365 Joins:

In Dynamics 365 Finance and Operations, there are multiple types of joins you can use to retrieve data from different tables:

1. Inner Join: It is the Default Join, if we don’t put any thing before join, system will automatically consider it as Inner join. It is also the most commonly used join, which returns records only when there is a match in both tables. For example, when fetching sales orders with matching customer records:

select firstonly salesTable
    where salesTable.SalesId == "1234"
        join custTable
    where custTable.AccountNum == salesTable.CustAccount;

Above query will return the matching/common records from both the tables. However, if there is no common records, no records will be returned.

2. Outer Join: In D365 Join, an outer join (which functions like a left join in SQL) retrieves all records from the left table (primary table) even if no matching records are found in the right table (secondary table). This is particularly useful when you want to include records that may not have related entries in another table(Secondary table).

select salesTable
    outer join salesLine
        where salesLine.SalesId == salesTable.SalesId;

Above query will return all sales orders, even those without sales lines. It is very helpful when you want to include primary data regardless of whether it has linked data or not.

3. Exists Join: In D365 Join, an exists join checks for the presence of matching records in the secondary table but does not return data from it. This is more efficient when you only want to confirm if a relationship exists, rather than pull in additional details. This is also used for optimising the a select query.

select salesTable
    exists join salesLine
    where salesLine.SalesId == salesTable.SalesId;

Above query checks whether a sales order has sales lines without returning details from the salesLine table. It improves performance by reducing data retrieval.

4. NotExists Join: This join works oppositely to exists join, pulling data from the primary table where no matching records exist in the related table. This one is very rarely used.

select salesTable
    notexists join salesLine
        where salesLine.SalesId == salesTable.SalesId;

Above query will return sales orders that have no sales lines. It can be useful when identifying orphaned records or transactions needing cleanup.

Simple Example for all the joins:

1. Inner join: The common records of both the tables

2. Outer join: All records of table A and common records of Table B

3. Exist join: common records from Table A

4. Not Exist join: The not common record of Table A

You can also check out my previous blog: D365 Edit method

Need help? Connect Atul

• Author
• Recent Posts

Atul Yadav

Lead Technical Consultant at Lifestyles

As a leading Dynamics 365 F&O Technical lead, Atul brings extensive experience in developing custom solutions that drive business efficiency. His skill set uniquely includes Azure DevOps for Dynamics 365 F&O, enabling efficient project management, automation, and deployment processes. Additionally, his expertise in Power Platform allows for the creation of powerful business solutions that leverage data analysis, app development, and automation.

His proficiency in Azure integration further distinguishes his capability, facilitating seamless connections with Dynamics 365 F&O and enhancing business operations. Committed to project success, he provides ongoing support and shares industry insights through his blog, contributing to the professional community's growth.

Latest posts by Atul Yadav (see all)

• D365 Data entitiy Insert method COC - October 30, 2024
• D365 Joins - October 16, 2024
• D365 Find method and Exist method - October 9, 2024