5.2 Complex Joins, CTEs & Set Operations

Combining and transforming complex datasets across lakehouse tables requires a firm grasp of SQL join algorithms, subquery constructs, Common Table Expressions (CTEs), and set operations in Databricks SQL. The Databricks Catalyst Optimizer automatically parses SQL statements, optimizes execution plans, and selects appropriate join strategies based on data size and table statistics.

Join Strategies & Execution Mechanics in Databricks SQL

When executing join operations across Delta Lake tables, Databricks SQL employs different physical join strategies depending on table size, join predicates, and available cluster memory.

1. Broadcast Hash Join (BHJ)

If one of the join tables is small enough to fit into executor memory, Databricks SQL broadcasts the small table to all worker nodes in the cluster. This completely eliminates the costly network shuffle of the larger table.

• Threshold: By default, Databricks SQL considers tables under 10 MB (configured via spark.sql.autoBroadcastJoinThreshold) for broadcast joins.
• Hint: Analysts can explicitly request a broadcast join using the /*+ BROADCAST(small_table) */ query hint.

SELECT /*+ BROADCAST(d) */
    f.order_id,
    f.amount,
    d.department_name
FROM main.sales.fact_orders f
JOIN main.sales.dim_department d
  ON f.department_id = d.department_id;

2. Shuffle Hash Join & Sort-Merge Join (SMJ)

For joins between two large tables where neither table fits into memory, Databricks SQL re-partitions data across cluster nodes using a hash key on the join columns. Historically, Sort-Merge Join sorted both datasets before merging. In modern Databricks SQL Warehouses equipped with the Photon engine, high-speed Shuffle Hash Joins optimized for SIMD vectorized execution are favored to achieve maximum throughput.

---

Cross-System Analytics: Joining Delta Tables with Federated Sources

Section 4 of the official exam guide requires querying cross-system analytics by joining data from a Delta table and a federated data source. After a Foreign Catalog is registered in Unity Catalog, analysts write ordinary SQL joins that mix local Delta Lake tables with remote RDBMS/warehouse tables.

-- Local governed Delta fact + federated PostgreSQL dimension
SELECT
  o.order_id,
  o.order_date,
  c.customer_name,
  c.loyalty_tier,
  o.order_total
FROM main.gold.orders_delta AS o
INNER JOIN pg_sales_catalog.public.customers AS c
  ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01';

Analyst checklist for federated joins

1. Confirm the Foreign Connection and Foreign Catalog exist and that you have privileges to query the remote objects.
2. Prefer pushdown-friendly filters on the federated side (WHERE, projections) so Lakehouse Federation minimizes data transfer.
3. Treat federated objects as typically read-only analytical sources; persist curated results to Delta when you need time travel, Liquid Clustering, or dashboard-friendly local performance.
4. Compare with Delta Sharing: federation queries live external databases; Delta Sharing reads shared Delta/Parquet datasets without copying.

Exam tip: the correct pattern is a single SQL statement joining main... (Delta) with foreign_catalog.schema.table, not an ETL copy followed by a local-only join — unless the prompt explicitly requires materializing a local snapshot.

Semi Joins, Anti Joins, and Subquery Optimizations

Databricks SQL provides specialized join types designed to filter data efficiently without expanding row counts or requiring explicit subquery evaluation.

LEFT SEMI JOIN

A LEFT SEMI JOIN returns rows from the left table that have at least one matching record in the right table. Unlike an INNER JOIN, a semi join never duplicates left-table rows even if multiple matching rows exist in the right table, and it selects columns strictly from the left table.

-- Returns distinct customers who placed an order in 2026
SELECT c.customer_id, c.customer_name, c.email
FROM main.crm.customers c
LEFT SEMI JOIN main.sales.orders o
  ON c.customer_id = o.customer_id
 WHERE o.order_date >= '2026-01-01';

LEFT ANTI JOIN

A LEFT ANTI JOIN returns rows from the left table that have no matching record in the right table. It serves as a highly performant alternative to WHERE key NOT IN (SELECT key FROM ...), avoiding subtle NULL handling bugs inherent to NOT IN subqueries.

-- Returns active customers who have never placed an order
SELECT c.customer_id, c.customer_name
FROM main.crm.customers c
LEFT ANTI JOIN main.sales.orders o
  ON c.customer_id = o.customer_id;

---

Lateral Joins & Array Unnesting with Table-Valued Functions

In modern data analytics, Delta tables frequently store nested structures such as JSON payloads, arrays, and maps. Standard join operators cannot directly correlate a scalar table row with an array column contained inside that same row. Databricks SQL supports LATERAL joins and LATERAL VIEW expressions to solve this challenge.

LATERAL Joins

A LATERAL join allows a inline subquery or table-valued generator function on the right side of the join to reference columns produced by tables on the left side.

-- Flattening array of purchase tags stored per customer
SELECT 
    c.customer_id, 
    c.customer_name, 
    t.tag_name
FROM main.crm.customers c
LATERAL VIEW explode(c.interest_tags) t AS tag_name;

Using the explicit JOIN LATERAL syntax:

SELECT 
    c.customer_id, 
    c.signup_date, 
    recent_orders.order_id, 
    recent_orders.amount
FROM main.crm.customers c
JOIN LATERAL (
    SELECT order_id, amount 
    FROM main.sales.orders o 
    WHERE o.customer_id = c.customer_id 
    ORDER BY order_date DESC 
    LIMIT 3
) recent_orders;

---

Common Table Expressions (CTEs) & Chained Query Blocks

Common Table Expressions (CTEs), declared using the WITH statement, break complex queries into modular, human-readable logic blocks. In Databricks SQL, CTEs are not merely syntactic sugar; the Catalyst Optimizer analyzes CTE usage patterns to optimize physical execution.

WITH regional_sales AS (
    SELECT 
        region, 
        SUM(amount) AS total_revenue
    FROM main.sales.orders
    WHERE status = 'COMPLETED'
    GROUP BY region
),
ranked_regions AS (
    SELECT 
        region, 
        total_revenue,
        DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS rank_pos
    FROM regional_sales
)
SELECT region, total_revenue
FROM ranked_regions
WHERE rank_pos <= 5;

Catalyst Optimization of CTEs

• Inlining: Single-use CTEs are usually inlined directly into the primary query plan.
• Materialization: If a CTE is referenced multiple times in a query (e.g., joined against itself), Databricks SQL can automatically compute and cache the CTE results temporarily to eliminate redundant scans.

---

Set Operations: UNION, INTERSECT, and EXCEPT

Set operations combine result sets from two or more queries into a single output. Corresponding columns across all queries must have compatible data types.

-- UNION vs UNION ALL comparison
SELECT email FROM main.crm.web_leads
UNION ALL -- Fast O(1) append; retains duplicates
SELECT email FROM main.crm.store_leads;

SELECT email FROM main.crm.web_leads
UNION DISTINCT -- Triggers broad hash shuffle to remove duplicates
SELECT email FROM main.crm.store_leads;

Set Operation Rules & Performance Characteristics

1. UNION ALL: Appends result sets directly. It performs no deduplication, making it an extremely fast $O(1)$ operation without network shuffle.
2. UNION / UNION DISTINCT: Merges result sets and executes a global shuffle and hash-deduplication step to remove identical rows.
3. INTERSECT / INTERSECT DISTINCT: Returns only rows that exist in both input queries, deduplicating the output.
4. EXCEPT / MINUS: Returns rows from the first query that do not exist in the second query.