dplyrからSQLへの翻訳はどのように行われているのか #SQL

はじめに

dplyr、そしてデータベースを操作する際にバックエンドとして働くdbplyrがどのようにRのコードをSQLへ翻訳しているのかを見ていきます。

まずは必要なパッケージをインストールし、ロードしておきます。

install.packages("dbplyr")
install.packages("dplyr")

library(dbplyr)
library(dplyr)

ベクトル演算

はじめにRでベクトル演算として表現できる単純な計算や集計について見ていきます。この種の単純な操作はRからSQLへ翻訳するのも比較的容易ですが、若干の違いもあります。

変数と文字列

標準SQLではダブルクォートで括った文字列はオブジェクト名として、シングルクォートで括られた文字列は文字列として扱われます（データベースによってはダブルクォートで括られた文字列を文字列リテラルとして許容する場合もあります）。そのため、dplyrはオブジェクト名や文字列を以下のように変換します。

オブジェクト名（引用符なし、またはバッククォートで括られた文字列）→ダブルクォートで括られる
文字列（ダブルクォートまたはシングルクォートで括られた文字列）→シングルクォートで括られる

実際に見てみましょう。式をSQLに翻訳した結果を得るには、translate_sql()関数を以下のように使います。

translate_sql(x)

output

<SQL> "x"

translate_sql("x")

output

<SQL> 'x'

数字

数字、特に整数の扱いについては、若干注意する必要があります。SQLでは単に1と書いた場合は整数型として扱われます。一方、Rでは1と書いた場合は実数として扱われます。そのため、Rの1をSQLに翻訳すると1.0と翻訳されてしまいます。

translate_sql(1)

output

<SQL> 1.0

Rで整数型を使いたい場合は、明示的に1Lと記述します。これはSQLでは1に翻訳されます。

translate_sql(1L)

output

<SQL> 1

関数

多くの関数が少し名前の違うSQLの関数に翻訳されます。

translate_sql(x == 1 && (y < 2 || z > 3))

output

<SQL> "x" = 1.0 AND ("y" < 2.0 OR "z" > 3.0)

SQLとして実行できるように、引数の順序が変更される場合もあります。

translate_sql(log(x, 10))

output

<SQL> LOG(10.0, "x")

翻訳される関数

次に示す関数は、どのようにSQLに翻訳すべきかをdplyrが知っています。

基本算術演算子: +, -, *, /, %%, ^
数学関数: abs, acos, acosh, asin, asinh, atan, atan2, atanh, ceiling, cos, cosh, cot, coth, exp, floor, log, log10, round, sign, sin, sinh, sqrt, tan, tanh
比較演算子: <, <=, !=, >=, >, ==, %in%
論理演算子: &, &&, |, ||, !, xor
単純集計関数: mean, sum, min, max, sd, var
文字列操作関数: tolower, toupper, trimws, nchar, substr
型変換: as.numeric, as.integer, as.character

しかし、これらの関数であっても翻訳は完璧に行われるとは限りません。これは、dplyrが「何をしているか」よりも「何を意味しているか」を重視しているからです。dplyrが意味の妥当性を重視するために、多少手の込んだSQLが生成される場合もあります。これはミスを防ぐありがたい気遣いになる場合もありますが、予期せぬバグの原因となることもあります。この点が気になる方は、一足先にこの記事の最後にある集合演算の説明を見てみると良いかもしれません。

また、RとSQLの関数は名前が似ていても完全に同じというわけではないので、上記の関数でも翻訳が失敗してしまうことがあります。たとえば、SQLがRの関数の引数に対応していない場合には翻訳は上手くいきません。例えばmean()にはトリム平均を計算するためのtrimという引数がありますが、これはSQLのAVG関数にはありませんから、翻訳ができません。

translate_sql(mean(x, trim = 0.1))

output

#> mean(x, trim = 0.1) でエラー: 使われていない引数 (trim = 0.1)

翻訳されない関数

翻訳が失敗するわけではなく、単に翻訳されない関数もあります。これにはdplyrが翻訳方法を知らないすべての関数が該当します。このような関数はそのままSQLになります。これはつまりデータベース関数がそのまま使えるということを意味しています。

例えば次の例はRの式としては動作しませんが、翻訳されたSQLは動作します。

translate_sql(glob(x, y))

output

<SQL> GLOB("x", "y")

translate_sql(x %like% "ab%")

output

<SQL> "x" LIKE 'ab%'

データベース間の差

データベースの種類によって使える関数や書き方は若干異なります。dplyrは接続しているデータベースの種類から自動的にどのデータベースに対応した翻訳をすべきか判断します。

translate_sql()にはcon引数があり、これにコネクションオブジェクトを渡すと接続先のデータベースに応じた翻訳をしてくれます。なお、デフォルトでは、（概ね）SQL-92に準拠するSQLが生成されます。

ウィンドウ関数

一部の関数はウィンドウ関数として翻訳されます。ウィンドウ関数はRやdplyrと比べてもかなり柔軟な表現が可能なので、翻訳も複雑になります。

まず、ウィンドウ関数について簡単に説明します。

ウィンドウ関数は以下のような構造を持っています。

[式] OVER ([パーティション句] [オーダー句] [フレーム句])

式はavg("x")のように関数名と変数名からなります。どのようなウィンドウ関数が使えるかはデータベースの種類によりますが、一般的にはランキング関数とlead, lag, nth, first, last, count, min, max, sum, avg, stddevといった関数がサポートされています。
パーティション句はウィンドウ関数を適用するグループの分割方法を指定します。これはGROUP BYと似たようなものです。実際、dplyrではgroup_by()によるグループ化は文脈に応じてGROUP BY句にもPARTITION BY句にも翻訳される可能性があります。GROUP BYとの主な違いは、結果を1行に集約しないという点です。
オーダー句は順序を制御します。これは順序が結果に影響するような計算で重要となります。例えばランキング関数を使う場合や、累積和を求めるような場合です。あるべきオーダー句が無い場合は、エラーになったり結果が不定になったりします。データベースは指定しない限り行の順序が定まらないためです。
フレーム句は現在の行を基準として、前後どれだけの範囲を関数に渡すかを指定します。例えば最初から現在の行までの値をsumに渡せば累積和を計算できますし、前後何行かをavgに渡せば移動平均が計算できます。フレーム句の指定方法には無数のバリエーションがありますが、よく使われるのは以下の3つです。
- 繰り返し: BETWEEN UNBOUND PRECEEDING AND UNBOUND FOLLOWING
  - （パーティションで区切られた）範囲のすべてを対象とします。例えば関数がavgであれば、すべての行に平均値が表示されます。
- 累積: BETWEEN UNBOUND PRECEEDING AND UNBOUND CURRENTROW
  - 累積和を計算するような場合に使います。
- ローリング: BETWEEN 2 PRECEEDING AND 2 FOLLOWING
  - 移動平均を計算するような場合に使います。

ウィンドウ関数がどのように機能するか、まずSQLを書いて確認してみましょう。

ローカルでコネクションを作成してあれこれ試すにはSQLiteを使うのが楽ですが、今のところRSQLiteに同梱されているSQLiteはウィンドウ関数に対応していません（新しいバージョンのSQLiteはウィンドウ関数に対応したようです）。

そこで今回はSparkを使用します。sparklyrパッケージを使えばSparkのインストールからコネクションの作成までR上で完結するので簡単です。

# インストールしていなければsparklyrとsparkをインストールする
#install.packages("sparklyr")
library(sparklyr)
#spark_install(version = "2.1.0")
sc <- spark_connect(master = "local", version = "2.1.0")
spark_mtcars <- copy_to(sc, mtcars, "mtcars")

ではウィンドウ関数を使ってみましょう。

SELECT
    mpg, 
    cyl, 
    sum(cyl) OVER (
      PARTITION BY cyl
      ORDER BY mpg
      ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) as sum_cyl
  FROM mtcars
  LIMIT 10

mpg	cyl	sum_cyl
17.8	6	18
18.1	6	24
19.2	6	30
19.7	6	30
21.0	6	30
21.0	6	24
21.4	6	18
21.4	4	12
21.5	4	16
22.8	4	20

cylでグループ化し、mpgでソートし、cylについて前後2行の範囲の合計を求めています。グループの境界の処理にも注目してみて下さい。

ウィンドウ関数への翻訳

同じ関数でも集計関数として計算される場合と、ウィンドウ関数として翻訳される場合があります。

集計関数は値を一つにまとめる働きをします。つまり、summarise()を使って値を集約するような式は集計関数として翻訳されます。

spark_mtcars %>% 
  summarise(mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()

output

<SQL>
SELECT AVG(`mpg`) AS `mean`
FROM `mtcars`

上記で使用したshow_query()は生成されるSQLを確認するもう一つの方法です。summarise()などを利用した場合は実際に生成されるクエリを確認するためにこちらを使う必要があります。

ウィンドウ関数の特徴は行ごとに値が計算されるという点にあります。つまり、mutate()やtransmute()を使って列を追加するような式がウィンドウ関数として翻訳されます。

spark_mtcars %>% 
  mutate(mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, avg(`mpg`) OVER () AS `mean`
FROM `mtcars`

パーティション句の指定は、group_by()を通じて行います。

spark_mtcars %>% 
  group_by(cyl) %>% 
  mutate(mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, avg(`mpg`) OVER (PARTITION BY `cyl`) AS `mean`
FROM `mtcars`

オーダー句の指定方法はいくつかあり、関数の種類によって多少異なります。例えばランキング関数の場合は明示的な指定が不要です。

spark_mtcars %>% 
  mutate(rank = rank(mpg)) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, rank() OVER (ORDER BY `mpg`) AS `rank`
FROM `mtcars`

累積関数の場合は、順序によって結果が変わりますから、明示的に指定する必要があります。指定しないと警告が出ます。まず、以下のようにorder_by()で関数を包むやり方があります。

spark_mtcars %>%
  mutate(cumsum = order_by(mpg, cumsum(mpg))) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM `mtcars`

window_order()を使えば、パイプ処理の途中で順序を指定できます。

spark_mtcars %>% 
  window_order(mpg) %>% 
  mutate(cumsum = cumsum(mpg)) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM `mtcars`

arrange()を使って全体の順序を指定している場合には、その順序がオーダー句でも使用されます。

spark_mtcars %>% 
  arrange(mpg) %>% 
  mutate(cumsum = cumsum(mpg)) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM (SELECT *
FROM `mtcars`
ORDER BY `mpg`) `jhxezjnagn`

window_order()とarrange()は併用することもできます。window_order()はウィンドウ関数のオーダ句にのみ作用するためです。

spark_mtcars %>% 
  arrange(cyl) %>% 
  window_order(mpg) %>% 
  mutate(cumsum = cumsum(mpg)) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, sum(`mpg`) OVER (ORDER BY `cyl`, `mpg` ROWS UNBOUNDED PRECEDING) AS `cumsum`
FROM (SELECT *
FROM `mtcars`
ORDER BY `cyl`) `xogpmlsput`

フレーム句は累積関数などでは自動的に設定されますが、window_frame()を使って明示的に指定することもできます。最初に示したSQLをdplyrで書くなら次のようになります

spark_mtcars %>% 
  select(mpg, cyl) %>% 
  group_by(cyl) %>% 
  window_frame(-2, 2) %>% 
  window_order(mpg) %>% 
  mutate(sum_cyl = sum(cyl, na.rm = TRUE))

output

# Source:     spark<?> [?? x 3]
# Groups:     cyl
# Ordered by: mpg
     mpg   cyl sum_cyl
 * <dbl> <dbl>   <dbl>
 1  17.8     6      18
 2  18.1     6      24
 3  19.2     6      30
 4  19.7     6      30
 5  21       6      30
 6  21       6      24
 7  21.4     6      18
 8  21.4     4      12
 9  21.5     4      16
10  22.8     4      20
# ... with more rows

なお、UNBOUND PRECEEDINGに対応するのは-Inf、 UNBOUND FOLLOWINGに対応するのはInfとなっています。

単一テーブルの操作

既に例を見ていますが、select()やmutate()はSELECT句を変更します。

spark_mtcars %>% 
  select(mpg) %>% 
  show_query()

output

<SQL>
SELECT `mpg`
FROM `mtcars`

spark_mtcars %>% 
  mutate(mpg2 = mpg * 2) %>% 
  show_query()

output

<SQL>
SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, `mpg` * 2.0 AS `mpg2`
FROM `mtcars`

filter()はWHERE句に対応します。

spark_mtcars %>% 
  filter(cyl == 4) %>% 
  show_query()

output

<SQL>
SELECT *
FROM `mtcars`
WHERE (`cyl` = 4.0)

arrange()はORDER BY句に対応します。

spark_mtcars %>% 
  arrange(mpg) %>% 
  show_query()

output

<SQL>
SELECT *
FROM `mtcars`
ORDER BY `mpg`

group_by()はsummarise()と共に使うことでGROUP BY句を生成します。

spark_mtcars %>% 
  group_by(cyl) %>% 
  summarise(mpg_mean = mean(mpg, na.rm = TRUE)) %>% 
  show_query()

output

<SQL>
SELECT `cyl`, AVG(`mpg`) AS `mpg_mean`
FROM `mtcars`
GROUP BY `cyl`

複数テーブルの操作

JOIN

各種の結合は概ね次のように翻訳されます。

R	SQL
`inner_join(x, y)`	`SELECT * FROM x JOIN y ON x.a = y.a`
`left_join(x, y)`	`SELECT * FROM x LEFT JOIN y ON x.a = y.a`
`right_join(x, y)`	`SELECT * FROM x RIGHT JOIN y ON x.a = y.a`
`full_join(x, y)`	`SELECT * FROM x FULL JOIN y ON x.a = y.a`
`semi_join(x, y)`	`SELECT * FROM x WHERE EXISTS (SELECT 1 FROM y WEHRE x.a = y.a)`
`anti_join(x, y)`	`SELECT * FROM x WHERE NOT EXISTS (SELECT 1 FROM y WHERE x.a = y.a)`

動作を確認しておきましょう。2つのテーブルを読み込んでおきます。

members <- copy_to(sc, band_members)
instruments <- copy_to(sc, band_instruments)
instruments2 <- copy_to(sc, band_instruments2)

membersとinstrumentsの2つはいずれも3行2列の小さなテーブルで、nameという同じ名前の列を1つもっています。そして、nameの一部は値が重複していますが、一部は異なります。

members

output

# Source: spark<band_members> [?? x 2]
  name  band   
* <chr> <chr>  
1 Mick  Stones 
2 John  Beatles
3 Paul  Beatles

instruments

output

# Source: spark<band_instruments> [?? x 2]
  name  plays 
* <chr> <chr> 
1 John  guitar
2 Paul  bass  
3 Keith guitar

instruments2はinstrumentsと中身は同じですが列名が異なります。

instruments2

output

# Source: spark<band_instruments2> [?? x 2]
  artist plays 
* <chr>  <chr> 
1 John   guitar
2 Paul   bass  
3 Keith  guitar

`inner_join()`

inner_join()は共通部分を抽出します。

inner_join(
  members, instruments,
  by = "name"
  )

output

# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass

他のJOINでも同じですが、SQLでON以下に相当する部分はby =で指定します。結合する2つの列名が同じなら上記例のような指定で大丈夫ですが、異なる場合はby = c("name" = "artist")のようにc()を使って指定します。

inner_join(
  members, instruments2,
  by = c("name" = "artist")
)

output

# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass

同じ列名を結合のキーとする場合は列名指定自体を省略することもできます。

inner_join(members, instruments)

output

Joining, by = "name"
 # Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass

`left_join()`

left_join()は左側のテーブルに存在する項目をすべて残しつつ、右側のテーブルを結合します。右側のテーブルのうち、キー列の項目が存在しない部分はNAで埋められます。

left_join(
  members, instruments,
  by = "name"
)

output

# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 Mick  Stones  <NA>  
2 John  Beatles guitar
3 Paul  Beatles bass

`right_join()`

right_join()はleft_join()の逆です。右側のテーブルが温存されます。

right_join(
  members, instruments,
  by = "name"
)

output

# Source: spark<?> [?? x 3]
   name  band    plays 
* <chr> <chr>   <chr> 
1 John  Beatles guitar
2 Paul  Beatles bass  
3 Keith <NA>    guitar

`full_join()`

full_join()はleft_join()とright_join()を組み合わせたようなものです。両方のテーブルのすべての項目が温存されます。

full_join(
  members, instruments,
  by = "name"
)

output

# Source: spark<?> [?? x 3]
  name  band    plays 
* <chr> <chr>   <chr> 
1 Keith <NA>    guitar
2 Mick  Stones  <NA>  
3 Paul  Beatles bass  
4 John  Beatles guitar

`semi_join()`

semi_join()は少し分かりにくい結合ですが、キー列の項目が右側のテーブルに存在する左側のテーブルの行を返します。

まずクエリを確認してみます。

semi_join(
  members, instruments,
  by = "name"
) %>% show_query()

output

<SQL>
SELECT * FROM `band_members` AS `TBL_LEFT`

WHERE EXISTS (
  SELECT 1 FROM `band_instruments` AS `TBL_RIGHT`
  WHERE (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`)
)

WHERE EXISTS以下にサブクエリが書いてありますが、このサブクエリを実行して行の存在する部分が真、存在しない部分が偽として左側のテーブルのフィルタリングが行われます。WHERE EXISTS内部で入れ子になっているWHERE句に注目すると理解しやすいでしょう。「キー列の項目が右側のテーブルに存在する」という部分がここで指定されています。このサブクエリは行が返るかどうかだけが重要で値はどうでも良いのでSELECT 1となっています。

これをどのようなタイミングで使うかですが、例えば手元に何らかの集計、フィルタリングが行われたテーブルがあるとしましょう。

members_filtered <- members %>% 
  filter(name == "Mick") %>% 
  mutate(some_value = 1) %>% 
  collect()
members_filtered

output

# A tibble: 1 x 3
  name  band   some_value
  <chr> <chr>       <dbl>
1 Mick  Stones          1

この集計後の行に対して対応する元のテーブルの行を抽出したい場合に、semi_joinが利用できます。

semi_join(members, members_filtered, by = c("name"), copy = TRUE)

output

# Source: spark<?> [?? x 2]
  name  band  
* <chr> <chr> 
1 Mick  Stones

この挙動は2つのテーブルの共通部分を抽出するintersect()とよく似ていますが、抽出に用いるのはキーとして指定した列だけという点で異なります。また、抽出後のテーブルに右辺のテーブルは含まれません。これは右辺のテーブルのキーで左辺のテーブルをフィルタリングしているとも言えるので、semi joinは後述のanti joinと合わせてfiltering joinと呼ばれています。

なお、copy = TRUEを指定すると、第一引数と同じ場所に第二引数のテーブルをコピーした上でjoinが実行されます。第一引数がデータベース上のテーブルであればテンポラリテーブルが作成されます。コピーをするので実行コストは上がりますが、この仕組みを利用すれば異なるデータベース間、ローカルとリモート間のテーブルであってもjoinが可能です。

`anti_join()`

anti_join()はsemi_join()の逆です。左辺のテーブルのうちsemi_join()で選択されないものが返ります。クエリとしてはEXISTSがNOT EXISTSに変わっているだけです。

anti_join(
  members, instruments,
  by = "name"
) %>% show_query()

output

<SQL>
ELECT * FROM `band_members` AS `TBL_LEFT`

WHERE NOT EXISTS (
  SELECT 1 FROM `band_instruments` AS `TBL_RIGHT`
  WHERE (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`)
)

semi_join()がintersect()と似ているように、anti_join()はsetdiff()と似ています。

集合演算

集合演算は次のように翻訳されます。

R	SQL
`intersect(x, y)`	`SELECT * FROM x INTERSECT SELECT * FROM y`
`union(x, y)`	`SELECT * FROM x UNION SELECT * FROM y`
`union_all(x, y)`	`SELECT * FROM x UNION ALL SELECT * FROM y`
`setdiff(x, y)`	`SELECT * FROM x EXCEPT SELECT * FROM y`

intersect()は共通部分、union()は和集合、setdiff()は差集合を計算します。union_all()はunion()に似ていますが重複を除外しません。rbind()と同じようなものと考えると良いでしょう。

集合演算の翻訳は単純なように見えますが、大きな違いがあります。dplyrは列名を考慮するのです。

まずSQLの例を確認しておきましょう。SQLで集合演算を行う場合、列名はそれほど重要ではありません。例えば列名が違う2つのテーブルでもそのまま結合できます。

SELECT * FROM band_instruments
UNION
SELECT * FROM band_members

name	plays
Keith	guitar
Mick	Stones
John	guitar
John	Beatles
Paul	Beatles
Paul	bass

というよりそもそも列名なんか見ていませんから、同じ列名があっても無視して列の順序どおりに結合します。

SELECT name, plays FROM band_instruments
UNION
SELECT plays, name FROM band_instruments

name	plays
Keith	guitar
guitar	Keith
John	guitar
guitar	John
Paul	bass
bass	Paul

一方、dplyrの場合は列名を重視し、丁寧に扱います。例えば異なる列名を含むテーブルをunion()で結合すると、full_join()のような結果が得られます。

union(instruments, members)

output

# Source: spark<?> [?? x 3]
  name  plays  band   
* <chr> <chr>  <chr>  
1 Keith guitar <NA>   
2 Mick  <NA>   Stones 
3 John  guitar <NA>   
4 John  <NA>   Beatles
5 Paul  <NA>   Beatles
6 Paul  bass   <NA>

クエリを確認するとわかりますが、結合相手のテーブルにしか存在しない列は生成され、列の順序は揃えられてから結合が行われています。

union(instruments, members) %>% show_query()

output

<SQL>
(SELECT `name`, `plays`, NULL AS `band`
FROM (SELECT *
FROM `band_instruments`) `ifrioiyfiu`)
UNION
(SELECT `name`, NULL AS `plays`, `band`
FROM (SELECT *
FROM `band_members`) `tpievfioxx`)

つまり、列の順序が違うテーブルでも、列名を考慮して結合が行われます。

union(
  instruments %>% select(name, plays),
  instruments %>% select(plays, name)
)

output

# Source: spark<?> [?? x 2]
  name  plays 
* <chr> <chr> 
1 Keith guitar
2 John  guitar
3 Paul  bass

集合演算は書いた通りに動くことを重視するSQLと、書いてある文の意味を重視するdplyrの思想の違いが現れている例とも言えるでしょう。

参考

SQL translation