# SQLCute — Usage Guide

🌍 Language: English | Español

Introduction

SQLCute builds parameterized SQL through a fluent method chain. The entry point is always TQuery.New:

uses Daf.SQLCute, Daf.SQLCute.Compiler;

var Q := TQuery.New          // IQuery — ref-counted, no Free needed
  .From('products')
  .Where('status', 'active')
  .OrderBy('name')
  .Limit(50);

var Compiler: IQueryCompiler := TAnsiSqlCompiler.Create;  // ref-counted: it needs an owner
var R := Compiler.Compile(Q);
// R.SQL      → 'SELECT * FROM products WHERE status = ? ORDER BY name LIMIT 50'
// R.Bindings → ['active']

TSQLResult always contains:

Field	Type	Description
`SQL`	`string`	The compiled SQL string with `?` placeholders (in the native style, dialect compilers write `$1`, `:p1`, `@p0`)
`Bindings`	`TArray<Variant>`	Positional values matching the placeholders

Pass R.SQL and R.Bindings to your database driver — SQLCute never executes queries itself.

SELECT & FROM — columns, DISTINCT, aggregates, ORDER BY, LIMIT/OFFSET
WHERE — equality, comparisons, NULL, BETWEEN, IN, EXISTS, groups, columns
JOINs — INNER, LEFT, RIGHT, CROSS, FULL OUTER, callbacks
GROUP BY / HAVING — grouping and aggregate filters
Set Operations — UNION, INTERSECT, EXCEPT, pagination
DML — INSERT, UPDATE, DELETE
Subqueries & CTEs — derived tables, WITH, recursive CTEs
String Operations — LIKE, starts/ends/contains
Date Operations — WhereDate, per-dialect date casting
Dialect Compilers — quoting, placeholders, dialect matrix

The Compiler Pattern

Every IQuery is compiler-agnostic. You choose the dialect at compile time:

uses Daf.SQLCute;                      // IQueryCompiler
uses Daf.SQLCute.Compiler;             // TAnsiSqlCompiler (default)
uses Daf.SQLCute.Compiler.Postgres;    // TPostgresCompiler
uses Daf.SQLCute.Compiler.MySql;       // TMySqlCompiler
// … etc.

var Compiler: IQueryCompiler := TPostgresCompiler.Create;  // ref-counted: it needs an owner
var R := Compiler.Compile(Q);

You can also call Q.Compile(MyCompiler) as a shorthand when you have a compiler instance.

Clone — Query Variants

Clone creates a deep copy, useful for building related queries from a shared base:

var Base := TQuery.New.From('orders').Where('year', 2024);

var Pending  := Base.Clone.Where('status', 'pending');
var Shipped  := Base.Clone.Where('status', 'shipped');

Null Safety

All Where* methods that take a Variant accept Null values. When the value is Null, the generated SQL uses IS NULL with = or no operator, and IS NOT NULL with any other, with no binding; WhereNot keeps its negation.

.Where('deleted_at', Null)      // → WHERE deleted_at IS NULL
.WhereNot('deleted_at', Null)   // → WHERE NOT (deleted_at IS NULL)

Differences from SqlKata

SQLCute follows the builder and the compilers of SqlKata. These are the places where the SQL it emits, or what it accepts, differs:

Case	SqlKata	SQLCute
Case-insensitive LIKE on Postgres	`ILIKE`	`LOWER(column) LIKE ?`, as in every dialect
Placeholders	`@p0`, `@p1`… in every dialect, and `:p0`… on Oracle	`?` in every dialect; in the native style, `$1`… on Postgres, `@p0`… on SQL Server, `:p1`… on Oracle, `?` in the others
Base compiler	quotes identifiers with `"`	`TAnsiSqlCompiler` quotes nothing; each dialect compiler quotes with its own character
`WhereIn` with an empty set	`1 = 0`	`col IS NULL AND col IS NOT NULL`, which names the column
`Where(col, 'in', values)`	rejected	binds the values, like `WhereIn`
`WhereNot` with `Null` or a logical value	drops the negation	keeps it: `NOT (col IS NULL)`
A negated comparison with a logical value	`col != true`	`NOT (col = true)`
A logical value on Firebird	`col = 1`, which a `BOOLEAN` column rejects	`col = true`, which a `SMALLINT` flag column rejects
Escape character of the LIKE family	any character	a single quote or a `?` raise `EArgumentException`
`\?` in a raw expression	stays `\?` in the SQL	becomes `??`, FireDAC’s literal `?`, outside a string, and `?` inside one and in the native style
A SQL Server page on a `Distinct` query with no `OrderBy`	`ORDER BY (SELECT 0)`, which SQL Server rejects under `DISTINCT`	`ORDER BY 1`

SELECT & FROM

🌍 Language: English | Español

← Back to Guide

From

Sets the source table. All queries start here.

TQuery.New.From('orders')
// → SELECT * FROM orders

With alias:

TQuery.New.From('orders', 'o')
// → SELECT * FROM orders o

Select — Column List

TQuery.New.From('users')
  .Select(['id', 'name', 'email'])
// → SELECT id, name, email FROM users

Single column:

.Select('name')
// → SELECT name FROM users

With alias:

.SelectAs('full_name', 'name')
// → SELECT full_name AS name FROM users

Select * (default)

When no Select call is made, SELECT * is emitted:

TQuery.New.From('products')
// → SELECT * FROM products

Distinct

TQuery.New.From('orders')
  .Select('customer_id')
  .Distinct
// → SELECT DISTINCT customer_id FROM orders

SelectRaw

Emit a raw SQL expression in the SELECT list (no quoting or escaping applied):

TQuery.New.From('sales')
  .SelectRaw('YEAR(created_at) AS year')
  .SelectRaw('COUNT(*) AS total')
// → SELECT YEAR(created_at) AS year, COUNT(*) AS total FROM sales

Aggregate Functions

Method	Default alias	SQL generated
`SelectCount(col, alias)`	`'count'`	`COUNT(col) AS alias`
`SelectSum(col, alias)`	`'sum'`	`SUM(col) AS alias`
`SelectAvg(col, alias)`	`'avg'`	`AVG(col) AS alias`
`SelectMin(col, alias)`	`'min'`	`MIN(col) AS alias`
`SelectMax(col, alias)`	`'max'`	`MAX(col) AS alias`

TQuery.New.From('orders')
  .SelectCount('*', 'total_orders')
  .SelectSum('amount', 'revenue')
  .SelectAvg('amount', 'avg_order')
  .GroupBy('status')
// → SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue, AVG(amount) AS avg_order
//   FROM orders GROUP BY status

Subquery as Column

var Sub := TQuery.New.From('order_items')
  .SelectSum('qty')
  .Where('order_id', '=', TQuery.New.From('...'));  // conceptual — use as scalar subquery

TQuery.New.From('orders')
  .Select(Sub, 'item_count')
// → SELECT (SELECT SUM(qty) FROM order_items) AS item_count FROM orders

ORDER BY

.OrderBy('name')          // ASC
.OrderByDesc('created_at') // DESC
.OrderByRaw('FIELD(status, ''pending'', ''shipped'', ''closed'')')

Multiple ORDER BY: call OrderBy/OrderByDesc multiple times:

TQuery.New.From('products')
  .OrderBy('category')
  .OrderByDesc('price')
// → SELECT * FROM products ORDER BY category ASC, price DESC

Where NULLs land

An OrderBy over a nullable column says nothing about the NULLs, so each database applies its own rule — Postgres and Oracle sort them last ascending, MySQL, SQL Server, SQLite and Firebird sort them first. Say it out loud when it matters, with NullsFirst / NullsLast:

TQuery.New.From('people').OrderBy('age').NullsLast
// → SELECT * FROM people ORDER BY age ASC NULLS LAST

TQuery.New.From('people').OrderByDesc('age').NullsFirst
// → SELECT * FROM people ORDER BY age DESC NULLS FIRST

The modifier qualifies the ORDER BY column added last, so each column keeps its own placement:

TQuery.New.From('people')
  .OrderBy('age').NullsLast
  .OrderBy('name')
// → SELECT * FROM people ORDER BY age ASC NULLS LAST, name ASC

The same thing as one call, when passing the option reads better than chaining:

TQuery.New.From('people').OrderBy('age', TNullsOrder.Last)      // Daf.SQLCute.Clauses
TQuery.New.From('people').OrderByDesc('age', TNullsOrder.First)

NullsFirst / NullsLast raise if there is no ordered column to qualify — a raw OrderByRaw expression does not count, since it is emitted verbatim.

Not every dialect has the NULLS FIRST/LAST syntax; the ones that don’t emulate it, so a qualified sort means the same thing everywhere. See Dialect Compilers for the per-dialect detail and for each database’s own default.

LIMIT and OFFSET

TQuery.New.From('logs').Limit(100)
// → SELECT * FROM logs LIMIT 100

TQuery.New.From('logs').Limit(20).Offset(40)
// → SELECT * FROM logs LIMIT 20 OFFSET 40

Take and Skip are aliases for Limit and Offset:

.Take(20).Skip(40)   // same as Limit(20).Offset(40)

For page-based pagination see Set Operations.

WHERE

🌍 Language: English | Español

← Back to Guide

Basic Equality

.Where('status', 'active')
// → WHERE status = ?   Bindings: ['active']

With an explicit operator:

.Where('price', '>', 100)
.Where('stock', '<=', 0)
// → WHERE price > ? AND stock <= ?   Bindings: [100, 0]

The operator is one of =, <>, !=, <, <=, >, >=, like, not like, in and not in, in any case; any other raises EArgumentException.

OR connector:

.Where('status', 'pending')
.OrWhere('status', 'processing')
// → WHERE status = ? OR status = ?

WhereNot

.WhereNot('role', 'guest')
// → WHERE NOT (role = ?)

.OrWhereNot('status', 'closed')
// → OR NOT (status = ?)

NULL Checks

.WhereNull('deleted_at')       // → WHERE deleted_at IS NULL
.WhereNotNull('email')         // → WHERE email IS NOT NULL
.OrWhereNull('archived_at')    // → OR archived_at IS NULL

Passing Null directly to Where, OrWhere, WhereNot or OrWhereNot compares with NULL as SqlKata does: with = or no operator it emits IS NULL, and with any other operator IS NOT NULL, with no binding. WhereNot keeps its negation, where SqlKata drops it:

.Where('deleted_at', Null)         // → WHERE deleted_at IS NULL
.Where('deleted_at', '<>', Null)   // → WHERE deleted_at IS NOT NULL
.WhereNot('deleted_at', Null)      // → WHERE NOT (deleted_at IS NULL)

Boolean Shorthand

.WhereTrue('active')    // → WHERE active = true
.WhereFalse('blocked')  // → WHERE blocked = false

The comparison is with the dialect’s literal, with no binding: true and false in ANSI, Postgres, MySQL, Oracle and Firebird, 1 and 0 in SQLite, and cast(1 as bit) and cast(0 as bit) in SQL Server — see Dialect compilers. Where, OrWhere, WhereNot and OrWhereNot with a logical value emit the same: with = or no operator, the comparison with the literal; with any other operator, its negation, which adds to that of WhereNot:

.Where('active', True)          // → WHERE active = true
.Where('active', '<>', False)   // → WHERE NOT (active = false)

BETWEEN

.WhereBetween('price', 10, 100)
// → WHERE price BETWEEN ? AND ?   Bindings: [10, 100]

.WhereNotBetween('age', 13, 17)
.OrWhereBetween('score', 90, 100)

WHERE IN

.WhereIn('status', ['pending', 'processing', 'shipped'])
// → WHERE status IN (?, ?, ?)

.WhereNotIn('id', [1, 2, 3])
.OrWhereIn('category_id', [10, 20])
.OrWhereNotIn('tag', ['spam', 'bot'])

The empty value set

A computed set can come out empty, and IN over nothing has an answer: x IN (a, b) is x = a OR x = b, so an empty list is the empty disjunction — false without consulting the column, and true for NOT IN.

The four verbs resolve that case where the condition is added, so no IN () is ever emitted:

.WhereIn('price', [])
// → WHERE (price IS NULL AND price IS NOT NULL)      matches no row

.WhereNotIn('price', [])
// → WHERE NOT (price IS NULL AND price IS NOT NULL)  matches every row

IS NULL and IS NOT NULL are the only predicates in the model that never yield unknown: exactly one of them holds for any value. That is what makes the group false for every row including one whose column is NULL, where price <> price or price = NULL would yield unknown and quietly drop the row. It also names the column, so a logged statement points back at the call that produced it — which 1 = 0 would not.

Both back-ends read the same clauses, so the SQL lane and the in-memory lane answer identically.

WHERE EXISTS (subquery)

var Sub := TQuery.New.From('order_items').WhereColumns('order_id', '=', 'orders.id');

TQuery.New.From('orders')
  .WhereExists(Sub)
// → WHERE EXISTS (SELECT * FROM order_items WHERE order_id = orders.id)

WHERE IN (subquery)

var ActiveUsers := TQuery.New.From('users').Select('id').Where('active', True);

TQuery.New.From('orders')
  .WhereInQuery('customer_id', ActiveUsers)
// → WHERE customer_id IN (SELECT id FROM users WHERE active = true)

WhereRaw

Append a raw SQL fragment (AND connector). Use with caution — no escaping applied:

.WhereRaw('created_at > NOW() - INTERVAL ''7 days''')

Grouped WHERE (Callback)

Wrap conditions in parentheses using a callback:

TQuery.New.From('products')
  .Where('active', True)
  .Where(function(Q: IQuery): IQuery
    begin
      Result := Q.Where('stock', '>', 0)
                 .OrWhere('backorder_allowed', True);
    end)
// → WHERE active = true AND (stock > ? OR backorder_allowed = true)

OR-connected group:

.OrWhere(function(Q: IQuery): IQuery
  begin
    Result := Q.Where('role', 'admin').OrWhere('role', 'moderator');
  end)
// → OR (role = ? OR role = ?)

Negated Grouped WHERE (Callback)

WhereNot and OrWhereNot also accept a callback. The group is built exactly as with Where / OrWhere, then wrapped in NOT (…):

TQuery.New.From('orders')
  .Where('active', True)
  .WhereNot(function(Q: IQuery): IQuery
    begin
      Result := Q.Where('status', 'cancelled')
                 .Where('refunded', True);
    end)
// → WHERE active = true AND NOT (status = ? AND refunded = true)

OR-connected group:

.OrWhereNot(function(Q: IQuery): IQuery
  begin
    Result := Q.Where('role', 'guest').OrWhere('banned', True);
  end)
// → OR NOT (role = ? OR banned = true)

⚠️ The negation applies to the group as a whole, not to each condition. NOT (status = ? AND refunded = true) matches whenever either inner condition fails — a cancelled order that was never refunded still matches. If you meant “neither of them”, negate each condition instead: .WhereNot('status', 'cancelled').WhereNot('refunded', True).

The negation applies to its own subtree only: sibling conditions and the enclosing group are untouched.

.WhereNot(function(Q: IQuery): IQuery
  begin
    Result := Q.Where('a', 1);
  end)
.Where('b', 2)
// → WHERE NOT (a = ?) AND b = ?     — b is not negated

To negate a single condition, use the leaf form. It is the idiomatic way and produces the same SQL as a one-element group:

.WhereNot('role', 'guest')          // → WHERE NOT (role = ?)   ← idiomatic

A negated group whose callback adds no condition is omitted from the query, just like an empty unnegated group — NOT () is never emitted:

.Where('a', 1)
.WhereNot(function(Q: IQuery): IQuery begin Result := Q end)
// → WHERE a = ?

Under SQL three-valued logic, a negated group containing a comparison against NULL evaluates to unknown, so the row is excluded — the same rule NOT already follows for a single condition. The in-memory back-end applies it identically.

Precedence, and the tree behind it

AND binds tighter than OR. A flat chain of conditions is not read left to right: consecutive AND runs are grouped first, and those groups are then OR-ed.

Query.From('users')
  .Where('active', '=', True)
  .Where('age', '>', 18)
  .OrWhere('role', '=', 'admin')
// -> WHERE (active = true AND age > ?) OR role = ?

The parentheses in that SQL are emitted, not assumed. Each condition carries the connector that joins it with the previous one, and Daf.SQLCute.WhereTree normalizes that flat list into a precedence-correct boolean tree before anything is compiled:

uses Daf.SQLCute.WhereTree;

var Tree := TWhereTree.Build(Query.Clauses);
try
  // Tree.Kind is TWhereNodeKind.Disjunction for the query above
finally
  Tree.Free;
end;

Member	Answers
`TWhereTree.Build(Clauses)`	The tree of the WHERE-relevant clauses. `nil` when there are none. The caller owns it and frees it
`TWhereTree.BuildHaving(Clauses)`	The same normalization over `THavingClause`. HAVING has no nesting and no `NOT`, so the tree is a flat AND/OR grouping
`TWhereNode.Kind`	`TWhereNodeKind.Leaf`, `.Neg`, `.Conjunction` or `.Disjunction` — the unit compiles with scoped enums
`TWhereNode.Clause`	The condition, on a `Leaf`. Not owned: it points back into the query’s AST
`TWhereNode.Children`	The operands of a conjunction or disjunction, or the single child of a `Neg`. Owned, and freed recursively

A grouped callback recurses as a nested node, and a negated one wraps its node in a Neg.

Both back-ends consume the same tree, and that is the point. The ANSI compiler walks it and writes the parentheses out, so precedence stops depending on how a particular database parses; the in-memory compiler builds its three-valued predicate from it. Two engines that read one tree cannot disagree about precedence — see SQLCute.InMemory.

The walk is depth-first, left to right, which preserves the original order of the conditions. That is what lets a consumer emit positional bindings while walking and get the same order the flat list produced.

WhereColumns

Column-to-column comparison (no binding — values are emitted verbatim):

.WhereColumns('start_date', '<', 'end_date')
// → WHERE start_date < end_date

.OrWhereColumns('price', '=', 'list_price')

Its operator is one of =, <>, !=, <, <=, >, >=, like and not like; in and not in raise EArgumentException.

⚠️ Only pass developer-controlled column names to WhereColumns. Never pass user input.

When — Conditional Chaining

Apply conditions only when a runtime flag is true:

var IncludeInactive := False;

TQuery.New.From('users')
  .When(not IncludeInactive, function(Q: IQuery): IQuery
    begin
      Result := Q.Where('active', True);
    end)
// → WHERE active = true   (only when IncludeInactive is False)

With an else branch:

.When(IsAdmin,
  function(Q: IQuery): IQuery begin Result := Q; end,        // no extra filter
  function(Q: IQuery): IQuery begin Result := Q.Where('tenant_id', TenantId); end)

String / LIKE Conditions

See String Operations for WhereLike, WhereStarts, WhereEnds, WhereContains.

Date Conditions

See Date Operations for WhereDate, WhereTime, WhereDatePart.

JOINs

🌍 Language: English | Español

← Back to Guide

Basic Joins

All join methods follow the same signature: JoinType(table, col1, col2, op). The default operator is =, and it is one of =, <>, !=, <, <=, >, >=, like and not like; any other raises EArgumentException.

INNER JOIN

TQuery.New.From('orders')
  .Join('customers', 'orders.customer_id', 'customers.id')
// → SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id

LEFT JOIN

.LeftJoin('addresses', 'users.id', 'addresses.user_id')
// → LEFT JOIN addresses ON users.id = addresses.user_id

RIGHT JOIN

.RightJoin('departments', 'employees.dept_id', 'departments.id')
// → RIGHT JOIN departments ON employees.dept_id = departments.id

CROSS JOIN

.CrossJoin('sizes')
// → CROSS JOIN sizes

FULL OUTER JOIN

.FullOuterJoin('b', 'a.id', 'b.a_id')
// → FULL OUTER JOIN b ON a.id = b.a_id

Join with Raw Condition String

Pass a raw SQL ON clause when the condition cannot be expressed as column=column:

.Join('prices', 'prices.product_id = products.id AND prices.active = 1')
// → INNER JOIN prices ON prices.product_id = products.id AND prices.active = 1

Join with Callback (Complex ON)

Use a callback for multiple conditions joined with AND/OR:

TQuery.New.From('orders')
  .Join('order_items', function(Q: IQuery): IQuery
    begin
      Result := Q
        .WhereColumns('order_items.order_id', '=', 'orders.id')
        .OrWhereColumns('order_items.alt_order_id', '=', 'orders.id');
    end)
// → INNER JOIN order_items ON (order_items.order_id = orders.id
//                               OR order_items.alt_order_id = orders.id)

In the callback, a column on the other side goes through WhereColumns; Where binds its value as a parameter.

LeftJoin, RightJoin, and FullOuterJoin all support the same callback overload.

Subquery Join

var Sub := TQuery.New.From('order_items')
  .Select('order_id')
  .SelectSum('qty', 'total_qty')
  .GroupBy('order_id');

TQuery.New.From('orders')
  .Join(Sub, 'oi', function(Q: IQuery): IQuery
    begin
      Result := Q.WhereColumns('oi.order_id', '=', 'orders.id');
    end)
// → INNER JOIN (SELECT order_id, SUM(qty) AS total_qty
//               FROM order_items GROUP BY order_id) AS oi
//   ON (oi.order_id = orders.id)

Multiple Joins

Chain as many joins as needed — they are emitted in declaration order:

TQuery.New.From('invoices')
  .Join('customers', 'invoices.customer_id', 'customers.id')
  .LeftJoin('discounts', 'invoices.discount_id', 'discounts.id')
  .Select(['invoices.id', 'customers.name', 'discounts.pct'])

GROUP BY & Aggregates

GROUP BY / HAVING

🌍 Language: English | Español

← Back to Guide

GROUP BY

TQuery.New.From('sales')
  .SelectCount('*', 'qty')
  .GroupBy('product_id')
// → SELECT COUNT(*) AS qty FROM sales GROUP BY product_id

Multiple columns — call GroupBy multiple times or pass an array:

.GroupBy(['year', 'month', 'category'])
// → GROUP BY year, month, category

Raw expression:

.GroupByRaw('YEAR(created_at), MONTH(created_at)')
// → GROUP BY YEAR(created_at), MONTH(created_at)

HAVING

A condition on an aggregate goes through HavingRaw, which every engine accepts:

TQuery.New.From('orders')
  .GroupBy('customer_id')
  .SelectCount('*', 'order_count')
  .SelectSum('total', 'revenue')
  .HavingRaw('count(*) > 5')
  .HavingRaw('sum(total) >= 1000')
// → SELECT COUNT(*) AS order_count, SUM(total) AS revenue
//   FROM orders
//   GROUP BY customer_id
//   HAVING count(*) > 5 AND sum(total) >= 1000

Having compares a column with a bound value. The column names a grouped column or the alias of an output column, and is quoted like any other column of the dialect. Its operator is one of =, <>, !=, <, <=, >, >=, like and not like; in and not in raise EArgumentException, and an IN on an aggregate goes through HavingRaw. MySQL, SQLite and Oracle 23 accept the alias of an output column in HAVING; Postgres, SQL Server and Firebird reject it.

.Having('order_count', '>', 5)
// Postgres → HAVING "order_count" > ?

OR connector:

.Having('order_count', '>', 10)
.OrHaving('revenue', '>=', 1000)
// → HAVING order_count > ? OR revenue >= ?

Raw HAVING expression:

.HavingRaw('AVG(response_time) < 200')

Having with Null compares the same way as Where: IS NULL for = and IS NOT NULL for any other operator, with no binding.

Combining with WHERE

WHERE filters rows before grouping; HAVING filters groups after:

TQuery.New.From('logs')
  .Where('level', 'error')          // applied before GROUP BY
  .GroupBy('service')
  .SelectCount('*', 'errors')
  .HavingRaw('count(*) > 50')       // applied after GROUP BY
// → SELECT COUNT(*) AS errors FROM logs
//   WHERE level = ?
//   GROUP BY service
//   HAVING count(*) > 50

Set Operations

Set Operations & Pagination

🌍 Language: English | Español

← Back to Guide

UNION

var Q1 := TQuery.New.From('active_users').Select(['id', 'name']);
var Q2 := TQuery.New.From('archived_users').Select(['id', 'name']);

Q1.Union(Q2)
// → SELECT id, name FROM active_users
//   UNION
//   SELECT id, name FROM archived_users

UNION ALL (keeps duplicates):

Q1.UnionAll(Q2)
// → … UNION ALL …

INTERSECT / EXCEPT

Q1.Intersect(Q2)
// → … INTERSECT …

Q1.&Except(Q2)    // & prefix avoids conflict with Delphi keyword
// → … EXCEPT …

Q1.IntersectAll(Q2)
Q1.ExceptAll(Q2)

CombineRaw

Append a raw set-operation string:

Q1.CombineRaw('MINUS SELECT id, name FROM deleted_users')
// → … MINUS SELECT id, name FROM deleted_users

Pagination

ForPage (recommended)

1-based page number, configurable page size:

TQuery.New.From('products')
  .Where('active', True)
  .OrderBy('name')
  .ForPage(3, 25)      // page 3, 25 items per page
// → … LIMIT 25 OFFSET 50

Default page size is 15:

.ForPage(2)   // → LIMIT 15 OFFSET 15

Manual LIMIT / OFFSET

.Limit(20).Offset(40)
// → LIMIT 20 OFFSET 40

// Aliases
.Take(20).Skip(40)

Typical pagination loop

const PageSize = 50;
var Page := 1;

repeat
  var R := Compiler.Compile(
    TQuery.New.From('orders').OrderBy('id').ForPage(Page, PageSize));
  // execute R, process rows…
  Inc(Page);
until RowCount < PageSize;

DML

DML — INSERT / UPDATE / DELETE

🌍 Language: English | Español

← Back to Guide

INSERT — Single Row

Pass parallel arrays of column names and values:

TQuery.New.From('users')
  .AsInsert(
    ['name',    'email',             'role'],
    ['Alice',   'alice@example.com', 'admin'])
// → INSERT INTO users (name, email, role) VALUES (?, ?, ?)
//   Bindings: ['Alice', 'alice@example.com', 'admin']

INSERT — Multiple Rows

TQuery.New.From('tags')
  .AsInsertRows(
    ['name', 'color'],
    [['Urgent', 'red'],
     ['Normal', 'green'],
     ['Low',    'gray']])
// → INSERT INTO tags (name, color) VALUES (?, ?), (?, ?), (?, ?)
//   Bindings: ['Urgent', 'red', 'Normal', 'green', 'Low', 'gray']

INSERT … SELECT

Copy rows from one table to another using a subquery:

var Sub := TQuery.New.From('temp_users')
  .Select(['name', 'email'])
  .Where('imported', True);

TQuery.New.From('users')
  .AsInsertFrom(['name', 'email'], Sub)
// → INSERT INTO users (name, email)
//   SELECT name, email FROM temp_users WHERE imported = true

UPDATE

TQuery.New.From('products')
  .Where('id', 42)
  .AsUpdate(
    ['price', 'stock'],
    [29.99,   100])
// → UPDATE products SET price = ?, stock = ? WHERE id = ?
//   Bindings: [29.99, 100, 42]

⚠️ Without a Where clause, ALL rows are updated. Add a WHERE condition to scope the update.

DELETE

TQuery.New.From('sessions')
  .Where('expired', True)
  .AsDelete
// → DELETE FROM sessions WHERE expired = true

Soft-delete pattern using UPDATE:

TQuery.New.From('users')
  .Where('id', UserId)
  .AsUpdate(['deleted_at'], [Now])
// → UPDATE users SET deleted_at = ? WHERE id = ?

Combining DML with Subqueries

// Delete orders older than 1 year whose items are all shipped
TQuery.New.From('orders')
  .Where('created_at', '<', EncodeDate(Year - 1, Month, Day))
  .WhereNotExists(
    TQuery.New.From('order_items')
      .WhereColumns('order_id', '=', 'orders.id')
      .WhereNot('status', 'shipped'))
  .AsDelete

Subqueries

Subqueries & CTEs

🌍 Language: English | Español

← Back to Guide

Derived Table (FROM subquery)

Use an IQuery as the FROM source with an alias:

var Inner := TQuery.New.From('order_items')
  .Select('order_id')
  .SelectSum('qty', 'total_qty')
  .GroupBy('order_id');

TQuery.New
  .From(Inner, 'oi')
  .Select(['oi.order_id', 'oi.total_qty'])
  .Where('oi.total_qty', '>', 10)
// → SELECT oi.order_id, oi.total_qty
//   FROM (SELECT order_id, SUM(qty) AS total_qty
//         FROM order_items GROUP BY order_id) oi
//   WHERE oi.total_qty > ?

FROM Raw

Use a raw SQL expression as the FROM source (e.g. table-valued functions):

TQuery.New
  .FromRaw('generate_series(1, 100) AS s(n)', [])
  .Select('n')
// → SELECT n FROM generate_series(1, 100) AS s(n)

With bindings:

.FromRaw('fn_get_events(?, ?) AS e', [StartDate, EndDate])

Its ? count with the placeholders of the rest of the query, and its bindings take that same place: in the native style of Postgres, $1 and $2 here, and the next one in the query $3.

Subquery in WHERE IN

var RecentOrders := TQuery.New.From('orders')
  .Select('customer_id')
  .Where('created_at', '>', LastWeek);

TQuery.New.From('customers')
  .WhereInQuery('id', RecentOrders)
// → SELECT * FROM customers
//   WHERE id IN (SELECT customer_id FROM orders WHERE created_at > ?)

Variants: WhereNotInQuery, OrWhereInQuery, OrWhereNotInQuery.

Subquery in WHERE EXISTS

TQuery.New.From('orders')
  .WhereExists(
    TQuery.New.From('payments')
      .WhereColumns('order_id', '=', 'orders.id')
      .Where('status', 'complete'))
// → WHERE EXISTS (SELECT * FROM payments
//                WHERE order_id = orders.id AND status = ?)

The column of the outer query goes through WhereColumns; Where binds its value as a parameter.

Scalar Subquery in SELECT

var ItemCount := TQuery.New.From('order_items')
  .SelectCount('*')
  .WhereColumns('order_id', '=', 'orders.id');

TQuery.New.From('orders')
  .Select(ItemCount, 'item_count')
// → SELECT (SELECT COUNT(*) AS count FROM order_items WHERE order_id = orders.id)
//          AS item_count FROM orders

WITH (CTE)

var RecentSales := TQuery.New.From('sales')
  .Where('year', 2024)
  .Select(['product_id', 'amount']);

TQuery.New
  .&With('recent', RecentSales)
  .From('recent')
  .SelectSum('amount', 'total')
// → WITH recent AS (SELECT product_id, amount FROM sales WHERE year = ?)
//   SELECT SUM(amount) AS total FROM recent

RECURSIVE CTE

var Anchor := TQuery.New.From('categories')
  .Where('parent_id', Null);
var Recursive := TQuery.New.From('categories')
  .Join('tree', 'categories.parent_id', 'tree.id');

TQuery.New
  .WithRecursive('tree', Anchor.Union(Recursive))
  .From('tree')
// → WITH RECURSIVE tree AS (
//     SELECT * FROM categories WHERE parent_id IS NULL
//     UNION
//     SELECT categories.* FROM categories INNER JOIN tree ON categories.parent_id = tree.id
//   ) SELECT * FROM tree

WITH Raw

TQuery.New
  .WithRaw('cte_name', 'SELECT id FROM legacy_table WHERE flag = ?', [1])
  .From('cte_name')

Its ? count with the placeholders of the rest of the query, in the order of the text.

Nesting Depth

SQLCute supports arbitrary nesting — subqueries can themselves contain subqueries. Bindings are flattened into a single array in the order they appear in the compiled SQL.

String Operations

🌍 Language: English | Español

← Back to Guide

Overview

All string-match methods wrap the value in LIKE patterns. By default (CaseSensitive = False) SQLCute wraps the column in LOWER(…) and lower-cases the pattern, which travels as a binding. Pass True as CaseSensitive, the argument after the value, to emit a plain LIKE with the pattern as you wrote it.

The wildcards % and _ inside the value stay active in every method: WhereContains('code', '50%') matches any code that contains 50. To match them literally, declare an escape character.

WhereLike — Raw Pattern

Supply the full LIKE pattern yourself:

.WhereLike('name', '%John%')
// Case-insensitive (default):
// → WHERE LOWER(name) LIKE ?      binding: '%john%'

.WhereLike('code', 'ABC-%', True)
// Case-sensitive:
// → WHERE code LIKE ?             binding: 'ABC-%'

Variants: WhereNotLike, OrWhereLike, OrWhereNotLike.

WhereStarts — Prefix Match

.WhereStarts('email', 'admin')
// → WHERE LOWER(email) LIKE ?     binding: 'admin%'

Variants: WhereNotStarts, OrWhereStarts, OrWhereNotStarts.

WhereEnds — Suffix Match

.WhereEnds('filename', '.pdf')
// → WHERE LOWER(filename) LIKE ?  binding: '%.pdf'

Variants: WhereNotEnds, OrWhereEnds, OrWhereNotEnds.

WhereContains — Substring Match

.WhereContains('description', 'urgent')
// → WHERE LOWER(description) LIKE ?   binding: '%urgent%'

Variants: WhereNotContains, OrWhereContains, OrWhereNotContains.

Escape Character

Every method of the family takes an optional escape character after CaseSensitive. The character that follows it in the value is literal, and the SQL adds ESCAPE '<c>':

.WhereContains('code', '50!%', False, '!')
// → WHERE LOWER(code) LIKE ? ESCAPE '!'   binding: '%50!%%'

SQLCute escapes nothing on its own: the wildcards it adds around the value stay wildcards, and only what the escape character precedes is literal. A single quote or a ? as escape character raise EArgumentException.

With CaseSensitive = False the pattern is lower-cased and the escape character is not, so pick one with no case, such as !. The in-memory back-end reads the escape character the same way.

Combining String Filters

TQuery.New.From('products')
  .Where(function(Q: IQuery): IQuery
    begin
      Result := Q.WhereContains('name', 'coffee')
                 .OrWhereContains('tags', 'coffee');
    end)
  .WhereNotStarts('sku', 'DISC-')
// → WHERE (LOWER(name) LIKE ? OR LOWER(tags) LIKE ?)
//     AND LOWER(sku) NOT LIKE ?
//   bindings: '%coffee%', '%coffee%', 'disc-%'

Chaining the two OR terms without that group means something else: a flat list is read as OR-of-AND-runs, so WhereContains … OrWhereContains … WhereNotStarts groups as name OR (tags AND sku). Nest the alternation you mean.

Dialect Notes on Case Sensitivity

The pattern is lower-cased across all of Unicode, so WhereLike('name', 'ÑANDÚ%') binds 'ñandú%'. The column is lower-cased by the engine’s LOWER, and SQLite’s folds only ASCII letters: on SQLite, WhereStarts('name', 'ñan') does not match a stored Ñandú. The in-memory back-end folds both sides across Unicode.

Each database also has its own native LIKE behavior:

Dialect	LIKE case-sensitive?	Notes
ANSI / ANSI SQL	Yes	Standard behavior
PostgreSQL	Yes	Use `ILIKE` natively; SQLCute uses `LOWER(…)` instead
MySQL	No (by default)	`LOWER` wrapping is harmless
SQLite	No for ASCII	Non-ASCII letters are case-sensitive, and its `LOWER` folds only ASCII, unless the ICU extension is loaded
SQL Server	Depends on collation	`Latin1_General_CI_AS` → case-insensitive
Oracle	Yes	`LOWER` wrapping recommended
Firebird	Yes	`LOWER` wrapping recommended

When CaseSensitive = True, no LOWER wrapping is emitted regardless of dialect.

Date Operations

🌍 Language: English | Español

← Back to Guide

WhereDate — Exact Date Match

Filters rows where the DATE part of a datetime column equals a value, ignoring the time component. The cast expression is dialect-specific (see table below).

TQuery.New.From('orders')
  .WhereDate('created_at', '2024-06-15')
// ANSI: WHERE CAST(created_at AS DATE) = ?
// Bindings: ['2024-06-15']

OR connector:

.OrWhereDate('updated_at', '2024-06-15')

WhereTime — Time Comparison

TQuery.New.From('appointments')
  .WhereTime('start_time', '>=', '09:00:00')
// ANSI: WHERE CAST(start_time AS TIME) >= ?

OR variant: OrWhereTime. The operator is one of =, <>, !=, <, <=, >, >=, like and not like; any other raises EArgumentException when the condition is added.

WhereDatePart — Extract a Part

Filter by year, month, day, hour, or minute:

uses Daf.SQLCute.Clauses;  // TDatePart

TQuery.New.From('events')
  .WhereDatePart(TDatePart.dpYear,  'event_date', 2024)
  .WhereDatePart(TDatePart.dpMonth, 'event_date', 12)
// ANSI: WHERE EXTRACT(year FROM event_date) = ? AND EXTRACT(month FROM event_date) = ?

Available TDatePart values: dpDate, dpTime, dpYear, dpMonth, dpDay, dpHour, dpMinute.

OR variant: OrWhereDatePart.

Dialect Date-Cast Matrix

Each dialect compiler overrides the cast expression for WhereDate:

Dialect	`WhereDate('col', val)` generates
ANSI (default)	`CAST(col AS DATE) = ?`
PostgreSQL	`col::date = ?`
MySQL	`DATE(col) = ?`
SQLite	`date(col) = ?`
SQL Server	`CAST(col AS date) = ?`
Oracle	`TRUNC(col) = ?`
Firebird	`CAST(col AS DATE) = ?`

PostgreSQL example

uses Daf.SQLCute, Daf.SQLCute.Compiler.Postgres;

var Compiler: IQueryCompiler := TPostgresCompiler.Create;  // ref-counted: it needs an owner
var R := Compiler.Compile(
  TQuery.New.From('t').WhereDate('d', '2024-01-01'));
// → WHERE "d"::date = ?

MySQL example

uses Daf.SQLCute, Daf.SQLCute.Compiler.MySql;

var Compiler: IQueryCompiler := TMySqlCompiler.Create;  // ref-counted: it needs an owner
var R := Compiler.Compile(
  TQuery.New.From('t').WhereDate('d', '2024-01-01'));
// → WHERE DATE(`d`) = ?

Dialect DatePart Matrix

Dialect	EXTRACT year	EXTRACT month	EXTRACT day	EXTRACT hour
ANSI	`EXTRACT(year FROM col)`	`EXTRACT(month FROM col)`	`EXTRACT(day FROM col)`	`EXTRACT(hour FROM col)`
PostgreSQL	same as ANSI	same	same	same
MySQL	`YEAR(col)`	`MONTH(col)`	`DAY(col)`	`HOUR(col)`
SQLite	`strftime('%Y', col)`	`strftime('%m', col)`	`strftime('%d', col)`	`strftime('%H', col)`
SQL Server	`DATEPART(year, col)`	`DATEPART(month, col)`	`DATEPART(day, col)`	`DATEPART(hour, col)`
Oracle	`EXTRACT(YEAR FROM col)`	`EXTRACT(MONTH FROM col)`	`EXTRACT(DAY FROM col)`	—
Firebird	`EXTRACT(YEAR FROM col)`	`EXTRACT(MONTH FROM col)`	`EXTRACT(DAY FROM col)`	`EXTRACT(HOUR FROM col)`

Pass date values as strings ('2024-06-15') or as Delphi TDateTime values — the database driver handles the type conversion from the Variant binding.

⚠️ These predicates are SQL-only. The cast is dialect-specific, so SQLCute.InMemory rejects them with EInMemoryQueryError instead of approximating them. A query meant to run against both back-ends filters dates with a plain Where over a comparable column.

Dialect Reference

# Dialect Compilers

🌍 Language: English | Español

← Back to Guide

What a Dialect Compiler Does

The default TAnsiSqlCompiler generates generic ANSI SQL with ? placeholders and no identifier quoting. Dialect compilers extend it to handle:

Identifier quoting — wrapping table/column names to avoid keyword conflicts
Parameter placeholders — ? by default, and the engine’s own ($1, :p1, @p0) in the native style
Date casting — dialect-specific expressions for WhereDate/WhereDatePart
Pagination — LIMIT/OFFSET vs FETCH NEXT … ROWS ONLY vs ROWS m TO n
NULL-ordering emission — native NULLS FIRST/LAST, or an emulation where the dialect has no such syntax

Dialect Matrix

Every compiler writes ? by default. The placeholder column is what each one writes in the native style.

Dialect class	Quote char	Native-style placeholder	Package unit
`TAnsiSqlCompiler`	none	`?`	`Daf.SQLCute.Compiler`
`TPostgresCompiler`	`"`	`$1`, `$2`, …	`Daf.SQLCute.Compiler.Postgres`
`TMySqlCompiler`	`	`?`	`Daf.SQLCute.Compiler.MySql`
`TSQLiteCompiler`	`"`	`?`	`Daf.SQLCute.Compiler.SQLite`
`TSqlServerCompiler`	`[` … `]`	`@p0`, `@p1`, …	`Daf.SQLCute.Compiler.SqlServer`
`TOracleCompiler`	`"`, upper-cased	`:p1`, `:p2`, …	`Daf.SQLCute.Compiler.Oracle`
`TFirebirdCompiler`	none	`?`	`Daf.SQLCute.Compiler.Firebird`

A quoting dialect quotes each part of a qualified name on its own — "public"."users", [users].[id] — leaves a trailing * bare, and doubles its closing character inside a name. A column written name as alias is split: Select('users.name as n') compiles to SELECT "users"."name" AS "n" in Postgres. Oracle writes its aliases without AS: FROM "USERS" "U".

A logical comparison — WhereTrue, WhereFalse, or Where with a logical value — compares with the dialect’s literal, with no binding: true and false in ANSI, Postgres, MySQL, Oracle and Firebird, 1 and 0 in SQLite, and cast(1 as bit) and cast(0 as bit) in SQL Server. Oracle understands true from Oracle 23. In Firebird, a SMALLINT flag column rejects = true with a conversion error: compare it with a number, Where('flag', 1).

All dialect compilers ship in the Daf.SQLCute package, alongside the TAnsiSqlCompiler they extend. Requiring Daf.SQLCute is enough — there is no separate compilers package to add.

Placeholder Styles

The Placeholders property of a compiler sets what it writes for each binding, and for a \? in a raw expression:

`TPlaceholderStyle`	Each binding	`\?`
`FireDAC`, the default	`?`, in every dialect	`??` outside a string, `?` inside one
`Native`	the dialect’s own: `$1`… on Postgres, `@p0`… on SQL Server, `:p1`… on Oracle, `?` in the others	`?`

FireDAC executes the default style as it is, on any engine: it binds ? and translates it into the engine’s own placeholder, reads ?? as a literal ?, and looks for no parameter inside a string.

A string is what goes between single quotes, and a '' inside it does not close it. Other string forms, such as $$…$$ and E'…' on Postgres, are not followed: a \? inside one comes out as ??. The native style writes ? there.

For the native style, set the property before the instance compiles. The compiler is reference counted, so hold it in an IQueryCompiler:

uses
  Daf.SQLCute,
  Daf.SQLCute.Compiler,
  Daf.SQLCute.Compiler.Postgres;

var Postgres := TPostgresCompiler.Create;
Postgres.Placeholders := TPlaceholderStyle.Native;
var Compiler: IQueryCompiler := Postgres;
var R := Compiler.Compile(TQuery.New.From('users').Where('status', 'open'));
// R.SQL → 'SELECT * FROM "users" WHERE "status" = $1'

TPlaceholderStyle lives in Daf.SQLCute.Compiler. Changing Placeholders while another thread compiles with the same instance is not supported.

Placeholders in Raw Expressions

Every ? in a raw expression — WhereRaw, FromRaw, WithRaw and the other Raw forms — is a placeholder, counted with those the query generates, and its binding takes that same place; the native style numbers it with them. Write \? for a literal question mark, such as the ? operator of Postgres on jsonb:

TQuery.New.From('docs').WhereRaw('data \? ''tag''').Where('id', 1)
// Postgres               → SELECT * FROM "docs" WHERE data ?? 'tag' AND "id" = ?
// Postgres, native style → SELECT * FROM "docs" WHERE data ? 'tag' AND "id" = $1

Using a Dialect Compiler

uses
  Daf.SQLCute,
  Daf.SQLCute.Compiler.Postgres;

var Q := TQuery.New
  .From('users')
  .Where('status', 'open')
  .OrderBy('name');

var Compiler: IQueryCompiler := TPostgresCompiler.Create;
var R := Compiler.Compile(Q);
// R.SQL      → 'SELECT * FROM "users" WHERE "status" = ? ORDER BY "name"'
// R.Bindings → ['open']

Or use the shorthand Q.Compile(Compiler):

var R := Q.Compile(TPostgresCompiler.Create);

Dependency Injection

A compiler instance keeps nothing between compilations: one instance serves successive, nested and concurrent compilations. Register it as a singleton and inject IQueryCompiler wherever needed:

// Composition root
Services.AddSingleton<IQueryCompiler, TPostgresCompiler>;

// In a repository
type
  TOrderRepository = class
  private
    FCompiler: IQueryCompiler;
  public
    constructor Create(Compiler: IQueryCompiler);
    function FindByStatus(const Status: string): TSQLResult;
  end;

function TOrderRepository.FindByStatus(const Status: string): TSQLResult;
begin
  Result := FCompiler.Compile(
    TQuery.New.From('orders').Where('status', Status));
end;

A compiler registered by its type compiles in the default style. For the native style, register an instance set up as in Placeholder Styles: Services.AddSingleton<IQueryCompiler>(Compiler).

Implementing a Custom Compiler

Extend TAnsiSqlCompiler and override only what differs:

uses System.SysUtils, Daf.SQLCute, Daf.SQLCute.Compiler;

type
  TMyDbCompiler = class(TAnsiSqlCompiler)
  protected
    function WrapIdentifier(const Name: string): string; override;
    function FormatPlaceholder(const Index: Integer): string; override;
  end;

function TMyDbCompiler.WrapIdentifier(const Name: string): string;
begin
  Result := '[' + StringReplace(Name, ']', ']]', [rfReplaceAll]) + ']';
end;

function TMyDbCompiler.FormatPlaceholder(const Index: Integer): string;
begin
  Result := ':' + IntToStr(Index + 1);
end;

WrapIdentifier quotes a single name. WrapColumn and WrapTable split off an alias and split a qualified name at each dot, and call it for each part; AliasKeyword is what joins a name with its alias.

Every method that emits SQL — CompileSelect to CompileDelete, CompileWhereList, CompileDateWhere, CompileOrderByColumn — receives the compilation, a TSqlCompilation, as its first parameter, adds its bindings to it with AddBinding, and writes each placeholder as ?. In the native style, a final pass replaces the ? at each position with what FormatPlaceholder returns for it, counted from 0.

Pagination per Dialect

SQLCute emits LIMIT … OFFSET … by default. The SQL Server, Oracle and Firebird compilers override pagination to use their native syntax:

Dialect	Pagination SQL
ANSI / Postgres / MySQL / SQLite	`LIMIT n OFFSET m`
SQL Server	`ORDER BY … OFFSET m ROWS FETCH NEXT n ROWS ONLY`
Oracle	`OFFSET m ROWS FETCH NEXT n ROWS ONLY`
Firebird	`ROWS m+1 TO m+n`

SQL Server requires an ORDER BY with OFFSET/FETCH. When the query has none, TSqlServerCompiler adds ORDER BY (SELECT 0) —ORDER BY 1 on a Distinct query—, so the page compiles; which rows it holds is only stable with an .OrderBy(…) of your own.

NULL Ordering per Dialect

NullsFirst / NullsLast mean the same thing on every dialect. Where the syntax exists it is emitted; where it doesn’t, the compiler emulates it with a leading sort key. Neither form adds a binding.

Dialect	Qualified `NullsLast` compiles to	Its own default for an unqualified `ORDER BY col`
ANSI / Postgres	`col ASC NULLS LAST` (native)	NULLs last ascending, first descending (NULL sorts as the largest value)
Oracle	`"COL" ASC NULLS LAST` (native)	NULLs last ascending, first descending
SQLite	`"col" ASC NULLS LAST` (native, needs SQLite ≥ 3.30)	NULLs first ascending, last descending (NULL is the smallest value)
Firebird	`col ASC NULLS LAST` (native)	NULLs first ascending, last descending
MySQL	(`col` IS NULL) ASC, `col` ASC (emulated)	NULLs first ascending, last descending
SQL Server	`CASE WHEN [col] IS NULL THEN 1 ELSE 0 END ASC, [col] ASC` (emulated)	NULLs first ascending, last descending

col IS NULL is 1 for NULL and 0 otherwise, so the emulated key sorted ASC holds NULLs back and sorted DESC brings them forward. SQL Server needs the CASE because T-SQL allows no boolean expression in ORDER BY.

Notes:

The defaults are the databases’, not SQLCute’s. An unqualified OrderBy emits no NULL clause on any dialect, so the column above is what your database does with it. Qualify the sort to get one answer everywhere.
SQLite < 3.30 rejects the native syntax. Since emission lives behind the overridable CompileOrderByColumn, a project pinned to an older engine can subclass TSQLiteCompiler and emulate as MySQL does.
Firebird does not use an index on a column ordered with a non-default NULLS placement.

Introduction

Table of Contents

The Compiler Pattern

Clone — Query Variants

Null Safety

Differences from SqlKata

See Also

SELECT & FROM

SELECT & FROM

From

Select — Column List

Select * (default)

Distinct

SelectRaw

Aggregate Functions

Subquery as Column

ORDER BY

Where NULLs land

LIMIT and OFFSET

WHERE

WHERE

Basic Equality

WhereNot

NULL Checks

Boolean Shorthand

BETWEEN

WHERE IN

The empty value set

WHERE EXISTS (subquery)

WHERE IN (subquery)

WhereRaw

Grouped WHERE (Callback)

Negated Grouped WHERE (Callback)

Precedence, and the tree behind it

WhereColumns

When — Conditional Chaining

String / LIKE Conditions

Date Conditions

JOINs

JOINs

Basic Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

FULL OUTER JOIN

Join with Raw Condition String

Join with Callback (Complex ON)

Subquery Join

Multiple Joins

GROUP BY & Aggregates

GROUP BY / HAVING

GROUP BY

HAVING

Combining with WHERE

Set Operations

Set Operations & Pagination

UNION

INTERSECT / EXCEPT

CombineRaw

Pagination

ForPage (recommended)

Manual LIMIT / OFFSET

Typical pagination loop

DML

DML — INSERT / UPDATE / DELETE

INSERT — Single Row

INSERT — Multiple Rows

INSERT … SELECT

UPDATE

DELETE

Combining DML with Subqueries

Subqueries

Subqueries & CTEs

Derived Table (FROM subquery)

FROM Raw

Subquery in WHERE IN

Subquery in WHERE EXISTS

Scalar Subquery in SELECT

WITH (CTE)