> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Sub-Queries

> Add raw SQL conditions and correlated sub-queries to $where with the sql() helper.

Source: https://uql-orm.dev/querying/sub-queries

When no operator expresses a condition, write it as a [`sql`](https://uql-orm.dev/querying/raw-sql.md) fragment. Its values are still bound as parameters. SQL databases only.

## A raw condition in `$where`

Put it in a `$and` or `$or` array beside ordinary conditions, naming columns with `refs()`:

```ts title="You write"
import { pool } from './uql.config.js';
import { sql, refs } from 'uql-orm';
import { Item } from './shared/models/index.js';

const item = refs(Item);
const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    $and: [{ companyId: 1 }, sql`${item.price} * ${item.stock} > ${10000}`],
  },
});
```

PostgreSQL:

```sql
SELECT "id" FROM "Item" WHERE "companyId" = $1 AND "price" * "stock" > $2
```

## `$exists` / `$nexists` sub-queries

Over a declared relation, use the built-in [relation filters](https://uql-orm.dev/querying/relations.md). Otherwise pass `$exists` or `$nexists` a `sql` callback, which receives the dialect to render a nested query:

```ts title="You write"
import { sql } from 'uql-orm';
import { User, Item } from './shared/models/index.js';

const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    $nexists: sql(({ ctx, dialect, escapedPrefix }) => {
      dialect.find(
        ctx,
        User,
        {
          $select: { id: true },
          $where: {
            // escapedPrefix is the outer table's alias, with its trailing dot.
            companyId: sql(({ ctx }) =>
              ctx.append(`${escapedPrefix}${dialect.escapeId('companyId')}`),
            ),
          },
        },
        { autoPrefix: true },
      );
    }),
  },
});
```

PostgreSQL:

```sql
SELECT "id"
FROM "Item"
WHERE NOT EXISTS
    (SELECT "User"."id" FROM "User" WHERE "User"."companyId" = "Item"."companyId")
```

Capture the outer table’s `escapedPrefix` as above, and escape the column with `dialect.escapeId`: a `refs()` column inside the nested query would resolve against the nested table.

## Understanding `sql`

`sql` has two forms. The template form, ``sql`${item.price} > ${limit}` ``, covers most SQL: values are bound and refs become columns. A `sql` value interpolated into another template is inserted as SQL, not bound, so fragments compose.

The callback form, `sql(({ ctx, dialect, escapedPrefix }) => { ... })`, is for SQL the dialect must render, such as a sub-query. The callback receives:

- `ctx`: the query being built. `ctx.append(sql)` writes SQL as is; `ctx.addValue(value)` binds a value and writes its placeholder.
- `dialect`: the current SQL dialect, for nested queries (`dialect.find(...)`).
- `escapedPrefix`: the escaped alias of the table in scope, with its trailing dot.
- `prefix`: the same alias, unescaped, and empty where there is none.

Instead of writing to `ctx`, the callback can return a string or number, which is appended as SQL. `sql` also works as a computed column in `$select`; see [raw projections](https://uql-orm.dev/querying/querier.md#raw-projections-in-select).

## Next steps

- [Raw SQL](https://uql-orm.dev/querying/raw-sql.md): the `sql` tag and running whole raw statements.
- [Relations](https://uql-orm.dev/querying/relations.md): built-in `EXISTS` and `$size` relation filters.
- [Logical operators](https://uql-orm.dev/querying/logical-operators.md): combining raw conditions with others.