Membership tests in SQL

[SimonJF]

Say we have a list of IDs referenceIDs, and we want to select all rows in a database which appear in referenceIDs. We might want to write:

for (r <-- reference)
  where (elem(r.reference_id, referenceIDs))
  [r]

Alas, elem is wild. This leaves us with two options:

1. Fetch the entire table and filter in memory
2. Perform a query for each reference ID

Neither are performant. In raw SQL, it's possible to simply build up an SQL statement with multiple OR clauses and tests.

[jamescheney]

For a fixed l, something equivalent to fun (x) {elem(x,l)} should be definable in a tame way, for example by recursion over referenceIDs.

fun contains(l) {
  switch (l) {
    [] -> fun (x) {false}
    x::xs -> fun (y) { x == y || contains(xs)(x) }
}

Then you do:

var p = contains(referenceIDs);
for (r <-- reference)
  where (p(r.reference_id))
  [r]

[jamescheney]

http://www.cs.cornell.edu/conferences/dbpl2011/papers/dbpl11-cheney.pdf