join on: d1.a IS NULL OR d1.a = d2.a

Open
#7,439 5 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
45/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Quiet
Tech stack
r
Domain
data

Research direction

Start by reading data.table's existing join documentation and examining how d1[d2] handles NA values and multiple join conditions. Use the supplied d1 and d2 example as a behavioral test case, and compare the result with the shown DuckDB query. Done means a documented, tested way to express the requested NULL-or-equal join semantics.

Written by the indexing model from the issue text.

Description

feature request joins

There does not seem to be easy way to perform join on conditions like those. We are therefore forced to use duckdb. It would be nice to provide data.table functionality to cover use cases like that.

library(data.table)
d1 = data.table(a = c(1:2,NA,4L), b = c(NA,2:4), x = 1:4)
d2 = data.table(a = c(1L,NA,3:4), b = c(1L,NA,3:4), y = 1:4)

# d1[d2] ??

## duckdb way
conn = duckdb::dbConnect(duckdb::duckdb(), dbdir = ":memory:")
duckdb::duckdb_register(conn, name = "d1", df = d1)
duckdb::duckdb_register(conn, name = "d2", df = d2)
ans = DBI::dbGetQuery(
  conn,
  "SELECT *
FROM d1
LEFT JOIN d2 ON (
  (d1.a IS NULL OR d1.a = d2.a)
  AND
  (d1.b IS NULL OR d1.b = d2.b)
)")
ans
#   a  b x  a  b  y
#1  1 NA 1  1  1  1
#2 NA  3 3  3  3  3
#3  4  4 4  4  4  4
#4  2  2 2 NA NA NA
Dominant language
R
Stars
3.9k
Forks
1.1k
Avg merge
14h 4m
Merged PRs (30d)
4

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from Rdatatable/data.table

All issues in Rdatatable/data.table

Similar issues

More R issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.