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 JOINLEFT 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.emailaddress1FROM account AS aINNER 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.namec.fullnamec.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 JonesContoso └── David BrownFourth Coffee └── No Contacts
If we run:
SELECT a.name, c.fullnameFROM account AS aINNER 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 SmithAdventure Works Sarah JonesContoso 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.fullnameFROM account AS aLEFT JOIN contact AS c ON a.accountid = c.parentcustomerid
Now the Account is retained even when no matching Contact exists.
Conceptually:
Adventure Works John SmithAdventure Works Sarah JonesContoso David BrownFourth 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_nameFROM account AS aINNER JOIN contact AS c ON a.accountid = c.parentcustomeridINNER 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_nameFROM account AS aINNER JOIN contact AS c ON a.accountid = c.parentcustomeridINNER 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:
namefullnamename
our output describes what each value represents:
account_namecontact_namecontact_emailopportunity_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_accountFROM account AS childINNER 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.emailaddress1FROM account AS aINNER 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.fullnameFROM account AS aINNER 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.emailaddress1FROM account AS aINNER 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.fullnameFROM account AS aINNER 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.emailaddress1FROM account AS aINNER JOIN contact AS c ON a.accountid = c.parentcustomerid AND ( c.fullname LIKE 'A%' OR c.emailaddress1 LIKE 'B%' )
The important structure here is:
relationshipAND( 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 nameFROM 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_nameFROM account AS aINNER JOIN contact AS c ON a.accountid = c.parentcustomeridLEFT 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 dataFILTER dataSORT dataJOIN 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.
