5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions

Apache Spark SQL provides a powerful declarative relational interface layered directly over Spark's distributed computation engine. Rather than writing procedural low-level RDD or DataFrame transformations, developers can express complex analytical workflows using standard ANSI SQL. Under the hood, the Catalyst optimizer parses, analyzes, optimizes, and compiles SQL queries into the exact same optimized physical execution plans (consisting of RDD stages and tasks) as code written using the PySpark DataFrame API.

Mastering advanced Spark SQL requires understanding how declarative queries interact with DataFrames, how Common Table Expressions (CTEs) are treated by the query planner, how subqueries are rewritten into distributed joins, and how built-in string, date, and conditional expressions execute across cluster executors.

---

1. Executing SQL via spark.sql() and DataFrame Interoperability

The primary entry point is spark.sql(). SELECT returns a DataFrame whose row results are normally lazy. SQL commands such as CREATE, INSERT, or DROP may execute when spark.sql() is called; a DataFrame return type does not make every SQL statement lazy.

# Executing a standard SQL query returning a DataFrame
df_orders = spark.sql("""
    SELECT customer_id, COUNT(order_id) AS total_orders, SUM(order_amount) AS lifetime_spend
    FROM sales.orders
    WHERE order_status = 'COMPLETED'
    GROUP BY customer_id
""")

Lazy Evaluation Mechanics

A normal SELECT usually defers its row query, whereas eager commands and metadata discovery can do work immediately. Like any PySpark DataFrame transformation, SQL queries are lazily evaluated:

1. Analysis Phase: Spark parses the SQL string, validates syntax, and resolves table names, column identifiers, and function signatures against the active catalog metastore.
2. Logical Planning: Catalyst creates an unresolved logical plan, resolves it into an optimized logical plan, and prepares physical execution strategies.
3. Execution Barrier: No data is scanned from storage and no executor tasks are launched until an action (such as .show(), .count(), .collect(), or .write.save()) is explicitly invoked on the resulting DataFrame.

Registering DataFrames as SQL Views

To query an existing in-memory PySpark DataFrame using SQL, it must first be registered in the catalog as a temporary view. Spark provides two distinct view registration scopes:

# 1. Register a session-scoped temporary view
df_raw.createOrReplaceTempView("staged_transactions")

# Queryable only within the current active SparkSession
spark.sql("SELECT * FROM staged_transactions WHERE amount > 1000").show()

# 2. Register a global temporary view shared across sessions
df_raw.createOrReplaceGlobalTempView("company_wide_transactions")

# Must be qualified with the 'global_temp' system database identifier
spark.sql("SELECT * FROM global_temp.company_wide_transactions").show()

# Child sessions created via spark.newSession() can access global views, but NOT standard temp views
analytics_session = spark.newSession()
analytics_session.sql("SELECT * FROM global_temp.company_wide_transactions").show()  # Succeeds
# analytics_session.sql("SELECT * FROM staged_transactions").show()                 # Fails: Table not found

Fluent Chaining: Interoperability

Because spark.sql() produces a standard DataFrame, developers can freely interleave SQL expressions and DataFrame API transformations:

from pyspark.sql.functions import col, round

# Chain SQL query with PySpark transformations
final_df = (
    spark.sql("SELECT customer_id, order_amount, order_date FROM staged_transactions")
    .filter(col("order_amount") > 50.0)
    .withColumn("tax_amount", round(col("order_amount") * 0.0825, 2))
    .select("customer_id", "order_amount", "tax_amount")
)

Parameterized Queries in Spark SQL (Spark 3.4+)

To prevent SQL injection and avoid fragile string concatenation, modern Spark SQL supports parameterized query invocation:

# Safe parameter binding using named variables
query = "SELECT * FROM sales.orders WHERE region = :target_region AND order_amount >= :min_spend"
filtered_df = spark.sql(query, args={"target_region": "EMEA", "min_spend": 250.0})

---

2. Common Table Expressions (CTEs): Syntax, Chaining, and Catalyst Inlining

A Common Table Expression (CTE) defines a temporary named result set that exists solely within the execution scope of a single SQL statement. Declared using the WITH clause, CTEs break complex, deeply nested queries into modular, logical steps.

Syntax and Multiple CTE Chaining

When chaining multiple CTEs, the WITH keyword is declared only once at the beginning of the statement. Subsequent CTE declarations are separated by commas, and later CTEs can reference earlier CTEs:

WITH 
    -- CTE 1: Filter and clean initial transactions
    cleaned_orders AS (
        SELECT order_id, customer_id, order_amount, order_date
        FROM sales.orders
        WHERE order_status = 'COMPLETED' AND order_amount IS NOT NULL
    ),
    -- CTE 2: Aggregate customer spend metrics referencing CTE 1
    customer_aggregates AS (
        SELECT 
            customer_id,
            COUNT(order_id) AS total_orders,
            SUM(order_amount) AS total_spend,
            MAX(order_date) AS last_order_date
        FROM cleaned_orders
        GROUP BY customer_id
    )
-- Main Outer Query consuming CTE 2
SELECT 
    c.customer_name,
    ca.total_orders,
    ca.total_spend,
    ca.last_order_date
FROM customer_aggregates ca
JOIN sales.customers c ON ca.customer_id = c.customer_id
WHERE ca.total_spend > 5000.0;

Catalyst Optimizer Plan Inlining

A critical performance and architectural principle tested on the Databricks certification exam is how Spark treats CTEs under the hood:

• Syntactic Sugar, Not Materialization: In Spark SQL, CTEs are purely logical abstractions. Declaring a CTE does not create a temporary physical table on disk, nor does it persist the CTE's intermediate output in executor memory.
• Plan Inlining: During query optimization, Catalyst may inline eligible CTE definitions; it can also retain/reuse exchanges or subplans. A CTE name alone does not promise materialization or reuse.
• Redundant Computation Risk: If an expensive CTE (such as one performing heavy aggregations or multi-table joins) is referenced multiple times within the same statement (for example, in two separate join branches or a UNION ALL), Spark may evaluate repeated subplans more than once, depending on optimization and exchange reuse; inspect the physical plan.

User Query: WITH cte AS (Expensive Scan + Agg) SELECT * FROM cte JOIN cte ...
                                    │
                                    ▼
Catalyst Inlined Plan: (Expensive Scan + Agg) JOIN (Expensive Scan + Agg)

---

3. Subqueries in Spark SQL: Scalar, Correlated, and Predicates

A subquery is a SELECT statement nested inside an outer SQL query. Spark SQL categorizes subqueries based on their return shape (scalar vs table) and whether they reference columns from outer relations (correlated vs non-correlated).

Scalar Subqueries

A scalar subquery returns at most one row and exactly one column. Because it evaluates to a single scalar value, it can be placed wherever a literal or column expression is valid, including SELECT lists and comparison predicates in WHERE and HAVING clauses.

-- Scalar subquery in the SELECT list and WHERE clause
SELECT 
    emp_id,
    emp_name,
    salary,
    salary - (SELECT AVG(salary) FROM enterprise.employees) AS diff_from_company_avg
FROM enterprise.employees
WHERE salary > (SELECT AVG(salary) FROM enterprise.employees);

Runtime Multi-Row Exception

If a scalar subquery returns zero rows at runtime, Spark evaluates the expression as NULL. However, if the scalar subquery returns two or more rows, Spark cannot decide which value to assign to the scalar slot. Physical execution terminates immediately with a runtime error:

$$\texttt{org.apache.spark.SparkRuntimeException: Scalar subquery returned more than one row}$$

This behavior is strictly enforced during runtime execution; the query analyzer cannot always predict how many rows a subquery will produce until the data is scanned.

Correlated vs Non-Correlated Subqueries

• Non-Correlated Subquery: Completely self-contained; references no attributes from the outer query. Catalyst evaluates the subquery once, broadcasting or caching the result across executors.
• Correlated Subquery: References one or more columns from the enclosing outer query relation (termed an outer reference).

-- Correlated subquery: inner query references outer table alias 'e.dept_id'
SELECT e.emp_id, e.emp_name, e.dept_id, e.salary
FROM enterprise.employees e
WHERE e.salary > (
    SELECT AVG(inner_emp.salary)
    FROM enterprise.employees inner_emp
    WHERE inner_emp.dept_id = e.dept_id
);

During optimization, Catalyst decorrelates subqueries wherever possible, rewriting nested row-by-row lookups into distributed hash or sort-merge joins.

Subquery Predicates in WHERE: IN, EXISTS, NOT IN, NOT EXISTS

When subqueries are placed in WHERE filter clauses, Spark rewrites them into relational joins under the hood:

-- 1. Subquery using IN
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

-- 2. Equivalent subquery using EXISTS
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Both queries are transformed by Catalyst into a LEFT SEMI JOIN. A Left Semi Join emits rows from the left table (customers) if a match exists in the right table (orders), without duplicating left rows and without including right-table columns in the output schema.

The Critical NOT IN NULL Trap

A classic trap on certification exams involves the behavioral difference between NOT IN and NOT EXISTS when NULL values are present.

Under ANSI SQL three-valued logic, boolean expressions evaluate to TRUE, FALSE, or UNKNOWN (represented by NULL):

$$\text{val NOT IN (1, 2, NULL)} \iff (\text{val} \neq 1) \text{ AND } (\text{val} \neq 2) \text{ AND } (\text{val} \neq \text{NULL})$$

Because any equality or inequality comparison with NULL yields UNKNOWN, the entire chained AND expression evaluates to UNKNOWN whenever val is not in the set. Since WHERE clauses require a strict TRUE to retain a row, all rows are discarded.

---