Equivalent to sql cartesian query with data.table

Question

Is there a way to get the equivalent in data.table to the following SQL query?

create C as select * from R,P where 
P.x between R.min_x and R.max_x and P.var2 < R.col3

My problem is that I cannot the the cartesian product of R,P as R would crash, I am happy with any technique (even if it is in several step...)

Typical size are R 1K rows, P 3M rows

EDIT:

library(data.table)
R = data.table(min_x=c(.6,.4,.01,.8),max_x=c(.7,.51,.05,.95),col3=c(.6,.4,1.2,.6))
P = data.table(x=seq(.1,.9,.1),var2=c(1,.4,.3,.2,0,.5,.65,.7,0))
setkey(P, x)
setkey(R,min_x,max_x) #and max_x is always > min_x

#R
#   min_x max_x col3
#1:  0.01  0.05  1.2
#2:  0.40  0.51  0.4
#3:  0.60  0.70  0.6
#4:  0.80  0.95  0.6

#B
#      x var2
#1:  0.1 1.00 => var1 not in any [col1,col2]
#2:  0.2 0.40 => same
#3:  0.3 0.30 => same
#4:  0.4 0.20 => .4 in [.4,.51] but .2 < .4 so NO
#5:  0.5    0 => same 
#6:  0.6 0.50 => .6 in [.6, .7] but .5 < .6 so NO
#7:  0.7 0.65 => .6 in [.6, .7] AND .65 > .6 => SELECTED
#8:  0.8 0.70 => YES
#9:  0.9    0 => NO

So expected result

#  min_x max_x  col3    x var2
#1: 0.60  0.70  0.6  0.70 0.65 
#2: 0.80  0.95  0.6  0.80 0.70

Answer 1

When this FR is implemented (and its links may be useful) :

FR#203 Allow 2 column to specify range in i instead of %between%

it might be :

setkey(B, var1, var2)
B[A[,list(.(col1,col2),.(-Inf,col3))], j]

If that sounds ok? You would want to specify a j which would run per group (per row of i) to save a potential cartesian expansion in memory. But if you really wanted the large table returned, the allow.cartesian flag could be set :

B[A[,list(.(col1,col2),.(-Inf,col3))], allow.cartesian=TRUE]

This can't be done right now of course, so this is just an exploratory answer.