data.table foverlaps() in R: Fast Overlap Joins on Intervals
The data.table foverlaps() function performs fast overlap joins between two interval-keyed tables in R. It matches each row of x to every row of y whose interval overlaps, with control over the overlap type, minimum overlap, and allowed gap.
foverlaps(x, y) # default "any" overlap foverlaps(x, y, type = "within") # x interval fully inside y foverlaps(x, y, type = "start") # intervals share a start foverlaps(x, y, type = "equal") # intervals match exactly foverlaps(x, y, mult = "first") # one match per x row foverlaps(x, y, nomatch = NULL) # inner join, drop unmatched foverlaps(x, y, maxgap = 5) # treat 5-unit gap as overlap foverlaps(x, y, which = TRUE) # return row indices only
Need explanation? Read on for examples and pitfalls.
What foverlaps() does
foverlaps() joins two data.tables by interval overlap instead of by equality. Each row in x is matched to every row in y whose [start, end] interval intersects with the x row's interval, and the matched columns are returned side by side. It is the data.table answer to GenomicRanges-style overlap joins.
The function is written in C and runs without a quadratic scan, so it scales to millions of rows. Both tables stay in memory; no SQL window is needed.
Before any overlap join, y must be keyed on at least two columns that define the interval, set with setkey(y, start, end) or with setkeyv().
foverlaps() syntax and arguments
The signature looks long, but only the first two arguments are mandatory. The defaults cover the most common case: an "any" overlap, all matches returned, and the keys inferred from y.
Each argument shapes how the overlap is detected and which matches are returned:
| Argument | Purpose |
|---|---|
x, y |
Two data.tables. y must be keyed; x must have matching interval columns. |
by.x, by.y |
Column names defining the interval, last two are the start and end. |
type |
Overlap rule: "any", "within", "start", "end", or "equal". |
mult |
When multiple y rows match, return "all", "first", or "last". |
nomatch |
NA keeps unmatched x rows (left join), NULL drops them (inner join). |
maxgap |
A non-overlap up to this distance still counts as a match. |
minoverlap |
Minimum overlap length required to count as a match. |
which |
If TRUE, return only row indices instead of the merged columns. |
foverlaps() is the data.table equivalent of GenomicRanges' findOverlaps() and of dplyr's experimental join_by(overlaps()) helper. Unlike SQL, no BETWEEN self-join is needed.foverlaps() examples
These examples use small inline tables so each overlap is easy to verify by eye. Each example targets a different real use case for interval joins.
A basic call matches sessions to events. Here every event whose timestamp falls inside a session window is paired with that session.
Use type = "within" to keep only matches where the x interval lies fully inside a y interval. This is strict containment, not partial overlap.
For pricing or rate lookups, you often want exactly one matching window per row. Pass mult = "first" so each x row returns its first match instead of every overlap.
Self-joining a table on itself finds rows whose intervals overlap any other row. This is the standard recipe for detecting double-booked rooms or duplicate ranges.
foverlaps() lets you express "this point falls inside that range" without writing a Cartesian self-join. Once you frame your data as intervals, joins that looked expensive become a single sorted scan.foverlaps() vs other join tools
foverlaps() is the right pick only when the match rule involves an interval. For equality, nearest-neighbour, or row-wise window logic, a different tool is faster or simpler.
| Tool | Use it when |
|---|---|
foverlaps(x, y) |
You need to join on overlap of [start, end] ranges. |
y[x, on = .(id)] |
You are joining on equal keys, not intervals. |
y[x, on = "date", roll = TRUE] |
You want the nearest preceding key, not an overlap. |
dplyr::join_by(overlaps()) |
You are already in dplyr and the data is small. |
GenomicRanges::findOverlaps() |
You work with biological ranges and need Bioconductor methods. |
Use maxgap to relax the overlap rule. With maxgap = 5, intervals that are within five units of touching still count as overlapping, which is useful for "near match" logic in genomics or sensor data.
Common pitfalls
Most foverlaps() errors trace back to keys, column order, or NA endpoints. These three mistakes cover almost every confused bug report.
Calling foverlaps() before keying y raises an error because the function cannot guess which columns hold the interval. Always run setkey(y, group, start, end) first; the last two key columns must be the start and end.
Mixing up start and end in x lets the function read inverted intervals and silently miss matches. The convention [low, high] must hold for every row in both tables.
Joining on a single point in x still requires two columns, so write t2 = t1 for point events. A missing endpoint becomes NA and is treated as non-overlapping, which usually returns no matches.
y is keyed on integers but x passes numerics or dates, the join silently returns zero matches. Confirm class(y$start) equals class(x$t1) before debugging logic.Try it yourself
Try it: Build a 2-row windows table with start = c(0, 10) and end = c(5, 15), key it, then use foverlaps() to match a single event at t = 3. Save the result to ex_match.
Click to reveal solution
Explanation: After keying ex_windows on g, start, end, foverlaps() knows which columns define the interval. The event t = 3 falls inside the first window only, so the second window is filtered out.
Related data.table functions
These functions pair naturally with foverlaps() for keyed and joined work:
setkey()sorts and indexes the right-hand table before any overlap join.merge()performs a standard equi-join when no interval logic is needed.shift()lags or leads a column, often used to build a[start, end]pair.rbindlist()stacks the rows returned from many overlap joins quickly.
See the official data.table foverlaps reference for the full argument list.
FAQ
What is the difference between foverlaps and merge in data.table?
merge() (or the y[x, on = ...] syntax) matches rows by equal key values. foverlaps() matches rows whose intervals overlap, so the join condition is x.start <= y.end AND x.end >= y.start rather than x.id == y.id. Use merge() when you have shared identifiers and foverlaps() when you need interval logic such as "event falls inside session" or "two date ranges intersect".
Why does foverlaps require setkey on y?
The function uses the key on y to identify the interval columns and to drive a binary-search-based scan that avoids comparing every pair of rows. Without a key it cannot know which columns are the start and end of the range, so it stops with an explicit error. Always call setkey(y, group_cols, start, end) first; the last two key columns must be the interval bounds.
Can foverlaps join on more than just intervals?
Yes. Any columns listed before start and end in the key are treated as equi-join columns. For example, setkey(y, region, from, to) joins rows only when region matches exactly and the date range overlaps. This is how you join interval data within groups, such as price windows per region or sessions per user.
How is foverlaps different from a rolling join?
A rolling join (y[x, on = "date", roll = TRUE]) finds the single nearest preceding key value for each row. An overlap join returns every y row whose interval intersects the x interval. Use rolling joins for snapshot-style lookups, such as the most recent price before a transaction, and foverlaps() when one or both sides has a range with a defined start and end.