Data Join

Join two tabular data files on common columns.

Cowork note: If relative paths don't resolve, call

mcp__qsv__qsv_get_working_dir

and

mcp__qsv__qsv_set_working_dir

to sync the working directory.

Strategy Selection

mcp__qsv__qsv_joinp

mcp__qsv__qsv_sqlp

mcp__qsv__qsv_sqlp

CROSS JOIN

mcp__qsv__qsv_command

command: "join"

mcp__qsv__qsv_joinp

asof: true

Steps

1. Index both files: Run

   mcp__qsv__qsv_index

   on both files for fast random access.

2. Inspect both files: Run

   mcp__qsv__qsv_headers

   on both files to identify column names. Determine which columns to join on.

3. Profile join columns: Run

   mcp__qsv__qsv_stats

   with

   cardinality: true, stats_jsonl: true

   on both files. Check the cardinality of join columns to determine optimal table order.

4. Choose strategy:

   • If cardinality of join column in file1 > file2, put file1 on the left
   • For

     joinp

     : smaller cardinality table should be on the right for best performance
   • If join condition is complex (non-equi), use

     mcp__qsv__qsv_sqlp

   • If join involves date/time matching where exact dates won't align (e.g., quarterly to monthly, event dates to nearest reporting period), use

     mcp__qsv__qsv_joinp

     with

     asof: true

5. Execute join: Use

   mcp__qsv__qsv_joinp

   for standard joins:

   joinp
     columns1: "id"
     input1: "file1.csv"
     columns2: "id"
     input2: "file2.csv"
     # Join type: omit for inner (default), or set one of:
     # left: true, full: true, cross: true
   

   Or use

   mcp__qsv__qsv_sqlp

   for complex joins:

   SELECT a.*, b.col1, b.col2
   FROM file1 a
   JOIN file2 b ON a.id = b.id AND a.date BETWEEN b.start_date AND b.end_date
   

   For ASOF (nearest-match) joins, use

   mcp__qsv__qsv_joinp

   with

   asof: true

   :

   joinp
     columns1: "date"
     input1: "events.csv"
     columns2: "date"
     input2: "reference.csv"
     asof: true
     strategy: "backward"
     allow_exact_matches: true
   

   • strategy: "backward"

     (default) — match to the last right row with key < left key

   • strategy: "forward"

     — match to the first right row with key > left key

   • strategy: "nearest"

     — match to the numerically closest row (supports

     tolerance

     parameter)
   • Add

     left_by

     /

     right_by

     parameters to restrict matching within subgroups (e.g., per jurisdiction)
   • Add

     allow_exact_matches: true

     to include equal keys (<=, >=); default is strict inequality (<, >)

6. Clean up result: Use

   mcp__qsv__qsv_select

   to remove duplicate join columns or unnecessary columns from the result.

7. Verify: Run

   mcp__qsv__qsv_count

   on the result. Compare with input counts to validate join behavior:
   • Inner join: result <= min(left, right)
   • Left join: result >= left count
   • Full outer: result >= max(left, right)
   • ASOF: result = left count (every left row gets a match or null, like a left join)

Join Column Validation Checklist

Before executing a join, read

.stats.csv

type

sqlp

CAST(col AS INTEGER)

nullcount

sparsity

min

max

mode

mode_count

uniqueness_ratio

mcp__qsv__qsv_count

outliers_percentage

Join Types

joinp

JOIN

--left

LEFT JOIN

--full

FULL OUTER JOIN

--cross

CROSS JOIN

--left-anti

NOT IN

NOT EXISTS

--left-semi

EXISTS

--asof

Notes

• joinp

  uses the Polars engine and is significantly faster than

  join

  for large files
• The stats cache helps

  joinp

  optimize join execution
• For joining on multiple columns, separate column names with commas:

  columns1: "col1,col2"

• Column names must match exactly (case-sensitive)
• If join columns have different names, specify separately:

  columns1: "id"

  ,

  columns2: "customer_id"

• For one-to-many joins, the result will have more rows than either input

• joinp

  handles null values in join columns (nulls don't match by default)
• ASOF joins implicitly enable

  --try-parsedates

  — no need to pass it explicitly
• For ASOF joins with subgroups, use

  --left_by

  and

  --right_by

  (e.g., match nearest date per jurisdiction)
• The

  --tolerance

  option (nearest strategy only) limits how far the nearest match can be: use duration strings for dates (

  1d

  ,

  30d

  ,

  365d

  ) or positive integers for numeric keys
• ASOF joins require sorted join columns; both datasets are auto-sorted unless

  --no-sort

  is set