Implementation:Pola rs Polars SQLContext CTE and DDL

Overview

Concrete API usage for advanced SQL features in Polars including Common Table Expressions (CTEs via the WITH clause), CREATE TABLE AS SELECT (materializing query results as new registered tables), and SHOW TABLES (schema introspection). These operations extend the basic execute() method with multi-step workflow capabilities.

Metadata

Field	Value
Namespace	Pola_rs_Polars
Workflow	SQL_Query_Interface
Implementation_ID	Pola_rs_Polars_SQLContext_CTE_and_DDL
Type	Implementation
Category	Data Access / Query Interface
Stage	Advanced Query Operations
last_updated	2026-02-09 10:00 GMT
Source_Repository	https://github.com/pola-rs/polars
Source_Files	docs/source/src/python/user-guide/sql/cte.py:L7-24, create.py:L7-21, show.py:L8-26
Documentation	https://docs.pola.rs

API Signatures

Common Table Expression (CTE)

SQLContext.execute("WITH cte AS (...) SELECT ...") -> LazyFrame or DataFrame

CREATE TABLE AS

SQLContext.execute("CREATE TABLE name AS SELECT ...") -> LazyFrame or DataFrame

SHOW TABLES

SQLContext.execute("SHOW TABLES") -> LazyFrame or DataFrame

I/O Contract

Direction	Type	Description
Input	str (WITH ... SELECT ...)	CTE query string with named subqueries
Input	str (CREATE TABLE ... AS SELECT ...)	DDL statement creating a new table from a query result
Input	str (SHOW TABLES)	Introspection command to list all registered tables
Output	LazyFrame / DataFrame	CTE query returns standard result (governed by eager setting)
Output	LazyFrame / DataFrame	CREATE TABLE AS returns the created table's data
Output	DataFrame	SHOW TABLES returns a single-column frame of table names

Implementation Examples

Common Table Expression (CTE)

import polars as pl

ctx = pl.SQLContext()
ctx.register("my_table", pl.LazyFrame({
    "name": ["Alice", "Bob", "Charlie", "David"],
    "age": [25, 30, 35, 40],
}))

# Use a CTE to define an intermediate result, then query it
result = ctx.execute("""
    WITH older_people AS (
        SELECT * FROM my_table WHERE age > 30
    )
    SELECT * FROM older_people WHERE STARTS_WITH(name, 'C')
""", eager=True)

# result contains only Charlie (age 35)

Multiple CTEs in a Single Query

import polars as pl

ctx = pl.SQLContext()
ctx.register("employees", pl.LazyFrame({
    "name": ["Alice", "Bob", "Charlie", "David", "Eve"],
    "department": ["Eng", "Sales", "Eng", "Sales", "Eng"],
    "salary": [90000, 70000, 85000, 75000, 95000],
}))

# Chain multiple CTEs for step-by-step transformation
result = ctx.execute("""
    WITH engineering AS (
        SELECT * FROM employees WHERE department = 'Eng'
    ),
    high_earners AS (
        SELECT * FROM engineering WHERE salary > 88000
    )
    SELECT name, salary FROM high_earners ORDER BY salary DESC
""", eager=True)

CREATE TABLE AS SELECT

import polars as pl

ctx = pl.SQLContext()
ctx.register("my_table", pl.LazyFrame({
    "name": ["Alice", "Bob", "Charlie", "David"],
    "age": [25, 30, 35, 40],
}))

# Materialize a filtered subset as a new table in the context
ctx.execute("CREATE TABLE high_age AS SELECT * FROM my_table WHERE age > 30")

# The new table 'high_age' is now registered and queryable
result = ctx.execute("SELECT * FROM high_age", eager=True)

SHOW TABLES for Schema Introspection

import polars as pl

ctx = pl.SQLContext()
ctx.register("my_table", pl.LazyFrame({
    "name": ["Alice", "Bob", "Charlie", "David"],
    "age": [25, 30, 35, 40],
}))

# Create a derived table
ctx.execute("CREATE TABLE high_age AS SELECT * FROM my_table WHERE age > 30")

# List all registered tables
tables = ctx.execute("SHOW TABLES", eager=True)
# Returns a DataFrame with table names: my_table, high_age

Complete Multi-Step Workflow

import polars as pl

# Set up context with source data
ctx = pl.SQLContext(eager=True)
ctx.register("raw_data", pl.LazyFrame({
    "name": ["Alice", "Bob", "Charlie", "David", "Eve"],
    "age": [25, 30, 35, 40, 28],
    "score": [88, 92, 75, 95, 83],
}))

# Step 1: Create filtered table via DDL
ctx.execute("CREATE TABLE seniors AS SELECT * FROM raw_data WHERE age >= 30")

# Step 2: Verify tables
print(ctx.execute("SHOW TABLES"))

# Step 3: Use CTE to further analyze the created table
result = ctx.execute("""
    WITH high_scorers AS (
        SELECT * FROM seniors WHERE score > 80
    )
    SELECT name, age, score FROM high_scorers ORDER BY score DESC
""")

Behavioral Notes

• CTEs are scoped to a single execute() call. They do not persist across multiple execute() calls and are not added to the context catalog.
• CREATE TABLE AS registers the result as a new table in the context. The table persists for the lifetime of the SQLContext or until overwritten by another CREATE TABLE with the same name.
• SHOW TABLES returns a DataFrame with a single column named "name" containing the names of all registered tables.
• Multiple CTEs in a single WITH clause are separated by commas. Later CTEs can reference earlier CTEs defined in the same clause.
• CREATE TABLE AS returns the data of the created table, in addition to registering it. This allows inspection of the result in the same call.

Relationships

• Principle:Pola_rs_Polars_Advanced_SQL_Features

See Also

• Implementation:Pola_rs_Polars_SQLContext_Constructor — Creating the SQLContext
• Implementation:Pola_rs_Polars_SQLContext_Register — Registering tables via the API
• Implementation:Pola_rs_Polars_SQLContext_Execute — Basic query execution
• Implementation:Pola_rs_Polars_SQL_Collect_and_Output — Collecting and writing query results