While sqldf has been around for a while, it's not been a performant choice, since it requires transforming and moving data into a database, albeit one that's in process. So with something like

    df <- data.frame(a=rnorm(5e8), b=runif(5e8))
    sqldf::sqldf("select count(*) from df where a > 3")

works, but takes 200 seconds and doubles the memory footprint of the process whereas

    nrow(df[df$a > 3, ])
    sum(df$a > 3)

take ~1.5 seconds and ~1s respectively on my machine.

I appear to have been too pessimistic about duckdf/duckdb though. It's docs[1] claim that registering an R data.frame as a table

> does not actually transfer data into DuckDB yet.

Which implied to me that it just deferred transfer until the user runs a query. However, in a basic test just now:

    con = dbConnect(duckdb::duckdb(), ":memory:")
    duckdb::duckdb_register(con, "iris_view", iris)
    duckdb::duckdb_register(con, "df_view", df)
    dbGetQuery(con, "select count(*) from df_view where a > 0")

it appears to execute the query directly against the data frame. At least, it runs in ~2.2s, and doesn't allocate more memory. Cool! As you've noted though, it's very new - looks like they released 0.1.0 last May?

I think the point stands: until very recently, SQL-on-dataframes was not a viable choice for anyone working at even moderate scales in R or Python, so preference has been for APIs exposed by libraries (pandas, data.table, dplyr, ...) that offered users reasonable performance, even if SQL would have been be a more ergonomic interface.

[1] https://duckdb.org/docs/api/r