Combine *_join() + select() in one query

[mgirlich]

A *_join() followed by select() currently produces an unnecessary subquery. This can easily be combined in a single query.

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)

lf <- lazy_frame(x = 1, a = 1)
lf2 <- lazy_frame(x = 1, b = 2)

left_join(lf, lf2, by = "x") %>% 
  select(x, b)

Currently

SELECT `x`, `b`
FROM (
  SELECT `LHS`.`x` AS `x`, `a`, `b`
  FROM `df` AS `LHS`
  LEFT JOIN `df` AS `RHS`
    ON (`LHS`.`x` = `RHS`.`x`)
) `q01`

Should be

SELECT `LHS`.`x` AS `x`, `b`
FROM `df` AS `LHS`
LEFT JOIN `df` AS `RHS`
ON (`LHS`.`x` = `RHS`.`x`)

semi_join(lf, lf2, by = "x") %>% 
  select(x)

Currently

SELECT `x`
FROM (
  SELECT * FROM `df` AS `LHS`
  WHERE EXISTS (
    SELECT 1 FROM `df` AS `RHS`
    WHERE (`LHS`.`x` = `RHS`.`x`)
  )
) `q01`

Should be

SELECT x FROM `df` AS `LHS`
WHERE EXISTS (
  SELECT 1 FROM `df` AS `RHS`
  WHERE (`LHS`.`x` = `RHS`.`x`)
)

^{Created on 2022-05-10 by the reprex package (v2.0.1)}