[R] SQL and R

[Gabor Grothendieck]

The advantages of SQL are that
- it can be used from many languages so if you know SQL you can easily
move that part of your code to python, say,
and visa versa
- it is widely used
- it can handle data stored outside of R and possibly otherwise too large for R
- some SQL databases support multiple concurrent users
- depending on the database it may be used to communicate the data to others
- one big difference is that SQL is declarative rather than
imperative.  You tell it what you want rather
than how to get it and the optimizer will figure out how to create a
low level query to get the result.
For example, in the simple case below it will simply scan the BOD
table but in more complex cases
there may be many approaches and it will try to find a good one:

library(sqldf)
sqldf("explain query plan select * from BOD")
##   id parent notused   detail
## 1  2      0       0 SCAN BOD

sqldf("explain select * from BOD")
##    addr       opcode p1 p2 p3   p4 p5 comment
## 1     0         Init  0 10  0 <NA>  0      NA
## 2     1     OpenRead  0  2  0    2  0      NA
## 3     2       Rewind  0  9  0 <NA>  0      NA
## 4     3       Column  0  0  1 <NA>  0      NA
## 5     4 RealAffinity  1  0  0 <NA>  0      NA
## 6     5       Column  0  1  2 <NA>  0      NA
## 7     6 RealAffinity  2  0  0 <NA>  0      NA
## 8     7    ResultRow  1  2  0 <NA>  0      NA
## 9     8         Next  0  3  0 <NA>  1      NA
## 10    9         Halt  0  0  0 <NA>  0      NA
## 11   10  Transaction  0  0  1    0  1      NA
## 12   11         Goto  0  1  0 <NA>  0      NA

On Wed, Dec 11, 2024 at 8:17 AM akshay kulkarni <akshay_e4 using hotmail.com> wrote:
>
> dear Members,
>                             I have recently started studying SQL and MySQL. My question is, what exactly is SQL used for? That is, whatever can be done by SQL, like subsetting and filtering of data sets, can also be done by R. What's, then, the advantage of SQL?  It is OK if you tag this question as offtopic, but I could'nt find any info on the web. Can you please refer me to some online resources that shed some light on this? Finally, how does SQL complement R? Are both dependent?
>
> THanking you,
> Yours sincerely,
> AKSHAY M KULKARNI
>
> [https://s-install.avcdn.net/ipm/preview/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>  Virus-free.www.avast.com<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.



-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com