SQL reference

What is AI-SQL?

AI-SQL extends standard SQL with operators that call a language model during query execution. You can filter rows, join tables, and match documents using natural language conditions instead of exact column comparisons.

For example, to find reviews that discuss the ending of a movie:

SELECT r.id
FROM reviews r
WHERE AI.IF(PROMPT('Does this review discuss the ending of the movie?\n\n{0}', r.body))

The model reads each review and returns TRUE or FALSE. Quail keeps the rows where the answer is TRUE.

Major database vendors define their own AI-SQL syntax, including Snowflake Cortex AISQL, BigQuery AI functions, and Databricks AI Functions. Quail supports two of these dialects:

session.sql(text, dialect="snowflake")  # AI_FILTER syntax
session.sql(text, dialect="bq")         # AI.IF syntax

Both compile to the same plan. The rest of this page uses the BigQuery dialect.

PROMPT

PROMPT writes the question that the model answers for each row. You write a template with {0}, {1}, etc. as placeholders for column values, and Quail substitutes each row's data in.

For a filter, the prompt references one column:

PROMPT('Does this review discuss the ending of the movie?\n\n{0}', r.body)

Here {0} is replaced with each review's body text.

For a join, the prompt references one column from each table:

PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
       r.body, a.aspect)

Here {0} is the review and {1} is the aspect, and the model answers for every pair.

AI operators

AI.IF

AI.IF takes a PROMPT and evaluates it on each row or each pair of rows. The model returns TRUE or FALSE.

Filtering

Use AI.IF in a WHERE clause to filter rows:

SELECT r.id
FROM reviews r
WHERE AI.IF(PROMPT('Does this review mention a positive aspect?\n\n{0}', r.body))
  AND AI.IF(PROMPT('Does this review discuss the ending?\n\n{0}', r.body))

You can chain multiple filters with AND. Quail requires all filters on one table to read the same document column, so it can compute the document's model state once and reuse it across filters.

Joining

Use AI.IF to join two tables with a natural language condition. The model evaluates the prompt on each pair of rows.

SELECT r.id, a.aspect
FROM reviews r, aspects a
WHERE AI.IF(PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
                   r.body, a.aspect))

You can also add relational predicates on ON to restrict which pairs the model sees. You can add more than one.

SELECT c.id, e.id
FROM claims c
JOIN evidence e
  ON c.wiki_url = e.id
 AND AI.IF(PROMPT('Does the passage support the claim?\n\nClaim: {0}\nPassage: {1}',
                  c.claim, e.text))

Here the model only evaluates pairs where c.wiki_url = e.id, instead of the full cross product.

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS are semi-join operators. For each row in the outer table, the subquery runs against the inner table. EXISTS keeps the outer row if the subquery returns at least one match. NOT EXISTS keeps it if the subquery returns no matches.

This EXISTS query finds reviews that discuss at least one aspect:

SELECT r.id
FROM reviews r
WHERE EXISTS (
    SELECT 1 FROM aspects a
    WHERE AI.IF(PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
                       r.body, a.aspect)))

This NOT EXISTS query finds reviews that do not discuss any of the listed aspects:

SELECT r.id
FROM reviews r
WHERE NOT EXISTS (
    SELECT 1 FROM aspects a
    WHERE AI.IF(PROMPT('Does the review discuss this aspect?\n\nReview: {0}\nAspect: {1}',
                       r.body, a.aspect)))

The subquery must be SELECT 1 FROM <table> WHERE AI.IF(...).

Options

Both AI.IF and AI_FILTER accept an optional second argument with planning hints:

AI.IF(PROMPT(...), {'selectivity': 0.3, 'anchor': 'r'})
AI_FILTER(PROMPT(...), {'selectivity': 0.3, 'anchor': 'r'})

selectivity: optional. Your estimate of the fraction of rows that will pass. It does not need to be accurate. Quail uses it to decide which filters and joins to run first. If you leave it out, Quail assumes 20% of rows pass.
anchor (joins only): optional. When evaluating a join, Quail picks one side to process first and keep in GPU memory, so those documents are not reprocessed for each pair. This side is called the anchor. You usually want it to be the table with longer documents. If omitted, Quail chooses automatically to minimize query cost.

AI.SCORE

AI.SCORE(PROMPT(...)) returns a score between 0 and 1 as a FLOAT64. Higher scores mean the model more strongly favors "yes" over "no" for the prompt. Use it in SELECT to return scores, or compare it with a threshold in WHERE or JOIN ... ON.

Every model runs AI.SCORE:

config = quail.EngineConfig(
    model="qwen3-reranker-0.6b-bf16",
    device="h100-sxm",
)

The rerankers, qwen3-reranker-0.6b-bf16 and qwen3-reranker-4b-bf16, use their own prompt layout. The system message says, "You are performing a data processing task." It asks the model to judge whether the document meets the query's requirements and answer "yes" or "no".
The generative models, qwen3-4b-fp8, qwen3-32b-fp8, and diffusion-gemma-26b-a4b-fp8, use the AI.IF prompt for the same template and compare TRUE with FALSE. AI.SCORE runs that prompt through a different execution path than AI.IF, so a score above 0.5 and the AI.IF answer can differ on some rows. On four QUAIL-B filters they differed on 0% to 2.4% of rows for Qwen3 32B and DiffusionGemma, and on 1.7% to 10% of rows for Qwen3 4B.

Use gpus to run a model copy on each GPU.

Return scores

Give the score column a name with AS:

SELECT d.id,
       AI.SCORE(PROMPT('Does the customer request a refund? {0}', d.body))
           AS refund_score
FROM documents d

Filter rows

Compare the score with <, <=, >, or >=. The threshold may be on either side of the comparison:

SELECT d.id
FROM documents d
WHERE AI.SCORE(
    PROMPT('Does the customer request a refund? {0}', d.body),
    {'selectivity': 0.1}
) >= 0.75

The optional selectivity hint estimates the fraction of rows that pass the comparison. Quail uses it to order filters and assumes 20% if omitted. The hint does not change the scores or threshold.

Join tables

For a prompt with two document arguments, a reranker includes the first in its query and uses the second as its candidate document. A generative model reads the pair in the AI.IF join layout. The template mentions {0} once, where that document is inserted; refer to it again in words:

SELECT q.id, d.id
FROM queries q
JOIN documents d ON AI.SCORE(
    PROMPT('Is {1} relevant to {0}?', q.text, d.body)
) >= 0.8

A pair score returned in SELECT without a comparison runs over every pair, so write the tables with CROSS JOIN. An ON column equality narrows the pairs only when the score is compared in ON.

In the reranker's query text, a placeholder that stands alone at the start or end of the template marks where the document goes and is left out, since the reranker holds the document in its own field. A placeholder inside a sentence reads "this document".

When the same score appears in SELECT, a filter, or a join condition, Quail computes it once. Two comparisons of one score, such as a range, share that one computation. A query cannot combine AI.SCORE with AI.IF.

Score definition

Quail computes the score from the model's yes and no logits:

score = exp(yes_logit) / (exp(yes_logit) + exp(no_logit))

A generative model uses its highest TRUE and FALSE logits, over the spellings TRUE, True, and their space-prefixed forms, in place of yes and no. A reranker is trained to give this score; a generative model is not, so the same threshold can select different fractions of rows on different models.

A score of 0.75 does not mean 75% accuracy. Choose the threshold using labeled examples for your task.

AI.CLASSIFY

Coming soon

Categorize each document into one of a set of labels you provide, like BigQuery's AI.CLASSIFY. Example:

SELECT AI.CLASSIFY(r.body,
    categories => ['positive', 'negative', 'neutral']) AS sentiment
FROM reviews r

AI.EXTRACT

Coming soon

Pull structured fields out of each document, like Snowflake's AI_EXTRACT. Example:

SELECT AI.EXTRACT(r.body, 'movie_title', 'rating') AS extracted
FROM reviews r

AI.MAP

Coming soon

Run a free-form instruction on each document and return the generated text, like Snowflake's AI_COMPLETE. Example:

SELECT AI.MAP('Summarize this review in one sentence.\n\n{0}', r.body) AS summary
FROM reviews r

SELECT, FROM, JOIN, LIMIT

These work the way you expect from standard SQL:

SELECT lists columns. * expands to all columns. No expressions or computed aliases.
FROM names a registered table, with an optional alias.
JOIN and CROSS JOIN are supported. Outer joins (LEFT, RIGHT, FULL) are not.
LIMIT takes a positive integer.

Unsupported SQL operators (for now)

GROUP BY, ORDER BY, DISTINCT, HAVING, window functions
UNION, EXCEPT, INTERSECT, OFFSET
OR between AI predicates
Queries with no AI predicate
JOIN with only relational predicates (no AI operator)
Outer joins (LEFT, RIGHT, FULL)

Using any of these raises quail.logical.CompileError.

SQL reference

On this page