Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
色んなSQLをScalikeJDBCで書くと?

色んなSQLをSquerylで書くと? のパクリです。

テーブル名とかカラム名はちょっと弄りました。

LEFT JOIN + IS NULL

SQL

SELECT *
  FROM User u
  LEFT JOIN Group g on (u.groupId = g.id)
  WHERE g.id IS NULL

ScalikeJDBC

select
  .from(User as u)
  .leftJoin(Group as g).on(u.groupId, g.id)
  .where.isNull(g.id)

相関サブクエリー

SQL

SELECT *
  FROM User u
  WHERE EXISTS (
    SELECT g.id
      FROM Group g
      WHERE u.groupId = g.id
  )

ScalikeJDBC

select 
  .from(User as u)
  .where.exists(
    select(g.result.id)
      .from(Group as g)
      .where.eq(u.groupId, g.id)
  )

(注) 1.6.1 から

複数のカラムでgroup by

SQL

SELECT u.groupId, u.teamId, count(*)
  FROM User u
  GROUP BY u.groupId, u.teamId

ScalikeJDBC

select(u.result.groupId, u.result.teamId, sqls.count)
  .from(User as u)
  .groupBy(u.groupId, u.teamId)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment