How to use a subquery inside dplyr::filter

Question

I have a SQL query that includes a subquery to filter for some entries:

SELECT *

FROM tbl

WHERE a > someValue AND b IN (

    SELECT b

    FROM tbl

    WHERE a > someValue AND c == someValue

    GROUP BY c, d

)

Using dplyr I would start by:

tbl(tbl) %>%

    filter(a > someValue)

But I am not sure about what is the best way to execute the subquery. I don't want to use a left_join on tbl itself since its performance is inferior.

Answer 1

I think the following code will help you to achieve your desired output:

library(dplyr)

tbl %>%

   filter(a > someValue & b %in% (

       tbl %>%

       filter(a > someValue & c == someValue) %>%

       pull(b) %>% unique))

If you are a beginner and want to know more about R then do check out the R programming course.