Skip to content

Instantly share code, notes, and snippets.

@fwbrasil
Last active September 10, 2015 08:47
Show Gist options
  • Save fwbrasil/2db2b6ac2b86fe820442 to your computer and use it in GitHub Desktop.
Save fwbrasil/2db2b6ac2b86fe820442 to your computer and use it in GitHub Desktop.
Monadic joins to SQL
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
t1.flatMap(a => t2.filter(b => b.s == a.s).map(b => b.s).take(10))
Is it possible to translate this composition to SQL?
@fwbrasil
Copy link
Author

fwbrasil commented Sep 9, 2015

This is not valid SQL:

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

@fwbrasil
Copy link
Author

fwbrasil commented Sep 9, 2015

This one is wrong. It'll take in consideration only the first 10 rows of t2, not the first 10 rows that match the filter.

SELECT x.s FROM t1, (SELECT ROWNUM r, t2.* FROM t2) x WHERE t2.s = t1.s AND x.r <= 10

@fwbrasil
Copy link
Author

I've found one alternative, but it's not generic enough to cover other similar compositions:

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

The normalized composition would be:

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

Example of composition that can't be normalized using this approach:

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment