CODEWITHSUNDEEP
sqlrdplyrdbplyr
Surya
Surya

Reputation: 171

"NOT IN" sql command in dplyr using result from a different query

I've been using dplyr with dbplyr to generate queries for my sql server and pull the data, but I couldn't find a way to execute the NOT IN sql command.

I'm trying to generate something like this

SELECT * FROM EMPLOYEE WHERE ID NOT IN id_list

where id_list is obtained from another query in dplyr

id_list <- mutate(uid = distinct(id)) %>% select(uid) %>% collect()

id_list is a tbl_Microsoft SQL Server object.

What is the dplyr code to generate the SQL query?

Upvotes: 1

Views: 658

Answers (1)

Matt Dzievit
Matt Dzievit

Reputation: 527

Ronak Shah's code is the correct idea, but is not negating properly. Right now it is just negating the ID instead of negating the results of the comparison.

EMPLOYEE %>% 
filter(!(ID %in% id_list)) %>%
collect()

If you put the comparison in parentheses and then negate the results of the comparison your query will work nicely (I tested it on a DB connection I have).

Upvotes: 2

Related Questions