CTEs, subqueries & CASE

Apache Spark · Spark SQL

Theory

A CTE (common table expression) names a sub-query with WITH name AS (...) so the main query can use it like a table; several CTEs can be chained, each building on the previous one, which makes long queries readable step by step. A subquery is a query inside another query: a scalar subquery returns one value (amount > (SELECT AVG(amount) FROM orders)), IN and EXISTS subqueries filter rows by membership in another result, and a subquery in FROM acts as a derived table.

CASE WHEN ... THEN ... ELSE ... END adds if/else logic inside SQL, for labels, buckets or conditional sums. Spark's optimizer often rewrites EXISTS and IN subqueries into semi or anti joins, so they run as efficiently as explicit joins. For example, a CTE region_totals sums amount per region, and the main query labels regions with total >= 100000 as 'Top' and the rest as 'Regular'.

Why we use it

Real reporting queries have several steps. CTEs keep each step named and readable, subqueries express 'compare with the average' or 'only customers who ordered' naturally, and CASE handles business rules, all without creating temporary tables.

When to use it

Use CTEs to break a complex query into readable steps, scalar subqueries for comparisons with an overall value, IN/EXISTS for membership filters, and CASE for conditional columns. If the same CTE result is reused by many queries, cache it as a DataFrame or write a table instead.

How it works

  1. Complex reporting question
  2. Write each step as a CTE (WITH ... AS)
  3. Main query selects from the last CTE
  4. Need to compare with an overall value or another table? Yes: Add a scalar or EXISTS/IN subquery. No: Skip subqueries.
  5. Add CASE WHEN for labels and buckets
  6. Run with spark.sql and show or write

Syntax

WITH t AS (SELECT ...), t2 AS (SELECT ... FROM t)
SELECT ... FROM t2
WHERE amount > (SELECT AVG(amount) FROM orders)
WHERE customer_id IN (SELECT customer_id FROM ...) / EXISTS (SELECT 1 ...)
CASE WHEN cond THEN a WHEN cond2 THEN b ELSE c END

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[1]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

orders = spark.createDataFrame([
    (101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (102, 2, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (103, 1, "North", "Mouse",  3,  1500.0, "2026-01-06"),
    (104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (105, 4, "West",  "Tablet", 1, 25000.0, "2026-01-08"),
    (106, 2, "East",  "Phone",  1, 15000.0, "2026-01-08"),
    (107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
    (108, 3, "South", "Mouse",  4,  2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
   "qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

customers = spark.createDataFrame([
    (1, "Asha",   "Jaipur",    "Retail"),
    (2, "Ravi",   "Pune",      "Corporate"),
    (3, "Meera",  "Delhi",     "Retail"),
    (4, "Karan",  "Jaipur",    "SMB"),
    (5, "Priya",  "Mumbai",    "Corporate"),
    (6, "Vikram", "Bengaluru", "SMB"),
], "customer_id INT, name STRING, city STRING, segment STRING")

orders.createOrReplaceTempView("orders")
customers.createOrReplaceTempView("customers")

spark.sql("""
    WITH region_totals AS (
        SELECT region, SUM(amount) AS total FROM orders GROUP BY region
    )
    SELECT region, total,
           CASE WHEN total >= 100000 THEN 'Top' ELSE 'Regular' END AS tier
    FROM region_totals
    ORDER BY total DESC
""").show()

# Scalar subquery: orders above the overall average
spark.sql("""SELECT order_id, amount FROM orders
             WHERE amount > (SELECT AVG(amount) FROM orders) ORDER BY order_id""").show()

# EXISTS / NOT EXISTS subqueries (run as semi / anti joins)
spark.sql("""SELECT name FROM customers c
             WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)""").show()

# Output:
# +------+--------+-------+
# |region|   total|   tier|
# +------+--------+-------+
# | North|104500.0|    Top|
# |  West| 55000.0|Regular|
# | South| 54000.0|Regular|
# |  East| 15000.0|Regular|
# +------+--------+-------+
#
# +--------+-------+
# |order_id| amount|
# +--------+-------+
# |     101|55000.0|
# |     102|30000.0|
# |     104|52000.0|
# |     107|48000.0|
# +--------+-------+
#
# +------+
# |  name|
# +------+
# |Vikram|
# +------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic