RAI Python to SQL Comparison

This guide maps common SQL operations to their RAI equivalents, helping users with a SQL background transition smoothly to using RAI.

Core Concepts

The following table maps the core RAI concepts discussed in the Core Concepts guide to their SQL equivalents:

RAI Model SQL Schema

Explanation: An RAI model groups related types and rules—like a Snowflake schema for tables—defining data entities and their relationships.

RAI Type SQL Entity Table / View

Explanation: An RAI type is like a SQL table or view—a set of entities—but it’s schemaless, so properties (i.e., fields) can be added on the fly.

RAI Object SQL Row

Explanation: An object is a row‑like record—an instance of a type—but it can belong to several types, not just one.

RAI Property SQL Column / Relationship Table

Explanation: An RAI property is a column‑like attribute, but it may be single‑ or multi‑valued and isn’t type‑fixed.

RAI Rule SQL Constraint / Trigger / Stored Procedure

Explanation: RAI rules merge SQL‑style constraints, triggers, and stored procedures—enforcing integrity, reacting to changes, and performing complex logic and data transforms.

RAI Query SQL SQL Query

Explanation: RAI queries fetch data like SQL but offer more reasoning power to answer questions SQL can’t.

Key Differences

While RAI and SQL share some similarities, there are several key differences that you should be aware of when transitioning from SQL to RAI.

RAI Dynamic schema SQL Fixed Schema

Explanation: RAI types can add properties on the fly and store mixed value types; SQL tables lock columns and data types upfront.

RAI Multi‑type SQL Single‑table

Explanation: An object can belong to several types; an SQL row exists in only one table.

RAI Immutable Data SQL Mutable Data

Explanation: RAI never changes the stored facts—it derives new states; SQL can rewrite rows with UPDATE.

RAI Set-Based Aggregations SQL Row-Based Aggregations

Explanation: RAI aggregates operate on distinct values; SQL aggregates scan every row, counting duplicates unless DISTINCT is used.

Models

RAI models are logical containers that group related types and rules together. To create a model, import the relationalai package and create a Model object:

RAI

import relationalai as rai

# Additional recommended imports:
from relationalai.std import alias  # For customizing result column names
from relationalai.std import aggregates  # For functions like count, sum, min, max, etc.

model = rai.Model("MyModel")

SQL

CREATE SCHEMA IF NOT EXISTS MyModel;
USE SCHEMA MyModel;

Caution

Models connect to your Snowflake account using your raiconfig.toml profile or connection details provided to the Model constructor. See Cloud Notebooks and Local Installation for details.

See What is a Model? for more information about models.

Types

Use the model.Type() constructor to define a new type in RAI:

RAI

# Create a new type named 'Person'. Person is an instance of the Type class.
Person = model.Type("Person")
# Optionally, declare properties used by the Person type.
Person.id.declare()

SQL

CREATE TABLE IF NOT EXISTS Person (
    id INT
);

Notes:

• Types are schemaless and do not require explicit column definitions. However, if you prefer a structured approach, you may use:

  • Property.declare() to explicitly declare single-valued properties.
  • Property.has_many() to declare multi-valued properties.

• Properties do not have a fixed data type, unlike columns in SQL tables. The same property may reference an integer for one object and to a float for another object.

Objects and Properties

Define Objects from Rows in a Snowflake Table

Use the model.Type() constructor with the source argument to define a type based on a Snowflake table:

# NOTE: `<db>`, `<schema>`, and `<table>` are placeholders for your database,
# schema, and table names, respectively.
Person = model.Type("Person", source="<db>.<schema>.<table>")

# If the table has a column that you intend to use as a multi-valued property,
# you must declare it as multi-valued using Property.has_many(). Otherwise, all
# columns are interpreted as single-valued properties. For example, your source
# data may have an 'email' column that you may want to use as a multi-valued
# property in your model to store multiple email addresses for the same person.
Person.email.has_many()

See Defining Objects from Rows in Snowflake Tables for details.

Define Objects in a Rule

Use the Type.add() method to define new objects in a rule:

RAI

with model.rule():
    Person.add(id=1)

SQL

INSERT INTO Person (id) VALUES (1);

Properties defined in Type.add() serve as the object’s primary key. See Defining Objects in Rules for details.

Set a Single-Valued Property

Use the Instance.set() method to set the value of a single-valued property for an object:

RAI

with model.rule():
    person = Person.add(id=1)  # Person.add() returns an Instance object
    person.set(name="Bob", birthday="1994-03-14")

SQL

-- Dynamically add the 'name' and 'birthday' columns to the 'Person' table.
ALTER TABLE Person
ADD COLUMN IF NOT EXISTS name VARCHAR(255),
ADD COLUMN IF NOT EXISTS birthday DATE;

-- Insert the new record with the 'birthday' property
INSERT INTO Person (id, name, birthday) VALUES (1, 'Bob', '1994-03-14');

See Single-Valued Properties for more information.

Set a Multi-Valued Property

Use the InstanceProperty.extend() method to add values to a multi-valued property:

RAI

with model.rule():
    person = Person.add(id=1)
    person.set(name="Bob", birthday="1994-03-14")
    # person.emails returns an InstanceProperty object. If the emails property
    # has never been set before, it is created by the .extend() method.
    person.emails.extend(["bob@example.com", "bob@company.com"])

SQL

-- Multi-valued properties would be modeled using separate tables with a
-- foreign key to the main table.
CREATE TABLE IF NOT EXISTS PersonEmails (
    person_id INT,
    email VARCHAR(255),
    FOREIGN KEY (person_id) REFERENCES Person(id)
);

-- Insert the email addresses for the person with id=1
INSERT INTO PersonEmails (person_id, email) VALUES
(1, 'bob@example.com'),
(1, 'bob@company.com');

See Multi-Valued Properties for details.

Relationships

Relationships are properties that link objects to other objects in the model. They may be defined based on foreign keys in the source data or created explicitly in rules.

Define a Relationship From a Foreign Key in Source Data

Use the Type.define() method to define relationships based on foreign keys in your source data:

RAI

# Define types sourced from Snowflake tables
Employee = model.Type("Employee", source="<db>.<schema>.employees")
Department = model.Type("Department", source="<db>.<schema>.departments")

# Define the 'department' property on Employee, linking to Department via
# Employee.department_id -> Department.id
Employee.define(department=(Department, "department_id", "id"))

SQL

CREATE TABLE Employee (
    id INT,
    name TEXT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

CREATE TABLE Department (
    id INT,
    name TEXT
);

By default, define() creates a single-valued property. To define a multi-valued property, use the Property.has_many() method:

RAI

Employee = model.Type("Employee", source="<db>.<schema>.employees")
Department = model.Type("Department", source="<db>.<schema>.departments")

# Define a multi-valued relationship between Employee and Department
Employee.define(departments=(Department, "department_id", "id"))
Employee.departments.has_many()

SQL

CREATE TABLE Employee (
    id INT,
    name TEXT
);

CREATE TABLE Department (
    id INT,
    name TEXT
);

-- Join table for the many-to-many relationship
CREATE TABLE EmployeeDepartments (
    employee_id INT,
    department_id INT,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

Notes:

• In the single-valued property case, each Employee object is associated with one Department object via the department_id foreign key in the employees table.
• In the multi-valued property case, where an employee can belong to multiple departments, a join table EmployeeDepartment represents the many-to-many relationship.

Define a Relationship in a Rule

You can define relationships between objects directly in rules by setting properties that link objects:

RAI

with model.rule():
    # Create new Employee and Department objects
    employee = Employee.add(id=1, name="Alice")
    department = Department.add(id=10, name="Engineering")

    # Set the 'department' property of the employee to the department object
    employee.set(department=department)

SQL

-- Insert department
ALTER TABLE Department
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT;
INSERT INTO Department (id, name) VALUES (10, 'Engineering');

-- Insert employee with department_id
ALTER TABLE Employee
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT,
ADD COLUMN IF NOT EXISTS department_id INT;
INSERT INTO Employee (id, name, department_id) VALUES (1, 'Alice', 10);

If an employee can belong to multiple departments, you can define a multi-valued relationship:

RAI

# Declare 'departments' as a multi-valued property
Employee.departments.has_many()

with model.rule():
    # Create Employee and Department objects
    employee = Employee.add(id=1, name="Bob")
    dept_engineering = Department.add(id=10, name="Engineering")
    dept_research = Department.add(id=20, name="Research")

    # Assign multiple departments to the employee
    employee.departments.extend([dept_engineering, dept_research])

SQL

-- Insert departments
ALTER TABLE Department
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT;
INSERT INTO Department (id, name) VALUES (10, 'Engineering'), (20, 'Research');

-- Insert employee
ALTER TABLE Employee
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name TEXT;
INSERT INTO Employee (id, name) VALUES (1, 'Bob');

-- Insert into join table to represent the multi-valued relationship
CREATE TABLE IF NOT EXISTS EmployeeDepartments (
    employee_id INT,
    department_id INT,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (department_id) REFERENCES Department(id)
);
INSERT INTO EmployeeDepartments (employee_id, department_id) VALUES
(1, 10),  -- Bob is in Engineering
(1, 20);  -- Bob is in Research

Notes:

• In the single-valued relationship, the department property links an employee to one department.
• In the multi-valued relationship, the departments property allows an employee to be associated with multiple departments.
• The Employee.departments.has_many() declares departments as a multi-valued property.
• The InstanceProperty.extend() method is used to add multiple departments to an employee.

Access Related Objects

You can perform joins by navigating relationships between objects using the dot (.) operator:

RAI

with model.query() as select:
    employee = Employee()
    # employee.department.name accesses the name property of the related Department object.
    employee.department.name == "Sales"
    response = select(employee.name)

SQL

SELECT e.name
FROM Employee e
JOIN Department d ON e.department_id = d.id
WHERE d.name = 'Sales';

For a multi-valued relationship, you can access related objects similarly:

RAI

with model.query() as select:
    employee = Employee()
    employee.departments.name == "Engineering"  # Multi-valued property
    response = select(employee.name)

SQL

SELECT e.name
FROM Employee e
JOIN EmployeeDepartment ed ON e.id = ed.employee_id
JOIN Department d ON ed.department_id = d.id
WHERE d.name = 'Engineering';

Rules and Queries

Rules define objects, set properties, and establish relationships between objects. Queries retrieve information from the model based on the rules you’ve defined.

A Simple RAI Model and Query

Both rules and queries are written using RAI’s Python query-builder syntax:

RAI

import relationalai as rai

# 1. Create a model.
model = rai.Model("MyModel")

# 2. Define types.
Person = model.Type("Person")
Adult = model.Type("Adult")

# 3. Define a rule to create a new Person object.
with model.rule():
    person = Person.add(id=1)
    person.set(name="Alice", age=30)

# 4. Define a rule that assigns Person objects to the Adult type if their age
# property is greater than or equal to 18.
with model.rule():
    person = Person()
    person.age >= 18
    person.set(Adult)

# 5. Get the names and ages of all adults.
with model.query() as select:
    adult = Adult()
    response = select(adult.name, adult.age)

SQL

-- 1. Create a schema and switch to it.
CREATE SCHEMA IF NOT EXISTS MyModel;
USE SCHEMA MyModel;

-- 2. Create tables for Person and Adult types.
CREATE TABLE IF NOT EXISTS Person;
CREATE TABLE IF NOT EXISTS Adult;

-- 3. Add columns to the Person table and insert a record.
ALTER TABLE Person
ADD COLUMN IF NOT EXISTS id INT,
ADD COLUMN IF NOT EXISTS name VARCHAR,
ADD COLUMN IF NOT EXISTS age INT;
INSERT INTO Person (id, name, age) VALUES (1, 'Alice', 30);

-- 4. Insert records into the Adult table corresponding to Person records
-- with age >= 18.
ALTER TABLE Adult
ADD COLUMN IF NOT EXISTS id INT;
INSERT INTO Adult (id) SELECT id FROM Person WHERE age >= 18;

-- 5. Get the names and ages of all adults.
SELECT name, age
FROM Person p, Adult a
WHERE p.id = a.id;

Notes:

• Rules and queries are defined using the model.rule() and model.query() context managers, respectively.
• Unlike SQL, which executes each command immediately, in RAI, rules are not executed until a query is run.
• Rules are not executed in any particular order, and can be thought of as a set of constraints that are applied to the data when a query is executed.

See Executing Queries and Capturing Knowledge in Rules for more information.

Change Result Column Names

Use the alias() function to customize the names of columns in the query results:

RAI

from relationalai.std import alias

with model.query() as select:
    person = Person()
    response = select(alias(person.name, "full_name"))

SQL

SELECT name AS full_name FROM Person;

Change the Result Format

By default, RAI queries return a pandas DataFrame. Set format="snowpark" in the query to return a Snowpark DataFrame instead:

with model.query(format="snowpark") as select:
    person = Person()
    response = select(person.name)

Sort Results (ORDER BY)

To sort objects or values within a query, use the rank_asc() and rank_desc() functions to compute the rank of each object and include the rank as the first column in the query results:

RAI

from relationalai.std import aggregates

with model.query() as select:
    person = Person()
    rank = aggregates.rank_asc(person.age)
    rank <= 10  # Filter results where rank is at most 10
    response = select(rank, person.name, person.age)

SQL

WITH ranked_persons AS (
    SELECT
        RANK() OVER (ORDER BY age ASC) AS rank,
        name,
        age
    FROM Person
)
SELECT rank, name, age
FROM ranked_persons
WHERE rank <= 10;

Both .rank_asc() and .rank_desc() work similarly to SQL’s RANK() function. The computed rank can be assigned to a variable and used in additional filters or aggregations.

As an alternative, you can also sort results after the query is executed using the pandas .sort_values() method or the Snowpark .sort() method, depending on the format of the query results:

RAI

# pandas format
with model.query() as select:
    person = Person()
    response = select(person.name, person.age)

response.results.sort_values(by='age', inplace=True)

# Snowpark format
with model.query(format="snowpark") as select:
    person = Person()
    response = select(person.name, person.age)

response.results.sort('age')

SQL

SELECT name, age FROM Person ORDER BY age;

Tip

In general, it is more efficient to sort results using pandas and Snowpark DataFrame methods after the query is executed. Consider using the rank_asc() and rank_desc() functions only when you need to use the rank in subsequent operations.

Limit Results (LIMIT)

Use the bottom() function to limit the number of results returned by a query:

RAI

from relationalai.std import aggregates

with model.query() as select:
    person = Person()
    # Limit the results to 10 people.
    aggregates.bottom(10, person)
    response = select(person.name, person.age)

SQL

SELECT name, age FROM Person LIMIT 10;

bottom() can also be used to rank results in ascending order and simultaneously limit the number of results:

RAI

with model.query() as select:
    person = Person()
    # Rank results in ascending order by age and limit to the 10 smallest ages.
    rank = aggregates.bottom(10, person.age)
    response = select(rank, person.name, person.age)

SQL

WITH ranked_persons AS (
    SELECT
        RANK() OVER (ORDER BY age ASC) AS rank,
        name,
        age
    FROM Person
)
SELECT rank, name, age
FROM ranked_persons
WHERE rank <= 10;

Alternatively, top() can be used to both rank results in descending order and limit the number of results:

RAI

with model.query() as select:
    person = Person()
    # Rank results in descending order by age and limit to the top 10 ages.
    rank = aggregates.top(10, person.age)
    response = select(rank, person.name, person.age)

SQL

WITH ranked_persons AS (
    SELECT
        RANK() OVER (ORDER BY age DESC) AS rank,
        name,
        age
    FROM Person
)
SELECT rank, name, age
FROM ranked_persons
WHERE rank <= 10;

Tip

bottom() is often more performant than top() for limiting results, as it avoids the need to sort the entire result set in descending order. Only use top() when you need to sort the results before limiting them.

Filter Data (WHERE)

Conditions in rules and queries are added line-by-line and implicitly combined using AND:

RAI

from relationalai.std.dates import date

with model.query() as select:
    employee = Employee()
    # Filter employees who started after January 1, 2021
    employee.start_date > date(2021, 1, 1)
    # Filter employees in the Engineering or Research departments
    employee.department.in_([Department(name="Engineering"), Department(name="Research")])
    response = select(employee.id, employee.name)

SQL

SELECT e.id, e.name
FROM Employee e
JOIN Department d ON e.department_id = d.id
WHERE e.start_date > '2021-01-01'
  AND d.name IN ('Engineering', 'Research');

Filter expressions are created using the following operators:

Operator	Description
==	Equal to
!=	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
.in_()	In a list of values

See Filtering Objects by Property Value for more details.

Python’s logical operators, like and, or, and not, expect boolean operands. However, RAI filter expressions, like employee.start_date > date(2021, 1, 1), do not return boolean values, since their truth value is only determined once the query or rule is evaluated. For this reason, you cannot use Python’s logical operators to combine filter expressions in RAI.

To express or in RAI, use the model.match(multiple=True) context manager:

RAI

with model.query() as select:
    employee = Employee()
    # Filter employees who started before January 1, 2020 or after January 1, 2021
    with model.match(multiple=True) as matched:
        with employee.start_date < date(2020, 1, 1):
            matched.add(employee)
        with employee.start_date > date(2021, 1, 1):
            matched.add(employee)
    response = select(matched.id, matched.name)

SQL

-- model.match(multiple=True) is equivalent to using UNION ALL in SQL to combine
-- the results of multiple queries.
SELECT id, name
FROM Employee
WHERE start_date < '2020-01-01'
UNION ALL
SELECT id, name
FROM Employee
WHERE start_date > '2021-01-01';

-- Logically, this is equivalent to using OR.
SELECT id, name
FROM Employee
WHERE start_date < '2020-01-01' OR start_date > '2021-01-01';

See Conditional Logic for more information on model.match().

Check for Existence (EXISTS)

You can check for the existence or non-existence of certain conditions or related objects in RAI using model.found() and model.not_found(), similar to SQL’s EXISTS and NOT EXISTS clauses:

RAI

# Get the names of employees who belong to the Research department, and the
# names of other departments they belong to.
with model.query() as select:
    employee = Employee()
    # Check if the employee is in the Research department
    with model.found():
        employee.departments.name == "Research"
    # Retrieve other departments the employee belongs to
    other_department = employee.departments
    other_department.name != "Research"
    response = select(employee.name, other_department.name)

SQL

SELECT e.name, d_other.name
FROM Employee e
JOIN EmployeeDepartments ed1 ON e.id = ed1.employee_id
JOIN Department d1 ON ed1.department_id = d1.id
JOIN EmployeeDepartments ed2 ON e.id = ed2.employee_id
JOIN Department d_other ON ed2.department_id = d_other.id
WHERE EXISTS (
    SELECT 1
    FROM EmployeeDepartments ed
    JOIN Department d ON ed.department_id = d.id
    WHERE ed.employee_id = e.id AND d.name = 'Research'
)
AND d_other.name != 'Research';

RAI

# Get the names of employees who do NOT belong to the Research department, and
# the names of departments they belong to.
with model.query() as select:
    employee = Employee()
    # Check if the employee is NOT in the Research department
    with model.not_found():
        employee.departments.name == "Research"
    # Retrieve departments the employee belongs to
    department = employee.departments
    response = select(employee.name, department.name)

SQL

SELECT e.name, d.name
FROM Employee e
JOIN EmployeeDepartments ed ON e.id = ed.employee_id
JOIN Department d ON ed.department_id = d.id
WHERE NOT EXISTS (
    SELECT 1
    FROM EmployeeDepartments ed2
    JOIN Department d2 ON ed2.department_id = d2.id
    WHERE ed2.employee_id = e.id AND d2.name = 'Research'
);

Aggregate and Group Data (GROUP BY)

Use the functions in the aggregates module to perform aggregations in queries. Unlike SQL, where aggregate function operate over each row in a column, RAI aggregate functions operate on the set of values assigned to a property. If multiple objects have the same value property, it only contributes once to the aggregation.

RAI

from relationalai.std import aggregates, alias

with model.query() as select:
    sale = Sale()
    # WARNING: The following may not work as you expect in RAI. This sums all
    # *distinct* amounts across all sales, rather than all amounts.
    total_sales = aggregates.sum(sale.amount)
    response = select(alias(total_sales, "total_sales"))

SQL

SELECT SUM(DISTINCT amount) AS total_sales
FROM Sale;

RAI

# To sum all amounts, including duplicates, include the sale variable in the sum function:
with model.query() as select:
    sale = Sale()
    # Sum the amounts for each sale object.
    total_sales = aggregates.sum(sale, sale.amount)
    response = select(alias(total_sales, "total_sales"))

SQL

SELECT SUM(amount) AS total_sales
FROM Sale;

Passing the sale object as the first argument to aggregates.sum() allows RAI to aggregate over (sale, sale.amount) pairs instead of just the sale.amount values. When multiple arguments are provided to an aggregate function like .sum(), the aggregation is performed on the last argument.

Use the per parameter to group results in an aggregation:

RAI

with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    total = aggregates.sum(sale, sale.amount, per=[salesperson])
    response = select(salesperson.name, total)

SQL

SELECT s.name, SUM(sa.amount) AS total
FROM Salesperson s
JOIN Sales sa ON sa.salesperson_id = s.id
GROUP BY s.id, s.name;

Additional filters on results of aggregations can be included in the query:

RAI

with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    count = aggregates.count(sale, per=[salesperson])
    count > 10  # Filter salespeople with more than 10 sales
    response = select(salesperson.name, alias(count, "count"))

SQL

SELECT s.name, COUNT(*) AS count
FROM Salesperson s
JOIN Sales sa ON sa.salesperson_id = s.id
GROUP BY s.id, s.name
HAVING COUNT(*) > 10;

Filters are applied in the order they are written in the query and only affect aggregations that follow them:

RAI

from relationalai.std import aggregates

with model.query() as select:
    sale = Sale()
    # First aggregation: Compute total sales amount across all sales.
    total_sales = aggregates.sum(sale, sale.amount)
    # Apply a filter after the first aggregation.
    sale.amount > 1000
    # Second aggregation: Count the number of large sales.
    count_large_sales = aggregates.count(sale)
    response = select(
        alias(total_sales, "total_sales"),
        alias(count_large_sales, "count_large_sales"),
    )

SQL

-- Compute total sales amount across all sales
WITH total_sales AS (
    SELECT SUM(amount) AS total_sales
    FROM Sale
),
-- Filter sales with amount > 1000 and count them
count_large_sales AS (
    SELECT COUNT(*) AS count_large_sales
    FROM Sale
    WHERE amount > 1000
)
-- Select both results
SELECT total_sales.total_sales, count_large_sales.count_large_sales
FROM total_sales, count_large_sales;

See Using Aggregate Functions for more details.

Conditional Logic (CASE-WHEN)

You can implement conditional logic in RAI using the model.match() and model.case() context managers, similar to SQL’s CASE statement:

RAI

with model.query() as select:
    student = Student()
    with model.match() as letter_grade:  # letter_grade is a variable
        with student.grade >= 90:
            letter_grade.add("A")  # Set the value of letter_grade to "A"
        with student.grade >= 80:
            letter_grade.add("B")
        with student.grade >= 70:
            letter_grade.add("C")
        with student.grade >= 60:
            letter_grade.add("D")
        # Default case if none of the above conditions are met.
        with model.case():
            letter_grade.add("F")
    response = select(student.name, alias(letter_grade, "letter_grade"))

SQL

SELECT
    name,
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM
    Student;

For each object in a rule or query, model.match() applies the first condition that evaluates to True and ignores the rest. You can use model.match(multiple=True) to apply multiple conditions. In a query, this works like a UNION ALL in SQL:

RAI

with model.query() as select:
    person = Person()
    # Assign status based on age group. multiple=True allows multiple matches,
    # so people who are over 60 will have status set to both "Senior" and "Adult".
    with model.match(multiple=True) as status:
        with model.case():
            person.age >= 60
            status.add("Senior")
        with model.case():
            person.age >= 18
            status.add("Adult")
        with model.case():
            person.age < 18
            status.add("Minor")
    response = select(person.name, status)

SQL

SELECT name, 'Senior' AS status
FROM Person
WHERE age >= 60
UNION ALL
SELECT name, 'Adult' AS status
FROM Person
WHERE age >= 18
UNION ALL
SELECT name, 'Minor' AS status
FROM Person
WHERE age < 18;

In a rule, model.match() can be used to conditionally set types and properties of objects:

RAI

with model.rule():
    person = Person()
    with model.match():
        # If the person's age is at least 18, assign them to the Adult type.
        with person.age >= 18:
            person.set(Adult)
        # Otherwise, assign them to the Minor type.
        with model.case():
            person.set(Minor)

SQL

-- Insert persons aged 18 or older into the Adult table
INSERT INTO Adult (person_id)
SELECT id
FROM Person
WHERE age >= 18;

-- Insert persons not in the Adult table into the Minor table
INSERT INTO Minor (person_id)
SELECT p.id
FROM Person p
WHERE NOT EXISTS (
    SELECT 1
    FROM Adult a
    WHERE a.person_id = p.id
);

RAI

# Using multiple=True
with model.rule():
    person = Person()
    with model.match(multiple=True):
        with person.age >= 60:
            person.set(Senior)
        with person.age >= 18:
            person.set(Adult)
        with person.age < 18:
            person.set(Minor)

SQL

-- Insert 'Senior' classifications
INSERT INTO Senior (person_id)
SELECT id
FROM Person
WHERE age >= 60;

-- Insert 'Adult' classifications
INSERT INTO Adult (person_id)
SELECT id
FROM Person
WHERE age >= 18;

-- Insert 'Minor' classifications
INSERT INTO Minor (person_id)
SELECT id
FROM Person
WHERE age < 18;

See Expressing if Using model.case() and Expressing if-else Using model.match() for more examples of conditional logic in RAI.

Missing Values

RAI does not have a NULL value. Instead, property values are either present or missing. You can check for missing values and provide default values in RAI, similar to SQL’s IS NULL, IS NOT NULL, and COALESCE.

Note

Properties that are not set may appear as NULL or NaN in query results. See Dealing With Null Values for more details.

Check for Value (IS NOT NULL)

Use the InstanceProperty.has_value() method to check that a property is not missing:

RAI

with model.query() as select:
    person = Person()
    person.age.has_value()
    response = select(person.name)

SQL

SELECT name FROM Person WHERE age IS NOT NULL;

Check for Missing Value (IS NULL)

Use model.not_found() with InstanceProperty.has_value() to check for missing values:

RAI

with model.query() as select:
    person = Person()
    with model.not_found():
        person.age.has_value()
    response = select(person.name)

SQL

SELECT name FROM Person WHERE age IS NULL;

Provide a Default Value (COALESCE)

Use the InstanceProperty.or_() method to provide a default value for a property when it is missing:

RAI

with model.query() as select:
    person = Person()
    response = select(person.name, person.age.or_(0))

SQL

SELECT name, COALESCE(age, 0) AS age FROM Person;

Stored Procedures

You can export Python functions as SQL stored procedures using the @model.export() decorator.

RAI

from typing import Tuple

@model.export("<db>.<schema>")
def get_friends_of_person(id: int) -> Tuple[str]:
    person = Person(id=id)
    friend = person.friends
    return friend.name

SQL

-- NOTE: Replace <db> and <schema> with the names of the database and schema where
-- the stored procedure will be created. The stored procedure name will be the same
-- as the function name.
CREATE FUNCTION get_friends_of_person(id INT)
RETURNS TABLE(name VARCHAR)
AS
BEGIN
    -- Function logic here
END;

See Exporting SQL Stored Procedures for more details.

Resources

• Core Concepts
• Basic Functionality
• RAI Python Reference
• Standard Library Functions