SQL joins and window functions with results

A join combines columns from two tables by matching rows, and a window function calculates across a set of related rows without collapsing them into one. The decision rule: use a join when you need columns from another table, use a window function when you need a per-group calculation (rank, running total, previous value) and still want every row back. GROUP BY collapses rows, a window function with OVER does not.

Every result below was produced by running the statements on a small data set (SQLite 3.46, using the standard SQL syntax that T-SQL, Spark SQL and Databricks SQL share for these cases). Run them yourself; reading a result table is faster than reading a definition.

The data used for every example

customers

orders (order 13 has no customer)

Join types, side by side

FROM customers c JOIN orders o ON o.customer_id = c.id with four variants:

A right join is a left join with the tables swapped, so exams use it to test whether you can read which side is preserved. The preserved side is the one written next to the word LEFT or RIGHT.

Anti join finds rows with no match. Customers who never ordered:

SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;

Result: one row, Cleo. Spark and Databricks also offer LEFT ANTI JOIN and LEFT SEMI JOIN directly; a semi join returns left rows that have a match but no right-side columns and never duplicates them.

Where the filter goes changes the answer

For a left join, a condition on the right table in WHERE runs after the join and removes the NULL rows, which quietly turns it into an inner join. The same condition in ON filters only the matching:

This is the most common reason a “customers with their large orders” report loses customers.

Row multiplication: the silent join bug

Joins multiply rows when the key is not unique on the other side. Give Ada two phone numbers in a ph table, join orders to ph on customer, and sum the amounts for Ada: the result is 700 instead of the true 350, because each of her two orders now appears twice. The symptom exams describe is “the total is higher after adding a join” or a fact table that grows from 50,000 to 73,000 rows. The fix is to aggregate first or join to a table that is unique on the key, which matters for any exam that asks you to join and then aggregate data, such as DP-600 and the Databricks exams. A star schema avoids this by design, see star schema and modelling.

Window functions: the OVER clause

A window function has three parts inside OVER (...): PARTITION BY (the groups, optional), ORDER BY (the sequence within each group) and optionally a frame. For customers 1 and 2:

SELECT order_id, customer_id, amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn,
  SUM(amount)  OVER (PARTITION BY customer_id ORDER BY order_date)      AS running,
  LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date)      AS prev
FROM orders WHERE customer_id IS NOT NULL;

All three rows survive; a GROUP BY would have returned two. Note that running uses ORDER BY inside the window, which makes the sum cumulative. Without ORDER BY the sum covers the whole partition.

ROW_NUMBER, RANK and DENSE_RANK on ties

Scores A=90, B=90, C=80, D=70 ordered descending:

ROW_NUMBER never repeats, so on a tie it picks an arbitrary order unless you add a tiebreaker column. RANK repeats and then skips (1, 1, 3). DENSE_RANK repeats without skipping (1, 1, 2).

Latest row per group

The pattern most often tested: number the rows per group newest first, then keep number 1. Window functions cannot be used directly in WHERE, so wrap the query:

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders o WHERE customer_id IS NOT NULL
) t WHERE rn = 1;

Result: order 11 for Ada and order 12 for Ben. Databricks SQL and some other engines also offer QUALIFY to filter on a window result without the wrapper, T-SQL does not.

Which exams test this

How questions are built around joins and windows

The scenario gives a symptom and asks for the cause or the query. “Customers with no orders” is an anti join. “Total is too high after the join” is row multiplication. “A left join returns the same rows as an inner join” is a right-table filter in WHERE. “Top one per group” or “most recent per customer” is ROW_NUMBER with a partition, and “ties share the same position” is RANK or DENSE_RANK, where “no gaps” decides between them. “Compare with the previous period” is LAG. A subquery per row is usually presented as the slow wrong option next to a join or window. Delta tables in a medallion pipeline use these same patterns to deduplicate in silver, and Delta Lake MERGE often takes the ROW_NUMBER result as its source.

Go deeper

• JOIN in Databricks SQL (Databricks documentation)
• Window functions in Databricks SQL (Databricks documentation)
• SELECT with the OVER clause in T-SQL (Microsoft Learn)
• FROM clause with joins in T-SQL (Microsoft Learn)

On this site: KQL cheat sheet for join kinds in the Kusto language, Power Query basics for merges, and the skills outline.