05.3 SQL Queries

Phase 5 — SQL in Databricks

This chapter builds the SQL reasoning skills required to write correct, maintainable and production-ready queries in Databricks. The goal is not to memorize syntax. The goal is to understand row grain, logical processing, NULL semantics, cardinality, data movement and result correctness.

---

1. What a SQL query really does

A query transforms source relations into a result set:

Source rows
    ↓
Choose relevant rows
    ↓
Combine datasets
    ↓
Group / calculate
    ↓
Filter calculated groups
    ↓
Project the required columns
    ↓
Sort / limit
    ↓
Result set

Databricks SQL supports SELECT queries together with CTEs, joins, grouping, HAVING, QUALIFY, set operators and other query constructs. See the Databricks SQL language reference.

The most important design question is:

What does one output row represent?

That is the query's grain.

Examples: one row per sales order; one row per customer; one row per customer per month; one row per material; one row per plant and material; one row per flight and day.

If the grain is unclear, the SQL is not ready for production.

2. Basic SELECT

SELECT
    customer_id,
    order_date,
    total_amount
FROM sales.orders;

The SELECT clause defines the columns and expressions returned by the query.

Avoid SELECT * in production interfaces unless every column is intentionally part of the contract. Prefer an explicit projection for stable schemas, smaller scans, safer schema evolution and easier downstream dependency management.

Databricks also supports SELECT * EXCEPT(...) when excluding a small number of columns is intentional. See the SELECT reference.

3. Query anatomy

WITH base AS (
    SELECT ...
)
SELECT ...
FROM ...
JOIN ...
WHERE ...
GROUP BY ...
HAVING ...
QUALIFY ...
ORDER BY ...
LIMIT ...;

Useful logical processing mental model:

CTE
 ↓
FROM
 ↓
JOIN
 ↓
WHERE
 ↓
GROUP BY + aggregation
 ↓
HAVING
 ↓
WINDOW / QUALIFY
 ↓
SELECT
 ↓
DISTINCT
 ↓
ORDER BY
 ↓
LIMIT

Do not confuse written order with logical processing. SQL is declarative, and the optimizer can transform the physical execution plan.

Logical order explains semantics. Physical plans explain performance.

4. FROM, namespaces and aliases

With Unity Catalog, prefer the three-level name:

SELECT order_id, total_amount
FROM sales_prod.commerce.orders;

The pattern is catalog.schema.object.

Explicit aliases make multi-table queries readable:

SELECT
    o.order_id,
    c.customer_name,
    o.total_amount
FROM sales.orders AS o
JOIN sales.customers AS c
    ON o.customer_id = c.customer_id;

For production code, fully qualified names reduce accidental dependency on session context.

5. Expressions and CASE

Expressions produce values:

SELECT
    quantity * unit_price AS gross_amount,
    quantity * unit_price * 0.18 AS tax_amount
FROM sales.order_items;

Common expression categories include arithmetic, comparison, Boolean, conditional, string, date/time, casts, functions and scalar subqueries. See the SQL expression reference.

CASE is a core business-rule construct:

SELECT
    order_id,
    CASE
        WHEN total_amount >= 100000 THEN 'HIGH'
        WHEN total_amount >= 25000 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS order_segment
FROM sales.orders;

6. WHERE and predicate design

WHERE filters rows from the input relation:

SELECT order_id, customer_id, total_amount
FROM sales.orders
WHERE order_status = 'COMPLETED'
  AND order_date >= DATE '2026-01-01';

Aggregate and window expressions do not belong directly in WHERE. Use HAVING or a derived result when filtering calculations.

Prefer predicates that expose the stored column:

WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-02-01'

over wrapping the filtered column in YEAR/MONTH expressions when a direct range expresses the same business rule.

Use parentheses for complex AND/OR logic:

WHERE order_status = 'COMPLETED'
  AND (sales_channel = 'ONLINE' OR sales_channel = 'MOBILE');

See the WHERE reference.

7. NULL semantics

NULL represents missing or unknown information. This is wrong:

WHERE customer_id = NULL

Use:

WHERE customer_id IS NULL

SQL uses three-valued logic:

TRUE
FALSE
UNKNOWN

Therefore a predicate such as customer_status <> 'ACTIVE' does not automatically include NULL values.

Use COALESCE deliberately:

SELECT COALESCE(preferred_name, legal_name, 'Unknown') AS customer_name
FROM customers;

Do not turn NULL into zero automatically in analytical measures. NULL and zero can have different business meanings.

Databricks supports NULL-safe equality with <=>. It evaluates true when both operands are NULL, which can be useful when NULL itself is a comparable state.

See NULL semantics.

8. DISTINCT and duplicate reasoning

SELECT DISTINCT customer_id
FROM sales.orders;

DISTINCT removes duplicate result rows, but it should not be used as a repair mechanism.

If a join unexpectedly produces ten million rows, DISTINCT may hide an incorrect cardinality while adding expensive deduplication.

First determine why the duplicates were created.

9. JOIN fundamentals

Before writing a join, identify:

1. left grain
2. right grain
3. join key
4. expected cardinality
5. NULL behavior
6. unmatched-row behavior

Example:

SELECT
    o.order_id,
    c.customer_name,
    o.total_amount
FROM sales.orders AS o
LEFT JOIN sales.customers AS c
    ON o.customer_id = c.customer_id;

10. Join cardinality

Suppose orders are one row per order and customers are one row per customer. That is normally an N:1 enrichment.

But if customer_addresses has many rows per customer, an order can multiply:

1 order
×
3 matching addresses
=
3 output rows

If the required output is still one row per order, the query is wrong unless that multiplication is intentional.

This is one of the most important SQL debugging skills.

11. Join types

INNER JOIN

Returns matching rows from both sides.

LEFT JOIN

Preserves all rows from the left side. Typical use: enrich a fact while preserving every fact row.

RIGHT JOIN

Equivalent in semantics to reversing the inputs of a LEFT JOIN. Prefer LEFT JOIN when it makes the business flow clearer.

FULL OUTER JOIN

Preserves unmatched rows from both sides and is useful for reconciliation.

SEMI JOIN

Returns left rows that have a match. EXISTS is often a clear expression of this intent.

ANTI JOIN

Returns left rows without a match. NOT EXISTS is often a clear expression of this intent.

Example:

SELECT o.*
FROM sales.orders AS o
WHERE NOT EXISTS (
    SELECT 1
    FROM sales.customers AS c
    WHERE c.customer_id = o.customer_id
);

12. Aggregation and grain

Aggregation changes the grain:

SELECT
    customer_id,
    SUM(total_amount) AS revenue,
    COUNT(*) AS order_count
FROM sales.orders
GROUP BY customer_id;

Input grain: one row per order.

Output grain: one row per customer.

Always state the grain before and after aggregation.

COUNT semantics

COUNT(*) counts rows.

COUNT(customer_id) counts non-NULL customer IDs.

COUNT(DISTINCT customer_id) counts distinct non-NULL customer IDs.

Conditional aggregation

SELECT
    customer_id,
    SUM(total_amount) AS total_revenue,
    SUM(CASE WHEN order_status = 'COMPLETED' THEN total_amount ELSE 0 END) AS completed_revenue
FROM sales.orders
GROUP BY customer_id;

HAVING

SELECT customer_id, SUM(total_amount) AS revenue
FROM sales.orders
GROUP BY customer_id
HAVING SUM(total_amount) > 100000;

WHERE filters rows; HAVING filters groups.

13. CTEs

CTEs make complex queries easier to reason about:

WITH completed_orders AS (
    SELECT order_id, customer_id, order_date, total_amount
    FROM sales.orders
    WHERE order_status = 'COMPLETED'
),
customer_revenue AS (
    SELECT customer_id, SUM(total_amount) AS revenue
    FROM completed_orders
    GROUP BY customer_id
)
SELECT customer_id, revenue
FROM customer_revenue
WHERE revenue >= 100000;

A CTE is primarily a query-composition tool. Do not assume it means materialized storage; the optimizer can transform the query.

See Common table expressions.

14. Subqueries

Scalar subquery:

SELECT
    order_id,
    total_amount,
    (SELECT AVG(total_amount) FROM sales.orders) AS overall_average
FROM sales.orders;

EXISTS:

SELECT o.*
FROM sales.orders AS o
WHERE EXISTS (
    SELECT 1
    FROM sales.customers AS c
    WHERE c.customer_id = o.customer_id
);

Choose subquery, EXISTS, IN or a join according to the business semantics and resulting plan.

15. Window functions

Window functions calculate across related rows without collapsing the row grain:

SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS order_rank
FROM sales.orders;

GROUP BY changes many rows into fewer rows. A window function retains the original rows and adds analytical attributes.

16. QUALIFY

QUALIFY filters results of window functions.

SELECT o.*
FROM sales.orders AS o
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC, order_id
) = 1;

This is useful for latest-record logic, deduplication, top-N per group and SCD preparation.

See the QUALIFY reference.

ROW_NUMBER vs RANK vs DENSE_RANK

• ROW_NUMBER assigns a unique sequence.
• RANK leaves gaps after ties.
• DENSE_RANK does not leave gaps.

Always define deterministic tie-breakers when the selected row must be stable.

17. ORDER BY and LIMIT

ORDER BY globally sorts the result:

SELECT customer_id, revenue
FROM customer_revenue
ORDER BY revenue DESC;

Global sorting can be expensive. Use it when ordering is part of the output contract.

LIMIT restricts output rows:

SELECT *
FROM sales.orders
ORDER BY order_date DESC
LIMIT 100;

For deterministic results, combine LIMIT with an explicit order.

18. Set operators

SELECT customer_id FROM online_customers
UNION
SELECT customer_id FROM store_customers;

UNION removes duplicates. UNION ALL preserves them and is normally preferable when duplicate preservation is intentional.

Databricks also supports INTERSECT and EXCEPT. See Set operators.

19. Dates and timestamps

Prefer typed literals:

DATE '2026-11-01'
TIMESTAMP '2026-11-01 10:30:00'

Useful functions include current_date(), current_timestamp(), date_add(), date_sub(), datediff(), date_trunc(), year() and month().

For enterprise systems, distinguish business date, event timestamp, ingestion timestamp and processing timestamp.

For consecutive time windows, prefer half-open ranges:

WHERE event_ts >= TIMESTAMP '2026-01-01 00:00:00'
  AND event_ts <  TIMESTAMP '2026-02-01 00:00:00'

Document timezone assumptions. Do not silently convert timestamps merely because a report is consumed in another region.

20. Strings and enterprise identifiers

Common functions include TRIM, UPPER, LOWER, SUBSTRING and REGEXP_REPLACE.

Be careful with SAP-style identifiers. A value such as 0000123456 can be a business key. Casting it to an integer destroys leading zeros.

Prefer string types for identifiers unless arithmetic is genuinely required.

21. Type casting and financial values

Use explicit casts when the business contract requires a type:

CAST(quantity AS DECIMAL(18,3))
CAST(order_date AS DATE)

For financial measures, choose precision and scale deliberately. Avoid casual floating-point conversions when exact decimal semantics are required.

22. Complete enterprise example

Requirement: find the top two completed orders for each customer in India during October 2026 and show total completed revenue.

WITH completed_orders AS (
    SELECT order_id, customer_id, order_date, total_amount
    FROM sales_prod.commerce.orders
    WHERE order_status = 'COMPLETED'
      AND country_code = 'IN'
      AND order_date >= DATE '2026-10-01'
      AND order_date <  DATE '2026-11-01'
),
customer_revenue AS (
    SELECT customer_id, SUM(total_amount) AS completed_revenue
    FROM completed_orders
    GROUP BY customer_id
),
ranked_orders AS (
    SELECT
        order_id, customer_id, order_date, total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY total_amount DESC, order_date DESC, order_id
        ) AS rn
    FROM completed_orders
)
SELECT
    r.customer_id,
    r.order_id,
    r.order_date,
    r.total_amount,
    cr.completed_revenue
FROM ranked_orders AS r
JOIN customer_revenue AS cr
    ON r.customer_id = cr.customer_id
WHERE r.rn <= 2
ORDER BY cr.completed_revenue DESC, r.customer_id, r.rn;

Notice the deliberate design: filter first, constrain the reusable dataset, aggregate separately, rank separately, join on a known key, use deterministic tie-breakers, and sort only at the final output boundary.

23. Query correctness checklist

Grain

• What does one output row represent?
• Does every join preserve that grain?
• Does every GROUP BY intentionally change it?

Filters

• Are row filters applied at the correct stage?
• Are NULLs handled deliberately?
• Are time boundaries correct?

Joins

• What is the cardinality?
• Can the join multiply rows?
• Are unmatched rows expected?

Measures

• Are NULLs meaningful?
• Is COUNT(*) or COUNT(column) correct?
• Is DISTINCT actually required?

Windows

• Is the partition correct?
• Is the ordering deterministic?
• Are ties handled intentionally?

Output

• Are column names stable?
• Are data types correct?
• Is ORDER BY required?

24. EXPLAIN and query plans

Do not optimize SQL by intuition alone.

EXPLAIN
SELECT
    customer_id,
    SUM(total_amount)
FROM sales.orders
WHERE order_status = 'COMPLETED'
GROUP BY customer_id;

Use DESCRIBE QUERY when you want the output metadata of a query:

DESCRIBE QUERY
SELECT customer_id, SUM(total_amount)
FROM sales.orders
GROUP BY customer_id;

See DESCRIBE QUERY.

Use plans to answer: was the filter pushed down, is there a large shuffle, which join strategy was selected, is the scan larger than expected, and where is the expensive operator?

25. Performance mindset

Once correctness is proven:

Read less
   ↓
Move less
   ↓
Compute less
   ↓
Reuse useful work
   ↓
Measure

Examples:

• project only required columns
• filter before large joins when semantics allow
• avoid repeated expensive expressions
• use appropriate governed tables or reusable results
• inspect Query Profile and execution plans

26. Common anti-patterns

SELECT *

Unstable contracts and unnecessary columns.

DISTINCT as a repair tool

Can hide duplicate-producing joins.

Functions around filter columns

Can reduce optimization opportunities.

Joining before filtering

Can increase rows and data movement.

Ignoring grain

Produces wrong metrics without syntax errors.

Treating NULL as zero

Changes business meaning.

Non-deterministic ranking

Can make top-N selection unstable.

27. Hands-on lab — customer order analytics

Use four logical datasets:

customers
orders
order_items
products

Task 1 — Basic filter

Return completed orders from the last 30 days.

Task 2 — Customer aggregation

Produce customer_id, order_count, total_revenue and average_order_value.

Task 3 — Join

Add customer attributes.

Task 4 — Data quality

Find orders without customers, NULL customer IDs and duplicate order IDs.

Task 5 — Latest order

Use ROW_NUMBER and QUALIFY.

Task 6 — Top three products per category

Use a window function.

Task 7 — Reconciliation

Use FULL OUTER JOIN to compare two customer reference datasets.

Task 8 — Performance

Run EXPLAIN and document scan, join, aggregation, sort and shuffle behavior.

28. Architect challenge — procurement analytics

Design SQL for a procurement Gold dataset.

Source grain:
one purchase order item

Required output:
one row per supplier + company code + fiscal month

Measures:
PO count
PO item count
ordered quantity
ordered value
average item value
late delivery count

Business rules:

• cancelled items excluded
• deleted items excluded
• supplier master enrichment required
• currency conversion required
• fiscal calendar differs from calendar month
• supplier may have missing master data

Document:

1. input grain
2. join cardinalities
3. filter order
4. NULL strategy
5. aggregation grain
6. currency logic
7. fiscal calendar logic
8. duplicate handling
9. output contract
10. performance risks

This is the level of reasoning expected from an analytics architect.

29. Troubleshooting matrix

Symptom	First question
Revenue is too high	Did a join multiply rows?
Some customers disappear	Was an INNER JOIN used accidentally?
NULL customers disappear	Was equality used instead of IS NULL?
Latest row changes between runs	Is window ordering deterministic?
Query scans too much data	Are filters and columns selective?
Aggregation is slow	What is the input cardinality and shuffle?
Dashboard is slow	Is the query queued or executing slowly?
Duplicate rows appear	What is the actual join cardinality?
Dates are shifted	What timezone and timestamp semantics are being used?
Financial totals differ	Are decimal precision and duplicate joins correct?

30. Interview questions

Beginner

WHERE vs HAVING? WHERE filters rows; HAVING filters groups after aggregation.

What is NULL? A missing or unknown value with three-valued SQL semantics.

What does GROUP BY do? It creates groups and changes the result grain for aggregation.

Intermediate

Why can a LEFT JOIN increase row count? Because the right side can contain multiple matches for a left key.

COUNT(*) vs COUNT(column)? COUNT(*) counts rows; COUNT(column) counts non-NULL values.

Why use a CTE? To compose complex SQL into named logical steps and improve readability.

Advanced

When use QUALIFY? To filter based on window-function results such as latest-record or top-N logic.

Why is DISTINCT dangerous? It can hide incorrect join cardinality and add expensive deduplication.

Why does logical SQL order matter? It determines semantics even when the optimizer chooses a different physical plan.

Architect

How do you review enterprise SQL? Start with grain, cardinality, NULL semantics, business rules and output contract; then inspect physical-plan evidence.

How do you prevent incorrect metrics? Define grain explicitly, validate join cardinality, reconcile totals and add data-quality assertions.

31. SQL architect mental model

BUSINESS QUESTION
      ↓
OUTPUT GRAIN
      ↓
SOURCE GRAIN
      ↓
FILTERS
      ↓
JOIN CARDINALITY
      ↓
AGGREGATION
      ↓
WINDOW LOGIC
      ↓
NULL / TYPE SEMANTICS
      ↓
OUTPUT CONTRACT
      ↓
PHYSICAL PLAN
      ↓
PERFORMANCE EVIDENCE

The best SQL developer is not the person who knows the most functions.

It is the person who can explain:

why every row exists in the result.

32. Current official references

• Databricks SQL language reference
• SELECT
• Query syntax
• WHERE
• SQL expressions
• NULL semantics
• CTEs
• QUALIFY
• Set operators
• DESCRIBE QUERY

---

Next

05.4 SQL Views & CTE Design

We will move from individual queries to reusable SQL assets: views, temporary views, materialized views, semantic-layer patterns, dependency design, governance, testing and production patterns.