Join Queries

  • Article

[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]

Join query support in SQL Data Services (SDS) allow you to retrieve entities from a container based on a join condition involving properties on different kinds of entities. For example, if you have a container with customer and order entities, a query to find orders for a given a customer would require you join the customer and order entities based on a common property. Since both the customers and orders are in the same container, you query the same container twice (using aliases); first find the customer and then find orders for that customer using a join condition.

Writing join queries against a container is similar to writing the self-join queries in relational databases where you join a table with itself by defining different aliases to the same table.

Examples

For illustration, the join queries in this topic assume:

  • You have a heterogeneous container with entities of Customer, Order and Employee kind.

  • The entities have following properties:

    Customer (Id, Name, Address, City) 
Order (Id, OrderDate, CustomerId)
Employee(Id, Name)

    The Id is a metadata property and other properties are flexible properties. Also note that both the Customer and Employee entities have Name flexible property.

  • The container has the following sample data:

    • Two customers (C1 and C2) and three orders (O1, O2 and O3)
    • Customer C1 has placed orders O1 and O2
    • Customer C2 has placed Order O3

Note

To test the queries in this topic, a sample set of entities and instructions to upload them to your container are provided in Sample Data for Join Queries.

Example 1. Given a customer name, find orders placed by that customer

As discussed earlier in this topic, you write two queries against the container using aliases and apply a join condition.

  • The first query constructs a set (alias "c") that has the specific customer entity.
  • The second query constructs another set (alias "o") of order entities with a join condition on the two sets to ensure this set has orders from the specific customer.

To help understand the join queries, this example builds the query in stages. This query creates a set c of one specific customer entity.

from   c in entities.OfKind("Customer")  
where  c["Name"]=="Customer 1"

Note that the query specifies filter to obtain entities of the customer kind. This is needed because both the Customer and Employee entities in the container have the Name flexible property. This query against the sample data produces a set c with customer C1 entity:

Add second query to create a set "o" of entities of the order kind.

from   c in entities.OfKind("Customer")  
where  c["Name"]=="Customer 1" 
from   o in entities.OfKind("Order") 
where  o["CustomerId"] == c.Id

Now the query has defined two sets with aliases c and o. The join condition (where o["CustomerId"] == c.Id) on these sets ensures the set "o" has orders from customer in set c. This query against sample data produces set o with order entities O1 and O2.

Finally add "select" to the expression to return the order set o as the query result:

from   c in entities.OfKind("Customer")  
where  c["Name"]=="Customer 1" 
from   o in entities.OfKind("Order") 
where  o["CustomerId"] == c.Id 
select o

This final query returns the orders placed by "Customer 1". This query can be written without using the OfKind() function as follows.

from  c in entities 
where c.Kind=="Customer" 
where c["Name"] == "Customer 1" 
from  o in entities 
where o.Kind=="Order" 
where o["CustomerId"] == c.Id 
select o

To test this query

  • Populate sample data in your container. For information see Sample Data for Join Queries.
  • For quick testing of the query you have these options:

    • Specify query in the browser in the browser by first specifying the container scope followed by the query.

      https://<your-authority-id>.data.database.windows.net/v1/<your-container-id>?q=' from  c in entities where c.Kind=="Customer" where c["Name"] == "Customer 1" from  o in entities where o.Kind=="Order" where o["CustomerId"] == c.Id select o'

    • Use SDS Explorer tool.

    • Use SDS Command Line Tool

Guidelines and Limitation:

  • Cross container queries are not supported. When specifying JOIN all the entities being joined must be in one container.
  • The queries don't support projection. This means you cannot return The query result always returns entire entities.
  • You can non-equijoin. That is the join condition can use operators other than equality operator.
  • If you don't sepcify the join condition, you get cartesian product. That is all possible combination of the two sets.

See Also

Concepts

Querying SQL Data Services
SQL Data Services (SDS) SDK
SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities)