7.10.18. SQL-17 — Subqueries

Four daslib/linq_boost macros lower to SQL’s IN / NOT IN / EXISTS / NOT EXISTS forms when used inside a _where predicate under _sql(...):

Source shape	Emitted SQL
`x._in(subquery)`	`<x> IN (<subquery-sql>)`
`x._not_in(subquery)`	`<x> NOT IN (<subquery-sql>)`
`subquery._any()`	`EXISTS (<subquery-sql>)`
`subquery._any(predicate)`	`EXISTS (... WHERE <predicate>)`
`subquery._none()`	`NOT EXISTS (<subquery-sql>)`
`subquery._none(predicate)`	`NOT EXISTS (... WHERE <predicate>)`

7.10.18.1. Why explicit positive/negative names

Negated forms have explicit names (_not_in, _none) rather than !_in(...) / !_any(...). The _sql AST walker pattern-matches on the macro’s textual name; a leading ! would require deep AST walking that fights with daslang’s expansion order. Expect this convention to repeat for any future predicate primitive with a negated form.

7.10.18.2. IN / NOT IN with a single-column subquery

For IN-style subqueries, project a single column with _select(_.Col) so the IN list shape matches.

let with_orders <- _sql(db |> select_from(type<User>)
                          |> _where(_.Id._in(db |> select_from(type<Order>) |> _select(_.UserId))))
// SELECT "Id", "Name", "Active" FROM "Users"
//   WHERE "Id" IN (SELECT "UserId" FROM "Orders")

let no_orders <- _sql(db |> select_from(type<User>)
                        |> _where(_.Id._not_in(db |> select_from(type<Order>) |> _select(_.UserId))))
// ... WHERE "Id" NOT IN (...)

7.10.18.3. IN / NOT IN with a captured collection

_in / _not_in also accept a captured daslang collection (an array<T> in scope). It is JSON-encoded into a single bind and queried through SQLite’s table-valued json_each — EF Core 8’s default IN-list strategy:

let want_ids <- [1, 2]
let some <- _sql(db |> select_from(type<User>)
                   |> _where(_.Id._in(want_ids)))
// ... WHERE "Id" IN (SELECT value FROM json_each(?))

NOT IN on a nullable column adds the OR <x> IS NULL guard so three-valued logic doesn’t silently drop NULL rows:

// nullable column:
// ... WHERE ("Tag" NOT IN (SELECT value FROM json_each(?)) OR "Tag" IS NULL)

7.10.18.4. EXISTS / NOT EXISTS

let some_big <- _sql(db |> select_from(type<User>)
                       |> _where((db |> select_from(type<Order>))._any(_.Total > 200)))
// ... WHERE EXISTS (SELECT ... FROM "Orders" WHERE "Total" > ?)

let no_huge <- _sql(db |> select_from(type<User>)
                      |> _where((db |> select_from(type<Order>))._none(_.Total > 1000)))
// ... WHERE NOT EXISTS (SELECT ... FROM "Orders" WHERE "Total" > ?)

7.10.18.5. Uncorrelated only

Only uncorrelated subqueries are supported — the inner WHERE references only the subquery’s own row (_), not outer columns. Correlated subqueries (referring to outer columns from inside the subquery’s WHERE) are not yet supported.