I get to hear a lot a question about CDS Views and that is: "What's up with the Associations?"
When showing someone for the first time one, the first thing he will notice is the association instead of a join. "Why not just adding there a join?" and for sure, his questions is legit. Associations have a lot of similarities with joins. Let us dive into the difference between the two.


SQL Joins

I assume you are already familiar with all the join types and what they do it doesn't make sens for me to spend a lot of time on this. Below I created a small CDS View example that has an inner join. A classic Header - Items scenario.

As expected, the result of joining the two tables will be a set of data consisting of Order Headers and their Items. The select will gather all the requested data from both tables. That means, even if a business user is interested only in header information the view still fetch data from both tables. To overcome this performance issue, CDS Views bring something new to the table, associations.

Associations

Ok, so with this out of the way let's tackle the main question. What do the Associations bring new?
Associations are a different type of gathering data toghether from multiple tables. We call them Join-on-demand. This means that the data in the joined (associated) tables is not accessible directly at run time, but only on demand. It is triggered only when the user needs that data. This is quite neat. Performance-wise, this can be a game changer.

First, here is the code inside the CDS Views I created.

By running the above code we will see only the 3 columns we select. Right click on one of them we will see the option "Follow Association". This is the path we exposed by writing the association syntax in the CDS View. The data related to the items it is now retrieved only when the user access this path (and only for the association key defined).

This behaves like an OUTER JOIN, but it can be influenced with small syntax manipulation. For example, adding inner in square brackets after publicly exposing the association.

{
  key header.node_key       as OrderKey,
      header.so_id          as OrderId,
      header.overall_status as Status,

      item[inner]
}

There is a special (not so special) type of associations, called AD-HOC Association. These type of associations are written the same way a JOIN would be, and as well, behave the same way a JOIN would do. More specifically, an OUTER JOIN. Since it is not helping with anything, I prefer not to use when I can just a type of JOIN and control exactly how I want the outcome of the CDS View.

I hope this clears the confusion between the two syntax statements.