如何在ArangoDB中使用左外连接、右外连接及全外连接查询？附示例

Hey there! Let's dive into how to implement left, right, and full outer joins in ArangoDB using AQL (ArangoDB Query Language). I'll use practical examples with two sample collections to make this concrete:

• users: Contains user documents with fields _id, name
• orders: Contains order documents with fields _id, userId (links to users._id), amount

1. Left Outer Join #

A left outer join keeps all documents from the left collection (users in our case), and matches them with corresponding documents from the right collection (orders). If there's no match, the right collection's fields will be null.

Example Query #

FOR u IN users
LEFT OUTER JOIN o IN orders
  ON u._id == o.userId
RETURN {
  user_name: u.name,
  order_amount: o.amount ? o.amount : null
}

What this does: #

• For every user, we'll get their name and any order amount they have.
• Users who haven't placed any orders will show null for order_amount.

2. Right Outer Join #

ArangoDB doesn't have a direct RIGHT OUTER JOIN keyword, but we can easily simulate it by swapping the left and right collections and using a LEFT OUTER JOIN. This way, we keep all documents from the original right collection (orders) and match them with the original left collection (users).

Example Query #

FOR o IN orders
LEFT OUTER JOIN u IN users
  ON o.userId == u._id
RETURN {
  user_name: u.name ? u.name : null,
  order_amount: o.amount
}

What this does: #

• For every order, we'll get the amount and the corresponding user's name.
• Orders that are linked to a non-existent user (e.g., the user was deleted) will show null for user_name.

3. Full Outer Join #

ArangoDB also lacks a native FULL OUTER JOIN, but we can build it by combining a left outer join with the unmatched records from a simulated right outer join, then deduplicating the results.

Example Query #

// Get all users + their orders (left outer join)
LET left_join_results = (
  FOR u IN users
  LEFT OUTER JOIN o IN orders
    ON u._id == o.userId
  RETURN {
    user_id: u._id,
    user_name: u.name,
    order_id: o._id ? o._id : null,
    order_amount: o.amount ? o.amount : null
  }
)

// Get orders that have no matching user (unmatched from right side)
LET right_unmatched_results = (
  FOR o IN orders
  LEFT OUTER JOIN u IN users
    ON o.userId == u._id
  FILTER u == null
  RETURN {
    user_id: null,
    user_name: null,
    order_id: o._id,
    order_amount: o.amount
  }
)

// Combine and deduplicate results
FOR result IN UNION_DISTINCT(left_join_results, right_unmatched_results)
RETURN result

What this does: #

• We get all users (even those without orders) and all orders (even those without a matching user).
• UNION_DISTINCT ensures we don't have duplicate records where a user-order pair was already captured in the left join.

内容的提问来源于stack exchange，提问作者Shankaragouda G