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, score documents, and assign labels using natural language instructions.

For example, suppose you registered a reviews table with columns id and body. This query returns IDs of reviews that discuss the movie's ending:

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.

Quail supports Snowflake and BigQuery syntax:

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

The examples below use the BigQuery dialect (dialect="bq").

PROMPT

PROMPT combines a question template with document column references. The placeholders {0}, {1}, and so on identify the columns in argument order.

For a filter, the prompt references one column:

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

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.

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))

Use AND to combine filters. All AI filters on one table must read the same document column.

Joining

Use AI.IF to join two tables with a natural language condition:

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))

Add column comparisons in ON to restrict the pairs the model evaluates. For example, imagine that each claim has a wiki_url identifying its source passage in an evidence table:

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))

The model evaluates only pairs where c.wiki_url = e.id.

EXISTS and NOT EXISTS

EXISTS keeps a row if the subquery returns at least one match. NOT EXISTS keeps it if the subquery returns no matches.

The following 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)))

Use NOT EXISTS to find reviews that discuss none 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 estimates the fraction of rows or pairs that pass. Quail uses it to choose filter and join order. The default is 0.2, meaning 20%. The hint does not change model answers.
• anchor (joins only) sets the table alias whose document KV is reused across pairs. This side is the anchor. If omitted, Quail selects the anchor using estimated query time. See Joins.

AI.SCORE

Unlike AI.IF, which returns only TRUE or FALSE, AI.SCORE(PROMPT(...)) returns a score between 0 and 1 as a FLOAT64. Higher scores indicate a stronger model preference for a positive answer to your question.

Return scores

For example, imagine a documents table where each row is a customer support request. Its body column contains the request text. Return the score for whether each customer requests a refund:

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

A score in SELECT requires a column alias with AS.

Filter rows

Compare the score with <, <=, >, or >=. For the same support requests, keep rows with a refund score of at least 0.75:

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

Choose the threshold using labeled examples from your task. A score of 0.75 does not establish that the model is correct 75% of the time.

The optional selectivity hint estimates the fraction of rows that pass the comparison. It defaults to 0.2 and does not change the scores. See Options.

Join tables

For example, imagine a queries table of search queries and a documents table of articles. Match each query with articles whose relevance score is at least 0.8:

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

For a prompt that reads two documents, the template must contain {0} once. Refer to that document in words if you need to mention it again.

To return a score for every pair without filtering, use CROSS JOIN and put AI.SCORE in SELECT.

Model behavior

All supported models can run AI.SCORE:

• Qwen3 rerankers are trained to score a document's relevance to a query. For a prompt with two documents, the first is the query and the second is the candidate document.
• Decision 2.0 Kai returns its probability of "Yes" when choosing between "Yes" and "No".
• Qwen3 generative models and DiffusionGemma compare their scores for TRUE and FALSE answers. Quail converts that comparison to a value between 0 and 1.

Quail computes a repeated score once when it appears in SELECT and a condition, or in several comparisons. A query cannot combine AI.SCORE with AI.IF.

AI.CLASSIFY

AI.CLASSIFY returns one of your supplied categories as a STRING. For example, imagine a reviews table with review text in body. Return a sentiment label for each review:

SELECT r.id,
       AI.CLASSIFY(r.body, ARRAY['positive', 'negative', 'mixed']) AS sentiment
FROM reviews r;

A classification in SELECT requires a column alias with AS. The call must be a separate item, rather than part of a larger expression.

Arguments

AI.CLASSIFY(input, categories[, options])

Each category can have a description of up to 25 words. For example, ARRAY[('refund', 'asks for money back'), ('praise', 'expresses approval')]. If labels have the same score, Quail returns the first in the list.

ARRAY[{'label': 'refund', 'description': 'asks for money back'}, {'label': 'praise'}]

Optional parameters

Pass an object as the third argument, such as {'probabilities': true}.

The probabilities sum to 1. They are available for individual documents; classifications of joined rows return labels only.

Query examples

Filter by category

Use IN in WHERE to keep negative or mixed reviews:

SELECT r.id,
       AI.CLASSIFY(PROMPT('What sentiment does this review express? {0}', r.body),
                   ARRAY['positive', 'negative', 'mixed']) AS sentiment
FROM reviews r
WHERE AI.CLASSIFY(PROMPT('What sentiment does this review express? {0}', r.body),
                  ARRAY['positive', 'negative', 'mixed'])
      IN ('negative', 'mixed');

• = keeps one category. Other comparisons raise CompileError.
• A category that is not in the list matches no rows.
• Quail evaluates the repeated classification once and uses its label in both SELECT and WHERE.

Classify joined rows

Use a PROMPT that refers to both documents to classify joined rows. For example, imagine a reviews table of movie reviews and an aspects table of subjects such as acting or plot. Find the aspects each review discusses and classify its sentiment about each matched aspect:

SELECT r.id, a.aspect,
       AI.CLASSIFY(
           PROMPT('What is the opinion of the review about the aspect? {0} {1}',
                  r.body, a.aspect),
           ARRAY['positive', 'negative', 'mixed']) AS opinion
FROM reviews r
JOIN aspects a ON AI.IF(
    PROMPT('Does the review discuss this aspect? {0} {1}', r.body, a.aspect));

These limits apply:

• The classification and its AI.IF join run on one GPU.
• A WHERE condition cannot test the label of a joined row.
• A query cannot classify joined rows and also use AI.SCORE.

For labels computed before later filters, see Classification answers. For the scoring methods, see Label scoring.

WHERE and ON

WHERE keeps rows that satisfy the given conditions. ON specifies which rows to join. Both support the following conditions on table columns:

Values can be numbers, strings, or booleans. IN requires a non-empty list of constant values. Combine conditions with AND. Comparisons exclude null values. Use IS NULL to keep them.

E.g., the following SQL query returns the IDs of reviews from 2024 with at least four stars that mention shipping:

SELECT r.id
FROM reviews r
WHERE r.year = 2024
  AND r.stars >= 4
  AND AI.IF(PROMPT('Does the review mention shipping? {0}', r.body))

Quail checks the year and rating before evaluating AI.IF.

A condition in ON filters the table containing the referenced column. Comparisons between columns from different tables require column = column in ON. See Joining.

SELECT, FROM, JOIN, LIMIT

Quail supports these SQL clauses with the following limits:

• SELECT lists columns and AI.SCORE or AI.CLASSIFY outputs with column aliases. * expands to all table columns. Other computed expressions are unsupported.
• 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.

ORDER BY

ORDER BY sorts result rows by table columns or names assigned to AI outputs with AS. AI expressions cannot appear directly in ORDER BY. Sorting columns need not appear in SELECT, except when using SELECT DISTINCT.

The default direction is ASC. Use DESC for descending order. NULLS FIRST or NULLS LAST overrides the dialect's default null placement:

• BigQuery puts nulls first for ASC and last for DESC.
• Snowflake puts nulls last for ASC and first for DESC.

E.g., imagine a documents table with customer messages in body. This query returns ten documents with the highest refund-request scores:

SELECT d.id,
       AI.SCORE(PROMPT('Does the customer request a refund? {0}', d.body))
           AS refund_score
FROM documents d
ORDER BY refund_score DESC NULLS LAST, d.id ASC
LIMIT 10

The example query uses d.id ASC to order documents with equal scores by ID.

SELECT DISTINCT

SELECT DISTINCT removes duplicate rows by comparing all selected columns. Quail removes duplicates before sorting and applying OFFSET or LIMIT. Every sorting column must appear in SELECT.

OFFSET

OFFSET n skips n rows after sorting and before applying LIMIT. The count must be an integer greater than or equal to zero. Use ORDER BY to specify which rows to skip. Without an explicit order, the skipped rows can differ between runs.

Sorting or removing duplicates prevents LIMIT from stopping AI evaluation early. An offset greater than zero has the same effect.

GROUP BY and HAVING

GROUP BY accepts table columns and names assigned to AI outputs with AS. Return grouping columns and aggregate results in SELECT. Give each aggregate in SELECT an AS name.

HAVING filters groups by comparing an aggregate with a number. Refer to the aggregate by its name or write the function call. Combine conditions with AND.

E.g., the following SQL query returns the review count and average star rating for each sentiment. It keeps sentiments with more than ten reviews:

SELECT AI.CLASSIFY(PROMPT('What is the sentiment of this review? {0}', r.body),
                   ['positive', 'negative', 'mixed']) AS sentiment,
       COUNT(*) AS review_count,
       AVG(r.stars) AS avg_stars
FROM reviews r
GROUP BY sentiment
HAVING review_count > 10
ORDER BY review_count DESC

Quail classifies every review before computing the counts and averages. Adding LIMIT reduces the number of returned groups, not the reviews that Quail classifies.

Aggregate functions

Functions that take a column ignore null values. The column can contain stored values or AI outputs. In SQL, an aggregate can also take an AI.SCORE call directly. Without GROUP BY, aggregates operate on all rows that pass WHERE.

Unsupported SQL

• Window functions, QUALIFY
• UNION, EXCEPT, INTERSECT
• OR between conditions
• BETWEEN, LIKE, and arithmetic in column comparisons
• Queries with no AI operator
• JOIN with only relational predicates (no AI operator)
• Outer joins (LEFT, RIGHT, FULL)
• AI.EXTRACT and AI.MAP

Using any of these raises quail.logical.CompileError.