DocumentDB SQL Joins - DocumentDB SQL

What is Joins in DocumentDB SQL?

  • The Joins clause is used to combine records from two or more tables in a database in relational database.
  • It is important while designing normalized schemas. Since DocumentDB deals with the denormalized data model of schema-free documents; the JOIN in DocumentDB SQL is the logical equivalent of a "selfjoin".

Consider the three documents as in the previous examples.

The below code is the AndersenFamily document.

The below code is the SmithFamily document.

The below code is the WakefieldFamily document.

Consider an example to understand how the JOIN clause works.

sql join

The query which will join the root to children subdocument is given below:

On executing the above query the following result is produced:

From the above example you can see that, the join is between the document root and the children sub-root which makes a cross-product between two JSON objects. Certain points to note as followed :

  • In the FROM clause, the JOIN clause is an iterator.
  • The first two documents WakefieldFamily and SmithFamily contain two children; hence the result set also contains the cross-product which produces a separate object for each child.
  • The third document AndersenFamily contains only one child, hence there is only a single object corresponding to this document.

Consider another example which will retrieve the child name as well for better understanding of JOIN clause.

sql joins

The query which will join the root to children subdocument is given below:

On executing the above query the following result is produced:

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

DocumentDB SQL Topics