D365 Outer Join In X++

In the previous article, I explained how to use D365 joins to combine rows from two ore more tables together. In a continuation of that article, learn how to use D365 outer join, exists join, and NotExists join to retrieve data in some more complex scenarios. Being able to efficiently pull in the exact data you need is crucial to working in Microsoft Dynamics 365 for Finance and Operations.

X++ D365 Joins And Their Differences

As a reminder, in the X++ language, developers can write code that looks very similar to T-SQL code to retrieve data. And load it into a table buffer variable for further use. This includes using joins. However, X++ joins really only support these four types of joins. See the summary below. Afterwards, I will go into further detail and show examples.

• Join – This is the same as an ‘inner join’ in T-SQL. However, in the X++ language the word ‘inner’ is not written. This returns a row when there is a match in both tables.
• Outer Join – D365 outer join is the same as a ‘Left Join’ in T-SQL. This will return all rows from the left table, even if there is not a match in the right table. Note: There is not ‘right join’ in x++. However, a developer can just change which table is first, and which table is second to come up with the same result as a ‘right join’.
• Exists join – In T-SQL, there is an EXISTS keyword. However, it is not used as a ‘join’. In X++, this word is used to achieve a similar effect. But, it used differently. See example below. Using the exists join will return records from the first table, only if there exists a record in the second table. Note: It will NOT return any records from the second table. And importantly, if there are many records in the second table, the number of rows in the result set will not increase. The system will stop looking after it finds one match in the second table.
• NotExists join – This returns a row in the first table, if there does NOT exist a match in the second (or joined) table.

The best way to learn is to look at and play with some examples.

Join

Please see the previous article for examples and explanation on how to use a Join in a select or while select statement.

Outer Join

D365 outer join is the same as a ‘Left Join’ in T-SQL. This will return all rows from the left table, even if there is not a match in the right table. Note: There is not ‘right join’ in x++. However, a developer can just change which table is first, and which table is second to come up with the same result as a ‘right join’.

Example Of Related Records That Only Sometimes Match

Let us talk through an example. Some items in D365 have what are called ‘variants’ or ‘product dimensions’. And some do not. To explain, think of a shirt. When purchases the specific shirt, there may be multiple ‘size’ options. Similarly, there might also be multiple ‘color’ options. Therefore, you could buy a Large Black version of that shirt. And you could buy a Blue Small version of that shirt.

In D365, the itemId of the shirt is stored in the table named InventTable. The specific size and color combination options are stored in a table named inventDimCombination. The thing is, that not all items have a size and color combination. Some do. Others do not. It all depends on how the item is setup.

For many items, they just do not come in more than one variant. Think of a book. Unless it comes in hard cover and soft cover, it may not have further options to choose from when purchases.

In this example, pretend that you wish to display all of the items in the system. You would need to loop through the table InventTable. But let’s also pretend that you wish to see every option, also called a variant, to choose from on every item that has options. For that you would need to join to the inventDimCombination table.

However, there is where things get interesting. If you use a ‘join’, also called an ‘inner join’, to get data from inventDimCombination, you will ONLY display items that have variants. That is not what we want. We want to display all the items. Whether they have variants or not. To do that, we need to use ‘outer join’. And ‘outer join’ in x++ works like a ‘left join’ or ‘left outer join’ in SQL.

SQL Left Outer Join

Instead of looking at the X++ for a D365 outer join first, let’s look at the SQL code first. This is often the order I recommend you write your code. The reason is that when you write SQL code you can use SQL Server Management Studio to quickly see the results and your query.

First, open SQL Server Management Studio on a Cloud Hosted development machine. Second, click ‘ok’ on the dialog box that opens. Third, change the drop-down to be ‘AxDB’. This selects the proper database. Fourth, click the ‘New Query’ button. A new code editor window will open.

Fifth, copy the following SQL code into the code editor window.

select 
it.ITEMID
,it.NAMEALIAS
, idc.INVENTDIMID
, idc.*

from inventTable it
left join INVENTDIMCOMBINATION idc
on idc.DATAAREAID = it.DATAAREAID
and idc.ITEMID = it.ITEMID

where it.DATAAREAID = 'usrt'
and it.ITEMID in ('0179','91012','0178','0185','0141')

Finally, click the ‘Execute’ button. You can also push the keyboard shortcut F5.

The results of the query will show in the ‘Results’ window that opens.

In the above query, we select specific items from the inventTable. We then left outer joined to the inventDimCombination table.

To say it another way, we selected specific rows from the inventTable. Then, if there existed one or more related record in the table inventDimCombination, we shows those records to.

The first two columns show the itemId and the nameAlias columns from the inventTable. But the rest of the columns are from the inventDimCombination table. Notice that sometimes there exist values in these columns. And other times we get the value ‘NULL’.

NULL is a coding term for ‘there is no data there. It is different than storing the value of an empty string or a zero. In this case there is no corresponding record in the inventDimCombination table for itemID’s 0185, 0178, 91012, and 0179, and so the results show ‘NULL’.

If we were to change the ‘left join’ to be ‘join’, all the records with ‘NULL’ would not longer show. Because according to the rules of a ‘join’, only when records exist in BOTH tables, should we include the result. Whereas with an ‘outer join’, the system will always records found in the first table. And depending on whether there is a match in the second table, the system will either show that data, or show ‘NULL’.

This type of join is exactly what you need when two tables ‘sometimes’ relate, but necessarily on every record.

D365 Outer Join In X++

Let’s look at the same query using X++. The code for a D365 outer join looks like this:

        InventTable inventTable ;
        InventDimCombination inventDimCombination;
        
        while select inventTable
            where inventTable.ItemId == '0179'
                || inventTable.ItemId == '91012'
                || inventTable.ItemId == '0141'
            outer join inventDimCombination
            where inventDimCombination.ItemId == inventTable.ItemId
        {
            Info(strFmt("ItemId %1 with name %2 has inventDimCombination with InventDimId %3."
                    ,inventTable.ItemId
                    ,inventTable.NameAlias
                    ,inventDimCombination.InventDimId)
                );
        }

Notice, in SQL we use the words ‘left join’ or ‘left outer join’. However, in x++, we use the words ‘outer join’. When the x++ code actually generates SQL behind the scenes it will use a ‘left join’.

Also notice, in SQL, I can use the keyword ‘IN‘ to provide a list of values I am filtering on. Whereas in X++ that keyword is not supported directly using this type of syntax. So instead I need to use a ‘where’ clause and ‘||’, which means ‘or’.

To summarize, the above x++ query will display the itemId, nameAlias, and inventDimId, if it exists, of all items I am filtering on. Notice that when the does not exist an inventDimCombination record for a particular itemId, then the inventDimCombination table buffer will be empty. However, instead of containing ‘null’ values, the system just stores empty strings or zeroes depending on the data type.

The results look like this:

Exists Join

In T-SQL, there is an EXISTS keyword. However, it is not used as a ‘join’. In X++, this word is used to achieve a similar effect. But, it used differently. See the example below. Using the exists join will return records from the first table, only if there exists a record in the second table. Note: It will NOT return any records from the second table. And importantly, if there are many records in the second table, the number of rows in the result set will not increase. The system will stop looking after it finds one match in the second table.

Exists Join Example

To explain, pretend that you wish to display a list of all salesID’s of all the sales orders that have at least one sales line on the order. You could query the salesTable and then ‘join’ to the salesLine. However, when you use the ‘join’ statement, the number of rows in the result set will increase to be one for every salesLine record. This would result in the same salesId being show over and over again.

Explaining How They Work

You could use a ‘group by’, to try to solve this. This is not something we have covered yet. But instead, it is best to use an ‘exists join’. Look at the following X++ code example.

        SalesTable salesTable;
        SalesLine salesLine;
        
        while select salesTable
            where salesTable.SalesId == "012506"
            exists join salesLine
            where salesLine.SalesId == salesTable.SalesId
        {
            Info(strFmt("Sales order %1 has salesLine with ItemId %2."
                    ,salesTable.SalesId
                    ,salesLine.ItemId)
                );
        }

The system will loop through all salesTable record where one or more salesLine record is found. But it will NOT return a record for every salesLine record, like a ‘join’ would.

That said, there a couple important things to remember.

1. The table that you are doing the ‘exists join’ to, will not return any data into the table buffer. So in the above example the salesLine table buffer will be empty. And when the code tries to display the salesLine.ItemId it will display a blank value. So the above ‘Info’ statement is not really correct.
2. Once you use an ‘exists join’, any ‘joins’ you have afterwards need to also be ‘exists join’. If you try to do a ‘join’, it will still act like an ‘exists join’, and you will not get the expected results. Therefore, you need to make sure all of your ‘exists join’ statements are last in your query.

Performance

Additionally, exists joins can be a great tool for improving performance. This next piece is definitely hard to explain. I will do my best, but do not worry if you do not understand this next piece at first.

Whenever you write a query an ‘join’ to another table, ask yourself two questions.

1. First, are you joining two tables in a way that would result in a one to one relationship? Meaning that for every record in the first table, you will always only return one record in the second table? For example, salesTable relates to custTable in this matter. For every salesTable there always exists a custTable record where salesTable.custAccount equals the custTable.AccountNum.
2. Secondly, do you need to read any of the values from the second table? Or are you joining to it as a way of filtering what rows you want to return from the first table?

To summarize, if you have a join that you know by the indexes on the two tables to be a one to one join, and you do not need to ‘read’ any of the values from the second table, you likely can change it to be an ‘exists’ join. You need to be very careful when you do this, as this can be easy to mess up. But the benefit is that the query may run a lot faster than using a ‘join’.

The reason is that the system can stop reading from the second table as soon as it finds one. It does not need to needlessly keep searching through the entire table, when you know, based on the indexes on the table, that there will only ever be one match per record in the first table.

NotExists Join

This type of query essentially works the opposite of exists join. This returns a row in the first table, if there does NOT exist a match in the second (or joined) table.

For example, let’s pretend you want to display a list of all salesID’s on open sales orders where there does NOT exist any sales lines. Perhaps you want to delete all of these empty sales orders.

Write the following X++ code:

        SalesTable salesTable;
        SalesLine salesLine;
        
        while select salesTable
            where salesTable.SalesStatus == SalesStatus::Backorder //Open
            notexists join salesLine
            where salesLine.SalesId == salesTable.SalesId
        {
            Info(strFmt("Sales order %1 has salesLine with ItemId %2."
                    ,salesTable.SalesId
                    ,salesLine.ItemId)
                );
        }

The system will loop through all sales orders with a sales status of ‘backorder’ which refer to the ‘open order’ sales orders. And the system will only return records where there does not exist any records in the salesLine table where the salesLine.salesId value matches the salesTable.salesId.

Additionally, this type of query can be very useful when you are writing an insert_recordset to create records that do not exist yet. I have not covered this syntax yet. But imagine you have a batch job that runs periodically. As as part of this batch job, you want the system to see new records that a user has created, and create additional records in a related table. Using a ‘notexists join’ will help tell you which records still need a related record created.

Conclusion

Over the course of the past two articles, I have covered the different types of joins in Microsoft Dynamics 365 for Finance and Operations. You have learned about D365 outer join, exists join, and notexists joins. While these joins ultimately result in SQL code being run against the database, the syntax of the x++ joins are a little different. Therefore, it is important to understand the differences.

Ultimately developers who understand both X++ select statements and T-SQL queries, will be the most effective. They can use SQL Server Management Studio to quickly test and view the results of complex queries. And then translate them into X++ select and while select statements for use in the system.

Working with data is one of the most common operations any developer will do in D365. Therefore, learning and practicing these queries until they are very familiar is very important.

Peter Ramer

Peter Ramer is a part of the Managed Application Services team at RSM working on Microsoft Dynamics 365. He focuses on the Retail and Commerce industries. When he is not solving problems and finding ways to accelerate his clients' business, he enjoys time with his three kids and amazing wife.