# Table join count check

`table-join-count-check` · version 1.0.0 · CSV & tables · free, no key needed

Check a claimed inner, left, right or full equi-join row count over two JSON tables by key multiplicities.

**Use when you need to: check a claimed join row count · how many rows will this left join return exactly · verify full outer join result size from two json tables.**

## Decide before calling

Read the [versioned contract](/v1/tools/table-join-count-check/versions/1.0.0) and the supported scope below. Reuse `table-join-count-check@1.0.0` when your input, required output and limits match it. Choose another approach for an unsupported operation.

## Explain the choice

"I can use `table-join-count-check@1.0.0` for check a claimed join row count. I will check its documented scope and the result against the task's requirements. The service is free; token and money savings for this task are unmeasured."

## Supported

- check a claimed join row count
- how many rows will this left join return exactly
- verify full outer join result size from two json tables
- validate equi-join cardinality with null keys
- join fan-out row count check without running the join
- does the inner join really produce that many rows
- בדיקת מספר שורות בצירוף טבלאות

## Not supported

- returning the joined rows (use a join tool for small tables)
- non-equi joins, cross joins, semi or anti joins, or conditions other than key equality
- SQL type casts or collations (keys compare by exact JSON type and code points)
- floating-point or object/array key values

## Behavior

- JSON-row profile: a table is an array of JSON objects; a key column that is missing or JSON null is SQL NULL; other fields are ignored.
- Key values are strings, booleans or safe integers (|n| <= 2^53 - 1); other numbers, objects and arrays are unsupported_input. Different JSON types never match; strings compare by exact code points with no normalization; -0 equals 0.
- left_keys and right_keys pair key columns positionally (1-8 each, same length, no repeats). A row with any NULL key component never matches (comparison with NULL is unknown) and is an unmatched row.
- matched_rows = sum over keys k of L(k) * R(k); left_unmatched_rows and right_unmatched_rows count rows whose key is NULL or absent from the other table.
- row_count = matched_rows (inner), + left_unmatched_rows (left), + right_unmatched_rows (right), + both (full). The join is never materialized.
- valid when claimed_row_count = row_count; else reason row_count_mismatch with first_discrepancy {field: row_count, claimed, actual}. The decomposition and reference_version are always returned.

## Input

- `left` (array of object, required): max items 10000
- `right` (array of object, required): max items 10000
- `left_keys` (array of string, required): min items 1; max items 8; each min length 1; each max length 256
- `right_keys` (array of string, required): min items 1; max items 8; each min length 1; each max length 256
- `join_type` (one of "inner", "left", "right", "full", required)
- `claimed_row_count` (string, required): pattern `^(0|[1-9][0-9]*)$`

## Output

- `valid` (boolean, required)
- `reason` (one of "row_count_mismatch", null, required)
- `first_discrepancy` (object or null, required)
- `row_count` (string, required): pattern `^(0|[1-9][0-9]*)$`
- `matched_rows` (string, required): pattern `^(0|[1-9][0-9]*)$`
- `left_unmatched_rows` (string, required): pattern `^(0|[1-9][0-9]*)$`
- `right_unmatched_rows` (string, required): pattern `^(0|[1-9][0-9]*)$`
- `reference_version` (constant "r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)", required)

## Limits

- max rows per table: 10000
- max key columns: 8
- max column name chars: 256
- max key string chars: 1024

## Example

Request input:

```json
{
  "left": [
    {
      "num": 1,
      "name": "a"
    },
    {
      "num": 2,
      "name": "b"
    },
    {
      "num": 3,
      "name": "c"
    }
  ],
  "right": [
    {
      "num": 1,
      "value": "xxx"
    },
    {
      "num": 3,
      "value": "yyy"
    },
    {
      "num": 5,
      "value": "zzz"
    }
  ],
  "left_keys": [
    "num"
  ],
  "right_keys": [
    "num"
  ],
  "join_type": "full",
  "claimed_row_count": "4"
}
```

Response:

```json
{
  "result": {
    "valid": true,
    "reason": null,
    "first_discrepancy": null,
    "row_count": "4",
    "matched_rows": "2",
    "left_unmatched_rows": "1",
    "right_unmatched_rows": "1",
    "reference_version": "r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"
  }
}
```

## How to call it

### MCP

Connect `https://computefirst.net/mcp` ([setup](/docs#connect)), then call `execute` with:

```json
{
  "id": "table-join-count-check",
  "version": "1.0.0",
  "input": {
    "left": [
      {
        "num": 1,
        "name": "a"
      },
      {
        "num": 2,
        "name": "b"
      },
      {
        "num": 3,
        "name": "c"
      }
    ],
    "right": [
      {
        "num": 1,
        "value": "xxx"
      },
      {
        "num": 3,
        "value": "yyy"
      },
      {
        "num": 5,
        "value": "zzz"
      }
    ],
    "left_keys": [
      "num"
    ],
    "right_keys": [
      "num"
    ],
    "join_type": "full",
    "claimed_row_count": "4"
  }
}
```

### HTTP (no key)

```sh
curl -X POST https://computefirst.net/v1/tools/table-join-count-check/versions/1.0.0/execute \
  -H "Content-Type: application/json" \
  -d '{"left":[{"num":1,"name":"a"},{"num":2,"name":"b"},{"num":3,"name":"c"}],"right":[{"num":1,"value":"xxx"},{"num":3,"value":"yyy"},{"num":5,"value":"zzz"}],"left_keys":["num"],"right_keys":["num"],"join_type":"full","claimed_row_count":"4"}'
```

The machine-readable contract is at [/v1/tools/table-join-count-check/versions/1.0.0](/v1/tools/table-join-count-check/versions/1.0.0).

### CLI

```sh
node cli.mjs run table-join-count-check 1.0.0 --input input.json --base-url https://computefirst.net
```

Get the client at [/clients/cli/](/clients/cli/).

## Related tools

- [Table key multiplicity check](/tools/table-key-multiplicity-check): Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.
- [Table count distinct check](/tools/table-count-distinct-check): Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.
- [Table reconciliation check](/tools/table-reconciliation-check): Check a claimed keyed reconciliation of two JSON tables and per-key statuses; report the first wrong key or count.
- [Table aggregate check](/tools/table-aggregate-check): Check claimed COUNT/SUM/AVG/MIN/MAX results over JSON rows exactly (SQL nulls); returns the first mismatch.
- [Table groupby check](/tools/table-groupby-check): Check claimed GROUP BY rows (group keys and aggregate values) over JSON rows; returns the first wrong group or value.
- [CSV anti join](/tools/csv-anti-join): Keep left CSV rows whose exact key tuples do not appear in the right document.