5.2 Complex Joins, CTEs & Set Operations

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

5.2 Complex Joins, CTEs & Set Operations

Key Takeaways Explain This Topic AI

Cross-system analytics joins local Unity Catalog Delta tables with Foreign Catalog tables from Lakehouse Federation in a single SQL statement.

Databricks SQL's Catalyst Optimizer automatically converts Common Table Expressions (CTEs) defined with WITH clauses into inline execution nodes or materializes them when referenced 2 or more times to prevent duplicate computation.

In Databricks SQL, SEMI JOIN returns distinct rows from the left table where a match exists in the right table without duplicating left rows or expanding the result set, while ANTI JOIN returns rows from the left table with no right match.

Set operations in Databricks SQL require matching data types across corresponding columns; UNION ALL preserves duplicate rows with O(1) appending overhead, whereas UNION or UNION DISTINCT triggers a broad hash shuffle to deduplicate.

Broadcast Hash Joins (BHJ) are automatically chosen by Databricks SQL when a table size is below spark.sql.autoBroadcastJoinThreshold (default 10 MB, configurable up to several GBs), bypassing expensive shuffle operations across cluster nodes.

Last updated: July 2026

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

Confirm the Foreign Connection and Foreign Catalog exist and that you have privileges to query the remote objects.

Prefer pushdown-friendly filters on the federated side ( WHERE , projections) so Lakehouse Federation minimizes data transfer.

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.

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;

Join Type Output Rows Included Column Selection Allowed Duplicate Prevention
INNER JOIN Left rows matching Right rows Left & Right table columns Can expand rows if Right has duplicates
LEFT SEMI JOIN Left rows matching Right rows Left table columns ONLY Guarantees NO row expansion
LEFT ANTI JOIN Left rows with NO Right match Left table columns ONLY Guarantees NO row expansion
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

UNION ALL : Appends result sets directly. It performs no deduplication, making it an extremely fast O ( 1 ) O(1) O ( 1 ) operation without network shuffle.

UNION / UNION DISTINCT : Merges result sets and executes a global shuffle and hash-deduplication step to remove identical rows.

INTERSECT / INTERSECT DISTINCT : Returns only rows that exist in both input queries, deduplicating the output.

EXCEPT / MINUS : Returns rows from the first query that do not exist in the second query.