Optic Joins Starter Pack

Row sources, join condition, and resulting row shape sit side by side here, so you can see exactly why one join type kept a row and another one quietly dropped it.

This starter pack is meant to be read as a decision guide. It helps you choose the right join shape before your query grows into a tangle of partial matches, missing rows, and confusing output columns.

The examples in this article assume the llamaverse (v2.5.0+) is deployed. The llamaverse sample data is freely available from github.com/cleverllamas/llamaverse - see the llamaverse article for full setup instructions.

API Context for This Pack

Context Item	What this pack uses
Primary row sources	`llamaverse.wildLlamas` and `llamaverse.secretPowers` views
Primary join functions	`op:join-inner`, `op:join-left-outer`, `op:join-full-outer`, `op:exists-join`, `op:not-exists-join`, `op:join-cross-product`
Join key pattern	`wildLlamas.secretPowerId` -> `secretPowers.id`
Output style	Short row samples showing the shape difference between join types

Join Type Reference

Function	What it keeps	Use when
`op:join-inner()`	Only matched left/right pairs	Both sides are required for the answer
`op:join-left-outer()`	All left rows plus matched right rows	The left side is primary and missing matches are still meaningful
`op:join-full-outer()`	All rows from both sides	You need to see unmatched rows on either side
`op:exists-join()`	Only left rows that have a match	The join is really a filter, not a merge
`op:not-exists-join()`	Only left rows that do not have a match	You need the gaps, exceptions, or missing relationships
`op:join-cross-product()`	Every left/right combination, optionally filtered	You need non-equality comparisons or deliberately broad pairing logic

op:join-inner()

op:join-inner() is the default relational join shape. Use it when the answer is only meaningful for rows that match on both sides.

The full signature is:

op:join-inner($leftPlan, $rightPlan, [$keys as op:on()*], [$condition as boolean-expression?])

Parameter	Type	What it controls
`$leftPlan`	plan	The left-side row set (the plan you are chaining from)
`$rightPlan`	plan	The right-side row set to join against
`$keys`	`op:on()*` — optional	One or more equality conditions built with `op:on()`. Each `op:on()` compares one left column to one right column. Pass an empty sequence `()` / `null` when using `$condition` only.
`$condition`	boolean expression — optional	Any boolean expression built with the Optic boolean expression functions (`op:eq`, `op:gt`, `op:ge`, `op:lt`, `op:le`, `op:ne`, `op:and`, `op:or`, `op:not`). Used for non-equality or complex join conditions.

When both $keys and $condition are provided they are combined with AND at plan evaluation time — a row must satisfy all key equalities AND the condition to appear in the result.

Equality join using op:on

The most common form: one or more op:on() calls providing the equality condition. Pass multiple op:on() calls as a sequence to join on compound keys.

In the llamaverse, both llamas and secretPowers are TDE projections of the same source document. Each llama document carries an embedded secretPower object; the TDE extracts the llama fields into the llamas view and the embedded power fields into a separate secretPowers view row. The join re-unites those two projections by matching llamas.secretPowerId against secretPowers.id.

{
  "id": "0c8bdb0d-ac62-49b7-ac74-94dbba46efa5",
  "name": "Aaron",
  "heightCm": 180,
  "breed": "Huacaya",
  "placeOfBirth": "Cusco, Peru",
  "secretPower": {
    "name": "Snake Charmer",
    "id": "f6236908-9c0e-416e-a591-a1bd3986dc05"
  }
}

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

let $llamas := op:from-view("llamaverse", "llamas")
let $powers := op:from-view("llamaverse", "secretPowers")

return
$llamas
  => op:join-inner(
       $powers,
       op:on(
         op:view-col("llamas", "secretPowerId"),
         op:view-col("secretPowers", "id")
       )
     )
  => op:select((
       op:view-col("llamas", "name"),
       op:view-col("secretPowers", "name"),
       op:view-col("secretPowers", "description")
     ))
  => op:limit(3)
  => op:result()

'use strict';

const op = require('/MarkLogic/optic');

const llamas = op.fromView('llamaverse', 'llamas');
const powers = op.fromView('llamaverse', 'secretPowers');

const results = llamas
  .joinInner(
    powers,
    op.on(
      op.viewCol('llamas', 'secretPowerId'),
      op.viewCol('secretPowers', 'id')
    )
  )
  .select([
    op.viewCol('llamas', 'name'),
    op.viewCol('secretPowers', 'name')
  ])
  .limit(5)
  .result();

({
  sample: 'optic/joins-starter-pack/assets/op-join-inner.sjs',
  kind: Array.isArray(results) ? (results.every((item) => typeof item === 'object' && 'subject' in item && 'predicate' in item && 'object' in item) ? 'triples' : 'rows') : ((results !== null && typeof results === 'object') ? 'object' : 'scalar'),
  count: Array.isArray(results) ? results.length : 0,
  data: results
});

{"llamaverse.llamas.name":"Cory", "llamaverse.secretPowers.name":"Beard of the Bards", "llamaverse.secretPowers.description":"Possesses a musical beard that tells epic tales and sings backup harmonies during karaoke battles."}
{"llamaverse.llamas.name":"Angela", "llamaverse.secretPowers.name":"Leaf Dancer", "llamaverse.secretPowers.description":"Performs enchanted dances that make leaves swirl, squirrels applaud, and acorns roll in rhythm."}
{"llamaverse.llamas.name":"Jaime", "llamaverse.secretPowers.name":"Featherlight Step", "llamaverse.secretPowers.description":"So light-footed they’ve been mistaken for a gentle breeze. Ideal for sneaking into cookie jars undetected."}

llamaverse.llamas.name	llamaverse.secretPowers.name	llamaverse.secretPowers.description
Cory	Beard of the Bards	Possesses a musical beard that tells epic tales and sings backup harmonies during karaoke battles.
Angela	Leaf Dancer	Performs enchanted dances that make leaves swirl, squirrels applaud, and acorns roll in rhythm.
Jaime	Featherlight Step	So light-footed they’ve been mistaken for a gentle breeze. Ideal for sneaking into cookie jars undetected.

What to notice: the answer only exists where both row sets contribute something — no llama, no power, no row. That's the whole deal with an inner join.

Combining keys and condition

Pass both $keys and $condition when the equality join needs an additional inequality constraint applied during the join. The equality key narrows the candidate pairs; the condition filters within those pairs. This is more efficient than adding a separate op:where() after a broad join because the condition participates in the plan’s join optimisation.

Here, we join llamas to their secret powers by the equality key, but keep only llamas taller than 155 cm:

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

(: Equality key (op:on) combined with an additional boolean condition.      :)
(: The two parameters are ANDed at plan evaluation time:                     :)
(:   - keys: wildLlamas.secretPowerId = secretPowers.id  (equality)          :)
(:   - condition: llamas.heightCm > 155                  (inequality filter) :)
(: Only tall llamas with a matching power row appear in the result.          :)

let $llamas := op:from-view("llamaverse", "llamas")
let $powers := op:from-view("llamaverse", "secretPowers")

return
$llamas
  => op:join-inner(
       $powers,
       op:on(
         op:view-col("llamas", "secretPowerId"),
         op:view-col("secretPowers", "id")
       ),
       op:gt(
         op:view-col("llamas", "heightCm"),
         155
       )
     )
  => op:select((
       op:view-col("llamas",        "name"),
       op:view-col("llamas",        "heightCm"),
       op:view-col("secretPowers",  "name")
     ))
  => op:order-by(op:desc(op:view-col("llamas", "heightCm")))
  => op:limit(5)
  => op:result()

'use strict';
const op = require('/MarkLogic/optic');

// Equality key (op.on) combined with an additional boolean condition.
// The two parameters are ANDed at plan evaluation time:
//   - keys: wildLlamas.secretPowerId = secretPowers.id  (equality)
//   - condition: llamas.heightCm > 155                  (inequality filter)
// Only tall llamas with a matching power row appear in the result.

const llamas = op.fromView('llamaverse', 'llamas');
const powers = op.fromView('llamaverse', 'secretPowers');

llamas
  .joinInner(
    powers,
    op.on(
      op.viewCol('llamas', 'secretPowerId'),
      op.viewCol('secretPowers', 'id')
    ),
    op.gt(
      op.viewCol('llamas', 'heightCm'),
      155
    )
  )
  .select([
    op.viewCol('llamas',       'name'),
    op.viewCol('llamas',       'heightCm'),
    op.viewCol('secretPowers', 'name')
  ])
  .orderBy(op.desc(op.viewCol('llamas', 'heightCm')))
  .limit(5)
  .result();

{"llamaverse.llamas.name": "Meredith", "llamaverse.llamas.heightCm": 163, "llamaverse.secretPowers.name": "Storm Caller"}
{"llamaverse.llamas.name": "Glen",     "llamaverse.llamas.heightCm": 161, "llamaverse.secretPowers.name": "Thunder Stomper"}
{"llamaverse.llamas.name": "Patrick",  "llamaverse.llamas.heightCm": 158, "llamaverse.secretPowers.name": "Fog Weaver"}
{"llamaverse.llamas.name": "Tina",     "llamaverse.llamas.heightCm": 157, "llamaverse.secretPowers.name": "Cactus Hugger"}
{"llamaverse.llamas.name": "Jaime",    "llamaverse.llamas.heightCm": 156, "llamaverse.secretPowers.name": "Featherlight Step"}

(Results need validation against live MarkLogic. Names, heights, and power names
are representative of the llamaverse dataset. The key behaviour to verify:
every returned row has heightCm > 155 AND a non-null secretPowers.name.)

llamaverse.llamas.name	llamaverse.llamas.heightCm	llamaverse.secretPowers.name
Meredith	163	Storm Caller
Glen	161	Thunder Stomper
Patrick	158	Fog Weaver
Tina	157	Cactus Hugger
Jaime	156	Featherlight Step

What to notice: only rows satisfying both the key equality AND the condition appear. The condition is not a post-filter — it is part of the join predicate evaluated by the plan engine.

Condition-only join

Pass an empty sequence (() in XQuery, null in JavaScript) for $keys and supply only $condition. Without equality keys, the join engine evaluates the condition against every pair from the cross-product of the two row sets — so the condition is the sole criterion for inclusion.

This is the right shape when no natural equality key exists between the two sources and you need a range-based, inequality, or expression-based pairing. The example below uses a literal right-side bucket table to assign each llama a height category based on a [lo, hi) range condition:

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

(: Condition-only join — no equality keys.                                   :)
(: This is semantically equivalent to a cross-product filtered by condition. :)
(: Each llama row is tested against every bucket row; a llama is kept where  :)
(: its heightCm falls within the bucket's [lo, hi) range.                    :)
(:                                                                            :)
(: The right-side literal rows have non-overlapping ranges, so each llama    :)
(: lands in exactly one bucket.                                               :)

let $llamas := op:from-view("llamaverse", "llamas")
let $buckets := op:from-literals((
  map:entry("bucket", xs:string("compact"))  => map:with("lo", xs:integer(120))
                                             => map:with("hi", xs:integer(140)),
  map:entry("bucket", xs:string("standard")) => map:with("lo", xs:integer(140))
                                             => map:with("hi", xs:integer(155)),
  map:entry("bucket", xs:string("tall"))     => map:with("lo", xs:integer(155))
                                             => map:with("hi", xs:integer(200))
), "sizes")

return
$llamas
  => op:join-inner(
       $buckets,
       (),                                   (: no equality keys :)
       op:and(
         op:ge(op:view-col("llamas", "heightCm"), op:view-col("sizes", "lo")),
         op:lt(op:view-col("llamas", "heightCm"), op:view-col("sizes", "hi"))
       )
     )
  => op:select((
       op:view-col("llamas",  "name"),
       op:view-col("llamas",  "heightCm"),
       op:view-col("sizes",   "bucket")
     ))
  => op:order-by((
       op:view-col("sizes",   "bucket"),
       op:view-col("llamas",  "heightCm")
     ))
  => op:limit(6)
  => op:result()

'use strict';
const op = require('/MarkLogic/optic');

// Condition-only join — no equality keys.
// Semantically equivalent to a cross-product filtered by condition.
// Each llama row is tested against every bucket row; a llama is kept where
// its heightCm falls within the bucket's [lo, hi) range.
//
// The right-side literal rows have non-overlapping ranges, so each llama
// lands in exactly one bucket.

const llamas = op.fromView('llamaverse', 'llamas');
const buckets = op.fromLiterals([
  { bucket: 'compact',  lo: 120, hi: 140 },
  { bucket: 'standard', lo: 140, hi: 155 },
  { bucket: 'tall',     lo: 155, hi: 200 }
], 'sizes');

llamas
  .joinInner(
    buckets,
    null,                                    // no equality keys
    op.and(
      op.ge(op.viewCol('llamas', 'heightCm'), op.viewCol('sizes', 'lo')),
      op.lt(op.viewCol('llamas', 'heightCm'), op.viewCol('sizes', 'hi'))
    )
  )
  .select([
    op.viewCol('llamas', 'name'),
    op.viewCol('llamas', 'heightCm'),
    op.viewCol('sizes',  'bucket')
  ])
  .orderBy([
    op.viewCol('sizes',  'bucket'),
    op.viewCol('llamas', 'heightCm')
  ])
  .limit(6)
  .result();

{"llamaverse.llamas.name": "Hannah",  "llamaverse.llamas.heightCm": 136, "sizes.bucket": "compact"}
{"llamaverse.llamas.name": "Angela",  "llamaverse.llamas.heightCm": 138, "sizes.bucket": "compact"}
{"llamaverse.llamas.name": "Bradley", "llamaverse.llamas.heightCm": 143, "sizes.bucket": "standard"}
{"llamaverse.llamas.name": "Aaron",   "llamaverse.llamas.heightCm": 148, "sizes.bucket": "standard"}
{"llamaverse.llamas.name": "Tina",    "llamaverse.llamas.heightCm": 157, "sizes.bucket": "tall"}
{"llamaverse.llamas.name": "Glen",    "llamaverse.llamas.heightCm": 161, "sizes.bucket": "tall"}

(Results need validation against live MarkLogic. Names and heights are
representative of the llamaverse dataset. The key behaviour to verify:
every llama appears exactly once, in the bucket whose [lo, hi) range
contains that llama's heightCm. No llama should appear in multiple buckets.)

llamaverse.llamas.name	llamaverse.llamas.heightCm	sizes.bucket
Hannah	136	compact
Angela	138	compact
Bradley	143	standard
Aaron	148	standard
Tina	157	tall
Glen	161	tall

What to notice: the right side is a literal row set — no view, no index. Each llama lands in exactly one bucket because the ranges are non-overlapping. Without equality keys, the plan must evaluate the condition against every possible pair, so keep the right-side row count small for condition-only joins.

op:join-left-outer()

op:join-left-outer() is what you want when the left side defines the candidate set and the right side only enriches it when a match exists.

Option / Argument	What it controls	Used here
`rightPlan`	The right-side row set used for enrichment	`llamaverse.secretPowers` view plan
`on`	Join condition for matching left/right rows	`wildLlamas.secretPowerId = secretPowers.id`

{
  "name": "Aaron",
  "breed": "Huacaya",
  "placeOfBirth": "Cusco, Peru",
  "secretPowerId": "d8839ba6-2b77-4bcc-9927-b86cdfecb9fb"
}

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

let $llamas := op:from-view("llamaverse", "llamas")
let $powers := op:from-view("llamaverse", "secretPowers")

return
$llamas
  => op:join-left-outer(
       $powers,
       op:on(
         op:view-col("llamas", "secretPowerId"),
         op:view-col("secretPowers", "id")
       )
     )
  => op:select((
       op:view-col("llamas", "name"),
       op:view-col("llamas", "secretPowerId"),
       op:view-col("secretPowers", "name")
     ))
  => op:limit(3)
  => op:result()

'use strict';

const op = require('/MarkLogic/optic');

const llamas = op.fromView('llamaverse', 'llamas');
const powers = op.fromView('llamaverse', 'secretPowers');

const results = llamas
  .joinLeftOuter(
    powers,
    op.on(
      op.viewCol('llamas', 'secretPowerId'),
      op.viewCol('secretPowers', 'id')
    )
  )
  .select([
    op.viewCol('llamas', 'name'),
    op.viewCol('llamas', 'secretPowerId'),
    op.viewCol('secretPowers', 'name')
  ])
  .limit(5)
  .result();

({
  sample: 'optic/joins-starter-pack/assets/op-join-left-outer.sjs',
  kind: Array.isArray(results) ? (results.every((item) => typeof item === 'object' && 'subject' in item && 'predicate' in item && 'object' in item) ? 'triples' : 'rows') : ((results !== null && typeof results === 'object') ? 'object' : 'scalar'),
  count: Array.isArray(results) ? results.length : 0,
  data: results
});

{"llamaverse.llamas.name": "Bradley", "llamaverse.llamas.secretPowerId": "027d5ed8-cd2f-4b72-9d89-3c85c0e4840d", "llamaverse.secretPowers.name": "Rain Rhymester"}
{"llamaverse.llamas.name": "Glen", "llamaverse.llamas.secretPowerId": "1ca5ed0c-069f-4e26-b8ea-59e10ed13710", "llamaverse.secretPowers.name": "Thunder Stomper"}
{"llamaverse.llamas.name": "Jeanette", "llamaverse.llamas.secretPowerId": "01f5441b-5875-49dc-a4c5-4fb324fc2b02", "llamaverse.secretPowers.name": "Wind Whisperer"}

llamaverse.llamas.name	llamaverse.llamas.secretPowerId	llamaverse.secretPowers.name
Bradley	027d5ed8-cd2f-4b72-9d89-3c85c0e4840d	Rain Rhymester
Glen	1ca5ed0c-069f-4e26-b8ea-59e10ed13710	Thunder Stomper
Jeanette	01f5441b-5875-49dc-a4c5-4fb324fc2b02	Wind Whisperer

What to notice: unmatched rows stay visible because the left side is the story.

op:join-full-outer()

op:join-full-outer() is for reconciliation work. Use it when you need to see both matched pairs and orphaned rows on either side.

Option / Argument	What it controls	Used here
`rightPlan`	The right-side row set to reconcile with the left side	Seeded right-side demo rows
`on`	Join condition used to detect matches	`powerId` equality across left/right rows

{
  "left": [
    { "powerId": "p1", "llama": "Aaron" },
    { "powerId": "p2", "llama": "Angela" }
  ],
  "right": [
    { "powerId": "p2", "power": "Fog Walker" },
    { "powerId": "p3", "power": "Potion Sniffer" }
  ]
}

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

let $left := op:from-literals((
  map:entry("powerId", "p1") => map:with("llama", "Aaron"),
  map:entry("powerId", "p2") => map:with("llama", "Angela")
))
let $right := op:from-literals((
  map:entry("powerId", "p2") => map:with("power", "Fog Walker"),
  map:entry("powerId", "p3") => map:with("power", "Potion Sniffer")
))

return
$left
  => op:join-full-outer($right, op:on("powerId", "powerId"))
  => op:result()

'use strict';

const op = require('/MarkLogic/optic');

const leftRows = [
  { id: 1, leftName: 'Aaron' },
  { id: 2, leftName: 'Angela' }
];
const rightRows = [
  { id: 2, rightName: 'X-Ray Vision' },
  { id: 3, rightName: 'Invisibility' }
];

const leftPlan = op.fromLiterals(leftRows);
const rightPlan = op.fromLiterals(rightRows);

const results = leftPlan
  .joinFullOuter(rightPlan, op.on(op.col('id'), op.col('id')))
  .result();

({
  sample: 'optic/joins-starter-pack/assets/op-join-full-outer.sjs',
  kind: Array.isArray(results) ? (results.every((item) => typeof item === 'object' && 'subject' in item && 'predicate' in item && 'object' in item) ? 'triples' : 'rows') : ((results !== null && typeof results === 'object') ? 'object' : 'scalar'),
  count: Array.isArray(results) ? results.length : 0,
  data: results
});

{"powerId": "p1", "power": null, "llama": "Aaron"}
{"powerId": "p2", "power": "Fog Walker", "llama": "Angela"}
{"powerId": "p3", "power": "Potion Sniffer", "llama": null}

powerId	power	llama
p1		Aaron
p2	Fog Walker	Angela
p3	Potion Sniffer

What to notice: full outer joins are less about enrichment and more about reconciliation.

op:exists-join() and op:not-exists-join()

These are filtering joins. They do not add right-side columns. They answer the narrower question: does a match exist?

Option / Argument	What it controls	Used here
`rightPlan`	The right-side row set used only for existence checks	`llamaverse.secretPowers` view plan
`on`	Condition that determines whether a match exists	`wildLlamas.secretPowerId = secretPowers.id`

{
  "name": "Aaron",
  "breed": "Huacaya",
  "placeOfBirth": "Cusco, Peru",
  "secretPowerId": "d8839ba6-2b77-4bcc-9927-b86cdfecb9fb"
}

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic" at "/MarkLogic/optic.xqy";

let $llamas := op:from-view("llamaverse", "llamas")
let $powers := op:from-view("llamaverse", "secretPowers")

return
$llamas
  => op:exists-join(
       $powers,
       op:on(
         op:view-col("llamas", "secretPowerId"),
         op:view-col("secretPowers", "id")
       )
     )
  => op:select((op:view-col("llamas", "name"), op:view-col("llamas", "secretPowerId")))
  => op:limit(3)
  => op:result()

'use strict';

const op = require('/MarkLogic/optic');

const llamas = op.fromView('llamaverse', 'llamas');
const powers = op.fromView('llamaverse', 'secretPowers');

const results = llamas
  .existsJoin(
    powers,
    op.on(
      op.viewCol('llamas', 'secretPowerId'),
      op.viewCol('secretPowers', 'id')
    )
  )
  .select([
    op.viewCol('llamas', 'name'),
    op.viewCol('llamas', 'secretPowerId')
  ])
  .limit(5)
  .result();

({
  sample: 'optic/joins-starter-pack/assets/op-exists-join.sjs',
  kind: Array.isArray(results) ? (results.every((item) => typeof item === 'object' && 'subject' in item && 'predicate' in item && 'object' in item) ? 'triples' : 'rows') : ((results !== null && typeof results === 'object') ? 'object' : 'scalar'),
  count: Array.isArray(results) ? results.length : 0,
  data: results
});

{"llamaverse.llamas.name": "Bradley", "llamaverse.llamas.secretPowerId": "027d5ed8-cd2f-4b72-9d89-3c85c0e4840d"}
{"llamaverse.llamas.name": "Glen", "llamaverse.llamas.secretPowerId": "1ca5ed0c-069f-4e26-b8ea-59e10ed13710"}
{"llamaverse.llamas.name": "Jeanette", "llamaverse.llamas.secretPowerId": "01f5441b-5875-49dc-a4c5-4fb324fc2b02"}

llamaverse.llamas.name	llamaverse.llamas.secretPowerId
Bradley	027d5ed8-cd2f-4b72-9d89-3c85c0e4840d
Glen	1ca5ed0c-069f-4e26-b8ea-59e10ed13710
Jeanette	01f5441b-5875-49dc-a4c5-4fb324fc2b02

The inverse shape is op:not-exists-join(): use it to find llamas without a matching power row, documents without companion data, or any other gap report.

op:join-cross-product()

op:join-cross-product() is the right shape when equality is not the join rule. It pairs every left row with every right row, then applies a condition to keep only the meaningful combinations. The right side is typically a small literal table of categories, thresholds, or buckets.

Option / Argument	What it controls	Used here
`rightPlan`	The row set paired with every row from the current left plan	Literal care-schedule tier table
`condition` (optional)	Predicate applied across every left/right pair	Range condition assigning each llama to exactly one tier

Here, each llamaverse llama is paired against a three-tier care schedule table. No equality key exists between the two sources — a llama lands in a tier when its height falls within the tier's [minCm, maxCm) range:

xquery version "1.0-ml";
import module namespace op = "http://marklogic.com/optic"
    at "/MarkLogic/optic.xqy";

(: Left: real llamas with their heights from the llamaverse view :)
let $llamas :=
  op:from-view("llamaverse", "llamas")
  => op:select((
       op:view-col("llamas", "name"),
       op:view-col("llamas", "heightCm")
     ))

(: Right: literal care-schedule tier table — height ranges, no equality key :)
let $schedule := op:from-literals((
  map:entry("careTask", "routine-check")     => map:with("minCm", 100) => map:with("maxCm", 165),
  map:entry("careTask", "growth-watch")      => map:with("minCm", 165) => map:with("maxCm", 180),
  map:entry("careTask", "heavyweight-watch") => map:with("minCm", 180) => map:with("maxCm", 300)
))

return
$llamas
  => op:join-cross-product(
       $schedule,
       op:and(
         op:ge(op:view-col("llamas", "heightCm"), op:col("minCm")),
         op:lt(op:view-col("llamas", "heightCm"), op:col("maxCm"))
       )
     )
  => op:select((
       op:view-col("llamas", "name"),
       op:view-col("llamas", "heightCm"),
       op:col("careTask")
     ))
  => op:order-by(op:view-col("llamas", "heightCm"))
  => op:result()

'use strict';
const op = require('/MarkLogic/optic');

// Left: real llamas with their heights from the llamaverse view
const llamas =
  op.fromView('llamaverse', 'llamas')
    .select([
      op.viewCol('llamas', 'name'),
      op.viewCol('llamas', 'heightCm')
    ]);

// Right: literal care-schedule tier table — height ranges, no equality key
const schedule = op.fromLiterals([
  { careTask: 'routine-check',     minCm: 100, maxCm: 165 },
  { careTask: 'growth-watch',      minCm: 165, maxCm: 180 },
  { careTask: 'heavyweight-watch', minCm: 180, maxCm: 300 }
]);

llamas
  .joinCrossProduct(
    schedule,
    op.and(
      op.ge(op.viewCol('llamas', 'heightCm'), op.col('minCm')),
      op.lt(op.viewCol('llamas', 'heightCm'), op.col('maxCm'))
    )
  )
  .select([
    op.viewCol('llamas', 'name'),
    op.viewCol('llamas', 'heightCm'),
    op.col('careTask')
  ])
  .orderBy(op.viewCol('llamas', 'heightCm'))
  .result();

{"llamaverse.llamas.name":"Debbie", "llamaverse.llamas.heightCm":156, "careTask":"routine-check"}
{"llamaverse.llamas.name":"Glen", "llamaverse.llamas.heightCm":158, "careTask":"routine-check"}
{"llamaverse.llamas.name":"Brandon", "llamaverse.llamas.heightCm":161, "careTask":"routine-check"}
{"llamaverse.llamas.name":"Jaime", "llamaverse.llamas.heightCm":162, "careTask":"routine-check"}
{"llamaverse.llamas.name":"Katrina", "llamaverse.llamas.heightCm":166, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Ashley", "llamaverse.llamas.heightCm":167, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Angela", "llamaverse.llamas.heightCm":167, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Teresa", "llamaverse.llamas.heightCm":169, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Jeanette", "llamaverse.llamas.heightCm":170, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Anthony", "llamaverse.llamas.heightCm":171, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Bradley", "llamaverse.llamas.heightCm":172, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Cory", "llamaverse.llamas.heightCm":173, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Tina", "llamaverse.llamas.heightCm":173, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Richard", "llamaverse.llamas.heightCm":174, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Tim", "llamaverse.llamas.heightCm":177, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Kimberly", "llamaverse.llamas.heightCm":178, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Cameron", "llamaverse.llamas.heightCm":179, "careTask":"growth-watch"}
{"llamaverse.llamas.name":"Aaron", "llamaverse.llamas.heightCm":180, "careTask":"heavyweight-watch"}
{"llamaverse.llamas.name":"William", "llamaverse.llamas.heightCm":182, "careTask":"heavyweight-watch"}
{"llamaverse.llamas.name":"James", "llamaverse.llamas.heightCm":184, "careTask":"heavyweight-watch"}

llamaverse.llamas.name	llamaverse.llamas.heightCm	careTask
Debbie	156	routine-check
Glen	158	routine-check
Brandon	161	routine-check
Jaime	162	routine-check
Katrina	166	growth-watch
Ashley	167	growth-watch
Angela	167	growth-watch
Teresa	169	growth-watch
Jeanette	170	growth-watch
Anthony	171	growth-watch
Bradley	172	growth-watch
Cory	173	growth-watch
Tina	173	growth-watch
Richard	174	growth-watch
Tim	177	growth-watch
Kimberly	178	growth-watch
Cameron	179	growth-watch
Aaron	180	heavyweight-watch
William	182	heavyweight-watch
James	184	heavyweight-watch

What to notice: every llama is evaluated against every tier (3 tiers × 20 llamas = 60 pairs considered), but each llama appears exactly once in the output because the ranges are non-overlapping. Keep the right-side row count small — cross-product scales as N×M before the condition filters it down.

Join Hygiene

Two join mistakes cause most confusion:

Forgetting that matching column identifiers can trigger natural join behaviour you did not mean.
Choosing a row-merging join when the real requirement was only existence filtering.

If the right side adds no columns to your answer, check whether exists-join or not-exists-join is the clearer expression.

Multi-Source Mastery Unlocked

Decision rule: if the right side adds no answer columns, use an existence join. Save row-merging joins for true enrichment work.

Joins sit in the middle of the Optic pipeline — sources feed into them and analysis or manipulation follows:

Optic Data Source Starter Pack — Understand op:from-view, op:from-triples, and op:from-lexicons before combining them.
Optic Data Analysis Starter Pack — Group and aggregate across your joined row sets to extract business insights.
Optic Data Manipulation Starter Pack — Transform the joined rows into your desired output shape.

Need Some Help?

Looking for more information on this subject or any other topic related to MarkLogic? Contact Us (info@cleverllamas.com) to find out how we can assist you with consulting or training!

Optic Joins Starter Pack

# API Context for This Pack

# Join Type Reference

# op:join-inner()

# Equality join using op:on

# Combining keys and condition

# Condition-only join

# op:join-left-outer()

# op:join-full-outer()

# op:exists-join() and op:not-exists-join()

# op:join-cross-product()

# Join Hygiene

# Multi-Source Mastery Unlocked