how to run SQL queries on tibbles?

I would like to improve my SQL skills a bit using ad-hoc small tibbles. How can I run SQL queries (merges, etc) that will work on my tibbles as if they were databases?

Does that make sense?

Thanks!

Yes, there is a package for that
https://cran.r-project.org/web/packages/sqldf/index.html

2 Likes

thanks! but the package looks abandoned and I wonder about various SQL flavors... perhaps that's the best we can get anyway... thanks

You can use dplyr::left_join, dplyr::right_join, etc. But R does work differently than SQL. I use both SQL & R a lot. I would recommend installing MySQL on your computer and loading some tables to play with. You can use MySQL Workbench. It's all free.

SQL as a language is fairly straight-forward. The challenge is thinking in set-based terms and avoiding things like cursors. There are tutorials with SQL problems that will help you sharpen your SQL thinking skills too.

I would also recommend pulling data from MySQL to R to get better at what SQL should handle and what R should handle regarding datasets.

2 Likes

You could try https://github.com/ianmcook/tidyquery, but the SQL support is still fairly basic.

2 Likes

Thanks! That’s interesting but it is true that the limitations are too important

Go with dbplyr. I'll link some info on how to run SQL queries from it on the database (low data crunch), sort your data to then bring it into R.

https://dbplyr.tidyverse.org/articles/dbplyr.html#generating-queries
-Good start here.


-If you're up for paying for a project-based class Matt Dancho's Learning Labs give you access to 3 SQL based projects (and 20+ other business analytics projects) that show you how to use dbplyr with a business goal, data set and outcome.

See Labs 21, 22, 23; cost is $19/m.
@mdancho

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.