Translation of monad composition in SQL

Question

I am working on a built-in language query library in Scala ( http://github.com/getquill/quill ), and there is one type of monad composition in which I am "trying to create the appropriate SQL query for.

It is possible to generate queries for these cases:

t1.flatMap(a => t2.filter(b => b.s == a.s).map(b => b.s))

   SELECT t2.s FROM t1, t2 WHERE t2.s = t1.s

t1.flatMap(a => t2.map(b => b.s).take(10))

   SELECT x.s FROM t1, (SELECT * FROM t2 LIMIT 10) x

But I can’t figure out how to express this differently:

t1.flatMap(a => t2.filter(b => b.s == a.s).map(b => b.s).take(10))

Is it possible? The question can also be formulated as: Is there a way to express such a data dependence in monadic compositions using applicative joins in SQL?

I am looking for a universal solution so that it can be used for other compositions like these:

t1.flatMap(a => t2.filter(b => b.s == a.s).sortBy(b => b.s % a.s).map(b => b.s).take(10))
t1.flatMap(a => t2.filter(b => b.s == a.s).map(b => b.s).take(10).flatMap(b => t3.filter(c => c.s == b.s/a.s))

I am working on dialects for MySQL, Postgres and H2.

Answer 1

, , . - , :

SELECT *
FROM t1, (
  SELECT t2.s
  FROM t2, t1 AS t1_inner
  WHERE t1_inner.s = t2.s
  LIMIT 10
)

, :

SELECT *
FROM t1, (
  SELECT t2.s
  FROM t2
  WHERE EXISTS (SELECT * FROM t1 t1_inner WHERE t1_inner.s = t2.s)
  LIMIT 10
)