Databricks SQL Editor: SELECT, WHERE, CASE and NULLs (Part 1 of 3)

Most SQL surprises come from nulls: arithmetic with a null returns null, = NULL is never true, and a WHERE clause quietly drops rows whose condition is unknown. This post follows Part 1 of the video series on the Databricks SQL Editor in Free Edition. It covers SELECT, WHERE, ORDER BY, CASE and null handling on eight fictional orders, all on a serverless SQL warehouse, without creating any tables.

<iframe width="560" height="315" src="https://www.youtube.com/embed/wDC8HrQTWms" title="Databricks SQL Tutorial: SELECT, WHERE, CASE &amp; NULLs in the SQL Editor (Part 1 of 3)" frameborder="0" allowfullscreen></iframe>

Watch on YouTube

The SQL file and cheat sheet are in the Part 1 folder on GitHub.

Open the SQL Editor on the serverless warehouse

In the sidebar, click SQL Editor. At the top right, the compute selector shows the one SQL warehouse Free Edition gives you, the Serverless Starter Warehouse. Select it and set the catalog to workspace. Serverless means you do not size or start a cluster yourself. Databricks manages the compute and shuts it down when it is idle.

Run a quick smoke test first:

SELECT 1 AS sql_editor_ready;

One row with the value 1 means the warehouse is connected. The editor runs all statements by default. To run one at a time, clear Run all statements, click inside a statement or highlight it, and press Ctrl+Enter (Cmd+Enter on a Mac).

Build the sample data inline

Instead of creating a table, each statement builds the orders with a VALUES list inside a common table expression named orders. That keeps Part 1 read-only.

WITH orders AS (
  SELECT * FROM VALUES
    (101, 'Ana',  'NORTH', 'Books', 2,    CAST(10.00 AS DECIMAL(10,2)), DATE '2026-10-01'),
    ...
    (105, NULL,   'EAST',  'Books', 1,    CAST(12.00 AS DECIMAL(10,2)), DATE '2026-10-03'),
    (106, 'Fay',  'SOUTH', 'Toys',  NULL, CAST(20.00 AS DECIMAL(10,2)), DATE '2026-10-04'),
    (107, 'Gus',  'WEST',  'Books', 4,    CAST(NULL  AS DECIMAL(10,2)), DATE '2026-10-04'),
    (108, 'Hana', 'NORTH', 'Toys',  1,    CAST(30.00 AS DECIMAL(10,2)), DATE '2026-10-05')
  AS t(order_id, customer, region, category, quantity, unit_price, order_date)
)
SELECT *, CAST(quantity * unit_price AS DECIMAL(12,2)) AS order_total
FROM orders
ORDER BY order_id;

Three gaps are there on purpose: 105 has no customer, 106 has no quantity and 107 has no unit price. In the result, order_total is null for 106 and 107, not zero. Order 105 still totals 12.00, because a missing name does not affect the math.

Two null rules to remember

• In arithmetic, null in means null out.
• In aggregates, SUM and COUNT(column) skip nulls, while COUNT(*) counts every row.

That difference is why the lesson defines valid rows explicitly.

Valid rows: the reconciliation number

A second CTE keeps only rows with both a quantity and a price:

valid AS (
  SELECT order_id, customer, region, category, quantity, unit_price, order_date,
         CAST(quantity * unit_price AS DECIMAL(12,2)) AS order_total
  FROM orders
  WHERE quantity IS NOT NULL AND unit_price IS NOT NULL
)
SELECT COUNT(*) AS valid_orders, SUM(order_total) AS valid_revenue
FROM valid;

Use IS NOT NULL, never = NULL, because a comparison with null is never true. The result is 6 valid orders and 142.50 in revenue. Every later total in the series should add back up to those numbers.

SELECT, WHERE, IN and ORDER BY

SELECT order_id, customer, region, category, order_total
FROM valid
WHERE category = 'Books' AND region IN ('NORTH', 'WEST')
ORDER BY order_id;

Two rows come back: 101 (Ana, NORTH, 20.00) and 103 (Cy, WEST, 25.50), for 45.50. Order 107 is also Books in WEST, but it was already removed for its missing price. AND is evaluated before OR, so the IN list also avoids a common parentheses mistake.

CASE stops at the first match

CASE
  WHEN order_total >= 30 THEN 'premium'
  WHEN order_total >= 25 THEN 'high'
  ELSE 'standard'
END AS value_band
...
WHERE order_total >= 25
ORDER BY order_total DESC, order_id DESC;

CASE checks each WHEN from top to bottom and stops at the first match, so order matters. Put the 25 check first and a 30.00 order would be labeled high. The result: 108 and 104 are premium at 30.00, then 103 and 102 are high at 25.50 and 25.00. The order_id tie-breaker puts 108 before 104. Without a tie-breaker, rows with equal totals can come back in any order.

Measure nulls before you clean

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN customer IS NULL THEN 1 ELSE 0 END) AS null_customer,
  SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END) AS null_quantity,
  SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) AS null_unit_price
FROM orders;

The result is 8 orders with exactly one null in each column. If these counts change, you know the source changed before your totals do.

COALESCE is for display, not business values

SELECT order_id, COALESCE(customer, '(missing)') AS customer_display, region, category
FROM orders
WHERE customer IS NULL OR quantity IS NULL OR unit_price IS NULL
ORDER BY order_id;

COALESCE returns its first non-null argument, so order 105 shows (missing). Use it for readable labels. Do not replace a missing price with zero, because that quietly changes revenue.

Practice challenge

Using the same eight orders, count the rows where customer != 'Ana'. One order belongs to Ana, so seven looks obvious. The answer is 6. Order 105 has a null customer, so NULL != 'Ana' is unknown, and WHERE keeps only rows where the condition is true. To get 7, add OR customer IS NULL, or use IS DISTINCT FROM, which treats null as a normal value in the comparison.

Exam traps from Part 1

• = NULL is never true. Use IS NULL and IS NOT NULL.
• Arithmetic with null gives null, but SUM and COUNT(column) skip nulls.
• CASE stops at the first match.
• AND is evaluated before OR.
• For interactive SQL, a serverless SQL warehouse is the usual compute choice.

Next, Part 2 groups, joins and ranks the same orders.