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
- Complex reporting question
- Write each step as a CTE (WITH ... AS)
- Main query selects from the last CTE
- Need to compare with an overall value or another table? Yes: Add a scalar or EXISTS/IN subquery. No: Skip subqueries.
- Add CASE WHEN for labels and buckets
- 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 ENDExample
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
- WITH name AS (...) names a step; later CTEs can use earlier ones
- Scalar subqueries must return one row and one column
- IN / EXISTS subqueries are rewritten into semi/anti joins by the optimizer
- Beware NOT IN with NULLs in the subquery: it returns no rows; prefer NOT EXISTS
- CASE WHEN checks conditions in order and returns NULL if no ELSE matches