Joining Dataverse Tables with SQL – INNER JOIN, LEFT JOIN and Multi-Table Queries

So far in this series, we have looked at how SQL can be sent through the Dataverse Web API and how familiar constructs such as SELECT, WHERE, LIKE, IN, and ORDER BY can be used to retrieve and filter data.

Those examples mostly dealt with a single Dataverse table.

But real-world Dataverse implementations rarely work that way.

An Account has Contacts.

An Account can have Opportunities.

A Contact may belong to an Account.

A table may even reference another row from the same table.

This means that sooner or later, our query needs to cross table boundaries.

If you have worked extensively with FetchXML, your mind might immediately go to:

<link-entity>

If you are working with OData, you may start thinking about relationships and $expand.

But with SQL through the Dataverse Web API, we get something much more familiar to developers coming from a relational database background:

JOIN

More specifically, Dataverse SQL supports:

INNER JOIN
LEFT JOIN

And this is where SQL through the Dataverse Web API starts getting particularly interesting.

Let’s Start with Accounts and Contacts

Consider a very common Dataverse relationship.

We have:

Account
|
+---- Contact
|
+---- Contact
|
+---- Contact

Suppose we want to retrieve Accounts together with their related Contacts.

Using SQL, we can write:

SELECT
a.name,
c.fullname,
c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid

If you have worked with SQL before, this is immediately recognisable.

We have given the Account table an alias:

account AS a

and the Contact table another alias:

contact AS c

Then we establish the relationship between them:

ON a.accountid = c.parentcustomerid

Finally, we select columns from both tables:

a.name
c.fullname
c.emailaddress1

So instead of retrieving Accounts first and then separately retrieving their Contacts, the relationship becomes part of the query itself.

What Does INNER JOIN Mean Here?

There is an important behaviour to understand with INNER JOIN.

Consider these records:

Adventure Works
├── John Smith
└── Sarah Jones
Contoso
└── David Brown
Fourth Coffee
└── No Contacts

If we run:

SELECT
a.name,
c.fullname
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid

we are asking for rows where the Account-to-Contact relationship exists.

Conceptually, our result might look something like:

Adventure Works John Smith
Adventure Works Sarah Jones
Contoso David Brown

But what about Fourth Coffee?

If Fourth Coffee doesn’t have a related Contact matching the join condition, it doesn’t appear in the INNER JOIN result.

That is normal relational SQL behaviour and is important when deciding which type of join represents our business requirement.

Sometimes, however, we still want Fourth Coffee.

That’s where LEFT JOIN comes in.

Keeping Accounts Even When There Is No Contact

Suppose our requirement changes slightly.

We want:

Give me every Account and show its Contacts where they exist.

That is different from:

Give me Accounts that have Contacts.

We can express that using:

SELECT
a.name,
c.fullname
FROM account AS a
LEFT JOIN contact AS c
ON a.accountid = c.parentcustomerid

Now the Account is retained even when no matching Contact exists.

Conceptually:

Adventure Works John Smith
Adventure Works Sarah Jones
Contoso David Brown
Fourth Coffee NULL

That difference between INNER JOIN and LEFT JOIN is small syntactically:

INNER JOIN

versus:

LEFT JOIN

but it can completely change the meaning of the query.

When writing Dataverse SQL, we therefore need to think about the business requirement before choosing the join.

Do I only want records where the relationship exists?

Or do I want the base records regardless of whether the related record exists?

We Aren’t Limited to Two Tables

Accounts and Contacts are a good starting point, but Dataverse SQL isn’t limited to a single relationship.

Microsoft’s documentation shows that multiple tables can be joined.

For example:

SELECT
a.name,
c.fullname,
o.name AS opportunity_name
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
INNER JOIN opportunity AS o
ON a.accountid = o.customerid

Now we are crossing three Dataverse tables:

             Account
              /   \
             /     \
            ↓       ↓
        Contact   Opportunity

For a Dataverse developer, this is where the SQL approach becomes interesting.

We can look at the query and immediately understand:

Start from Account
→ Join Contact using the Account relationship
→ Join Opportunity using the Account relationship
→ Return columns from all three

The query itself describes both the data we want and the relationships required to retrieve it.

A More Practical Example

Let’s make the requirement slightly more realistic.

Imagine we’re building an integration that needs Account information together with related Contacts and Opportunities.

The consuming application needs:

  • Account name
  • Contact name
  • Contact email address
  • Opportunity name

The SQL could look like:

SELECT
a.name AS account_name,
c.fullname AS contact_name,
c.emailaddress1 AS contact_email,
o.name AS opportunity_name
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
INNER JOIN opportunity AS o
ON a.accountid = o.customerid

Aliases make the result easier for the consuming application to understand.

Instead of potentially ambiguous fields such as:

name
fullname
name

our output describes what each value represents:

account_name
contact_name
contact_email
opportunity_name

This becomes increasingly useful as the number of tables involved in a query grows.

Joining a Table to Itself

There is another interesting scenario supported by Dataverse SQL: self joins.

Accounts provide a good example because an Account can have a Parent Account.

Imagine this hierarchy:

Contoso Group
|
+---- Contoso Australia
|
+---- Contoso New Zealand

Both the parent and child records live in the same account table.

So how do we join account to account?

We use aliases to treat the table as two logical participants in the query:

SELECT
child.name AS account,
parent.name AS parent_account
FROM account AS child
INNER JOIN account AS parent
ON child.parentaccountid = parent.accountid

Here:

child

and:

parent

both refer to the Account table.

The aliases simply tell us which role each instance is playing in the relationship.

This is a nice example of why aliases aren’t just about shortening table names.

They can help express the meaning of the relationship itself.

But Dataverse JOINs Have Rules

Up to this point, the syntax looks like standard SQL.

This is where we need to remember something that has come up throughout this series:

Dataverse SQL looks familiar, but it has its own boundaries.

The JOIN ... ON clause has some specific restrictions.

The main relationship condition needs to use an equality comparison between columns from the two tables.

For example:

INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid

That works.

But we can’t simply put an arbitrary condition in the ON clause.

For example, this isn’t a supported join:

SELECT
a.name,
c.fullname,
c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
ON c.emailaddress1 LIKE 'B%'

Why?

Because we haven’t actually defined a column relationship between Account and Contact.

The join needs to establish the relationship between the two tables.

The Relationship Comparison Must Use Equality

Consider this:

SELECT
a.name,
c.fullname
FROM account AS a
INNER JOIN contact AS c
ON a.accountid <> c.parentcustomerid

That isn’t supported either.

The join between columns must use:

=

So our relationship should look like:

a.accountid = c.parentcustomerid

rather than trying to use:

<>
>
<

or other comparison operators to establish the join.

Can We Add Additional Filters to a JOIN?

Yes.

But this is where things get more specific.

Suppose we only want related Contacts whose names start with A.

We can write:

SELECT
a.name,
c.fullname,
c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
AND c.fullname LIKE 'A%'

Notice what we’re doing.

First we establish the relationship:

a.accountid = c.parentcustomerid

Then we add another condition:

c.fullname LIKE 'A%'

The additional filter is combined with the relationship condition using:

AND

That is a supported pattern.

Where the Additional Filter Is Applied Matters

Now consider this:

SELECT
a.name,
c.fullname
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
AND a.name LIKE 'A%'

It might look reasonable.

But according to the documented Dataverse SQL rules, additional filters inside the ON clause need to apply to the joined table.

In our query:

contact AS c

is the joined table.

Therefore:

c.fullname LIKE 'A%'

can be used as an additional ON filter.

But:

a.name LIKE 'A%'

doesn’t fit that documented pattern because it applies to the base Account table.

This is exactly the kind of difference that can catch experienced SQL developers.

The syntax looks completely normal.

The Dataverse implementation has additional rules.

Combining Multiple Conditions

We aren’t restricted to one additional condition.

Suppose we want Contacts where either:

  • Full name begins with A

or:

  • Email address begins with B

We can write:

SELECT
a.name,
c.fullname,
c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
AND (
c.fullname LIKE 'A%'
OR c.emailaddress1 LIKE 'B%'
)

The important structure here is:

relationship
AND
(
additional condition
OR
additional condition
)

The equality relationship remains the foundation of the join.

Additional conditions are then combined with it using AND.

Why JOINs Make This Feature Interesting

A simple SQL query like:

SELECT name
FROM account

is convenient.

But convenience alone probably isn’t enough to fundamentally change how we think about querying Dataverse.

Relationships make the discussion much more interesting.

Dataverse applications are relational by nature.

A typical implementation might contain relationships such as:

Account
|
+---- Contacts
|
+---- Opportunities
|
+---- Cases
|
+---- Activities

The moment our requirement crosses those relationships, we need a query language capable of describing them.

With FetchXML, we have link-entity.

With OData, we have navigation properties and $expand.

And now, when using this SQL capability through the Dataverse Web API, we can express certain relationships using:

INNER JOIN

and:

LEFT JOIN

For developers who already think relationally, that can make some queries very easy to read.

JOINs Also Have a Cost

Just because we can join several Dataverse tables doesn’t mean we should keep adding joins indefinitely.

Microsoft’s performance guidance specifically recommends limiting join depth.

Each additional join increases the cost of the query.

So a query like:

Account
↓
Contact
↓
Another Table
↓
Another Table
↓
Another Table

should make us stop and ask whether we genuinely need all of those relationships in a single request.

This isn’t unique to Dataverse.

Complex relational queries have a cost in any data platform.

But when querying an application platform through an API, it becomes particularly important to retrieve only the data the application actually needs.

INNER JOIN or LEFT JOIN?

A useful way to think about the decision is through the business requirement.

If the requirement says:

Give me Accounts that have Contacts.

Think:

INNER JOIN

If the requirement says:

Give me all Accounts, and include Contacts where they exist.

Think:

LEFT JOIN

That difference matters much more than the SQL syntax itself.

The query should represent the business meaning of the relationship.

SQL Makes the Relationship Easy to See

Consider this query one final time:

SELECT
a.name AS account_name,
c.fullname AS contact_name,
o.name AS opportunity_name
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
LEFT JOIN opportunity AS o
ON a.accountid = o.customerid

Even without knowing the application, we can infer quite a lot from it.

We start with Accounts.

An Account must have a matching Contact because we’re using:

INNER JOIN contact

But the Opportunity is optional because we’re using:

LEFT JOIN opportunity

The SQL itself communicates the intended relationship.

And that readability is probably one of the most interesting aspects of having SQL available through the Dataverse Web API.

Where Do We Go Next?

We can now:

SELECT data
FILTER data
SORT data
JOIN related tables

But sometimes retrieving individual Dataverse rows isn’t what we need.

Sometimes the question is:

How many Contacts does each Account have?

Or:

What is the total value of these records?

Or:

What is the average value?

That’s when we move away from simply retrieving records and start summarising them.

And Dataverse SQL gives us another familiar set of tools for doing exactly that:

COUNT()
SUM()
AVG()
MIN()
MAX()
GROUP BY

That is where we’ll go next.

Leave a Reply