DEV Community: Baldwin Apps

SQL Pattern Series #4: The Moving Sum Pattern

Baldwin Apps — Tue, 09 Jun 2026 14:35:00 +0000

Seeing what is happening over the last N rows

SQL Pattern Series #4 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What a moving sum is
How window functions make moving calculations possible
Why moving sums are useful for trend analysis
When to use ROWS BETWEEN

Most reports start with simple totals.

SELECT SUM(SalesAmount)
FROM SalesData;

But eventually a different question appears:

What has happened over the last 30 days?

or:

What is the rolling total over the last 30 transactions?

That is where the Moving Sum Pattern becomes useful.

The Problem

Looking at individual rows often hides the bigger picture.

For example:

Date	Sales
Jan 1	100
Jan 2	120
Jan 3	95
Jan 4	140

Individual values move up and down.

Sometimes what matters is the cumulative behavior over a recent window.

Questions such as:

What were sales during the last 30 days?
How many errors occurred during the last 100 events?
What is the rolling total of customer purchases?

require more context than a single row provides.

The Moving Sum Pattern

A moving sum calculates the total of the current row plus a specified number of previous rows.

For example:

SUM(value) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING
         AND CURRENT ROW
)

Conceptually this creates a window.

As each row is processed, the window moves forward.

Rows enter the window.

Older rows leave the window.

The calculation updates automatically.

Example

SELECT
    date,
    value,
    SUM(value) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING
             AND CURRENT ROW
    ) AS moving_sum
FROM SalesData;

This produces a rolling 30-row total.

Each result includes:

the current row
the previous 29 rows

The window moves forward one row at a time.

Why This Pattern Matters

Moving sums help reveal trends that individual rows often hide.

Instead of asking:

What happened today?

you begin asking:

What has happened recently?

This makes the pattern useful for:

sales reporting
financial analysis
monitoring systems
operational dashboards
customer activity tracking

The result is often smoother and easier to interpret than raw values alone.

Window Functions Change Everything

Many SQL developers first encounter moving sums when learning window functions.

Without window functions, moving calculations often require:

self joins
correlated subqueries
temporary tables

Window functions provide a cleaner solution.

The Moving Sum Pattern is one of the most common examples.

A Note on Rows vs Dates

One important detail:

ROWS BETWEEN 29 PRECEDING

means:

Previous 29 rows

not:

Previous 29 days

Those are different concepts.

If dates contain gaps, weekends, or missing records, row counts and date ranges may not align.

Always verify that the window matches the business requirement.

When I Reach for This Pattern

I typically use the Moving Sum Pattern when:

I need rolling totals
I want to smooth noisy data
I need trend visibility
The analysis depends on recent activity

Examples include:

rolling sales totals
rolling transaction counts
rolling inventory movement
rolling customer activity

Key Takeaway

Individual rows show events.

Moving sums show context.

The Moving Sum Pattern helps answer:

What has happened recently?

instead of:

What happened on this row?

That shift often makes trends easier to see and decisions easier to make.

SQL Pattern Series

This article is part of the SQL Pattern Series, a collection of practical SQL patterns that help developers recognize common problem-solving approaches found in reporting, analytics, and application development.

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #3: The Missing Data Pattern

Baldwin Apps — Sat, 06 Jun 2026 14:39:00 +0000

Finding rows that disappear because of the wrong JOIN

SQL Pattern Series #3 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

Why rows sometimes disappear unexpectedly
The difference between INNER JOIN and LEFT JOIN
How to identify missing related data
When to use the Missing Data Pattern

Most SQL developers eventually encounter a report that looks wrong.

The numbers seem too low.

Rows appear to be missing.

And yet the query runs successfully.

Often the issue is not the data.

It's the JOIN.

The Question Behind the Query

Many SQL queries involve combining information from multiple tables.

The key question becomes:

Do I only want matching rows?

or:

Do I want all rows from one table, even when no match exists?

Those questions lead to very different results.

Matching Rows Only

INNER JOIN

Returns only rows that exist in both tables.

Keep All Rows

LEFT JOIN

Returns all rows from the left table, even when no matching row exists in the right table.

Rows without a match return NULL values for the right-side columns.

The Missing Data Pattern

The Missing Data Pattern appears when data seems to have vanished from a result set.

In many cases, nothing is actually missing.

The query is simply filtering rows through an INNER JOIN.

Imagine a list of orders.

Some orders have matching customers.

Some do not.

An INNER JOIN only returns the orders with matching customer records.

The remaining rows disappear from the result.

A LEFT JOIN reveals those unmatched rows.

The image above summarizes the core idea:

INNER JOIN returns only matching rows
LEFT JOIN keeps all rows from the left table
Missing matches appear as NULL
Sometimes the missing rows are the most important rows

Example Using INNER JOIN

SELECT o.OrderID,
       c.CustomerName
FROM Orders o
INNER JOIN Customers c
    ON o.CustomerID = c.ID;

This query returns only orders that have a matching customer record.

Any order without a match is excluded.

Conceptually, SQL asks:

Show me only the rows that exist in both tables.

Example Using LEFT JOIN

SELECT o.OrderID,
       c.CustomerName
FROM Orders o
LEFT JOIN Customers c
    ON o.CustomerID = c.ID;

This query keeps every order.

If a matching customer does not exist, the customer columns return NULL.

Conceptually, SQL asks:

Show me every order, whether a matching customer exists or not.

Why This Pattern Matters

Many reporting problems are not caused by bad data.

They are caused by accidental filtering.

Developers often write an INNER JOIN without realizing that unmatched rows are being removed.

The Missing Data Pattern encourages you to ask:

What rows am I losing?
Should unmatched rows be visible?
Is the absence of a match important information?

Sometimes the missing rows are exactly what you're trying to find.

A Note on Investigation

When a report seems incomplete, one of the first things I check is the JOIN type.

A quick comparison between:

INNER JOIN

and

LEFT JOIN

can often reveal whether rows are being filtered unintentionally.

This is especially useful when troubleshooting:

incomplete reports
missing customers
orphaned records
failed imports
data quality issues

When I Reach for This Pattern

I often think about the Missing Data Pattern when I need to find:

customers without orders
orders without customers
employees without managers
products without sales
records missing related data

In these situations, the absence of a relationship is often the most valuable information.

Key Takeaway

When data seems to disappear, ask:

Is the data actually missing?

or:

Is my JOIN hiding it?

Sometimes changing:

INNER JOIN

to:

LEFT JOIN

completely changes what you discover.

And sometimes the missing rows are the story.

SQL Pattern Series

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #2: The Match Pattern

Baldwin Apps — Tue, 02 Jun 2026 16:31:00 +0000

Choosing between exact matches and pattern matches

SQL Pattern Series #2 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

The difference between exact matching and pattern matching
When to use =
When to use LIKE
Why a small operator change can change the meaning of a query

Most SQL developers run into this distinction eventually.

= and LIKE solve different problems.

= checks for an exact value.

LIKE checks whether a value fits a pattern.

That difference matters when the question changes from:

Is this exactly Admin?

to:

Does this start with Admin?

The image above summarizes the core idea:

= asks whether a value is exactly equal.
LIKE asks whether a value matches a pattern.
Both are useful, but they answer different questions.

Exact Match Example

Use = when the value must match exactly.

SELECT *
FROM Users
WHERE Role = 'Admin';

This query asks:

Is the role exactly Admin?

It will match:

Admin

But it will not match:

AdminAssistant
Administrator
SuperAdmin

That is the point.

The = operator is precise.

Pattern Match

Use LIKE when the value may vary but still follows a recognizable pattern.

SELECT *
FROM Users
WHERE Role LIKE 'Admin%';

This query asks:

Does the role start with Admin?

It may match values like:

Admin
AdminAssistant
Administrator

The % wildcard means:

Any number of characters may appear here.

So Admin% means:

Starts with Admin.

The Match Pattern

The Match Pattern is about choosing the right kind of comparison for the question being asked.

Sometimes you need precision.

Sometimes you need flexibility.

The pattern is simple:

Use = for exact matches
Use LIKE for pattern-based matches

But the effect can be significant.

A query that is too strict may miss valid rows.

A query that is too broad may return rows you did not intend to include.

Why This Pattern Matters

This distinction shows up in many real-world queries.

For example:

Find one exact status
Find names that start with a prefix
Find emails from a specific domain
Find product codes with a shared pattern
Find log messages containing a phrase

Each of these questions requires a different kind of match.

Common Examples

Exact status match

SELECT *
FROM Orders
WHERE Status = 'Pending';

This asks for orders where the status is exactly Pending.

Prefix match

SELECT *
FROM Users
WHERE Username LIKE 'test%';

This asks for usernames that start with test.

Contains match

SELECT *
FROM Products
WHERE ProductName LIKE '%keyboard%';

This asks for product names that contain keyboard anywhere in the value.

Suffix match

SELECT *
FROM Customers
WHERE Email LIKE '%@example.com';

This asks for email addresses that end with @example.com.

A Note on Performance

Pattern matching can be powerful, but it can also affect performance.

For example:

WHERE Username LIKE 'Admin%'

may be easier for a database to optimize because the pattern has a fixed beginning.

But this:

WHERE Username LIKE '%Admin%'

can be more expensive because the database may need to search inside the value rather than starting from the beginning.

The exact behavior depends on:

the database system
indexes
collation settings
data size
query plan

As always, validate assumptions with real execution plans and real workloads.

When I Reach for This Pattern

I typically use = when:

I need one exact value
The column contains controlled values
The comparison should be strict
The query should not include partial matches

I typically use LIKE when:

I need prefix matching
I need suffix matching
I need contains matching
The data is text-based and variable
I am intentionally searching for a pattern

Key Takeaway

Small operator difference.

Very different behavior.

When writing SQL, ask:

Am I looking for an exact value?

or:

Am I looking for a pattern?

That question determines whether the query should use = or LIKE.

The better you understand the match you need, the easier it becomes to write queries that return the right rows.

SQL Pattern Series

This article is part of the SQL Patterns series, a collection of practical SQL patterns that help developers recognize common problem-solving approaches found in reporting, analytics, and application development.

SQL Pattern Series #1: The Presence Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #1: The Presence Pattern

Baldwin Apps — Sat, 30 May 2026 03:26:55 +0000

Thinking in terms of existence instead of lists

SQL Pattern Series #1 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

When EXISTS and IN solve the same problem
The difference between set membership and existence
Why the underlying mental model matters
When I typically reach for EXISTS

Most SQL developers write a query like this at some point:

SELECT c.CustomerID,
       c.CustomerName
FROM Customers c
WHERE c.CustomerID IN (
    SELECT o.CustomerID
    FROM Orders o
);

And it works.

But sometimes it isn't the best way to think about the problem.

The Question Behind the Query

Many SQL problems can be framed in two different ways.

Set Membership

Is this value in a set?

WHERE CustomerID IN (...)

Existence

Does at least one matching row exist?

WHERE EXISTS (...)

Both approaches often return the same result.

But they represent different mental models.

The Presence Pattern

The Presence Pattern is useful when you do not actually care about the values being returned from a related table.

You only care whether a matching row exists.

For example:

Customers who have placed an order
Users who have logged in
Employees assigned to a project
Products that have sales

In these cases, the question is often:

Does a related row exist?

rather than:

What values are contained in this list?

The image above summarizes the core idea:

EXISTS asks whether at least one matching row exists
IN checks whether a value belongs to a set
Both often return the same result
The difference is usually the mental model

Example Using EXISTS

SELECT c.CustomerID,
       c.CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

The subquery is correlated to the outer query.

Conceptually, SQL asks:

For this customer, does at least one matching order exist?

As soon as the answer becomes true, the condition is satisfied.

Why This Pattern Matters

Many SQL developers initially learn syntax.

Over time, they discover that query writing is really about choosing the right mental model.

The Presence Pattern encourages you to think in terms of:

existence
relationships
matching rows

instead of building lists unnecessarily.

That shift often makes queries easier to reason about.

A Note on Performance

Modern database optimizers are extremely sophisticated.

In many systems, IN and EXISTS may be rewritten into similar execution plans.

As a result:

The same result does not necessarily mean the same execution strategy.

And the same syntax does not necessarily mean different performance.

Always validate assumptions with execution plans and real-world testing.

The value of this pattern is primarily conceptual:

existence vs. membership
relationship vs. list
presence vs. values

When I Reach for This Pattern

I typically consider EXISTS when:

I only need to know whether related data exists
The subquery may return many rows
The relationship itself is the focus of the query
I want the query to communicate intent clearly

Examples include:

customers with orders
users with activity
products with transactions
accounts with associated records

Key Takeaway

Many SQL problems become easier when you ask:

Do I need the values?

or:

Do I simply need to know whether they exist?

That small distinction changes how you think about the query.

And sometimes, changing how you think about the problem is more important than changing the syntax.

SQL Pattern Series

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

One Practical SQL Trigger Example You Can Actually Use

Baldwin Apps — Fri, 29 May 2026 12:35:32 +0000

One UPDATE statement. One trigger. One automatic audit record — no extra code required.

Triggers are one of those SQL features that can seem a little mysterious at first, but the basic idea is simple: a trigger lets the database automatically do something when data changes.

In this example, we'll use a trigger to create a basic audit trail.

Whenever a row in an Employees table is updated, the trigger will record the old and new salary values in a separate audit table.

Note: The primary examples use SQL Server / T-SQL syntax. Where behavior or syntax differs meaningfully across SQL Server, MySQL, PostgreSQL, and Oracle, that's called out directly.

What Is a Trigger?

A trigger is a block of SQL that runs automatically in response to an event such as an INSERT, UPDATE, or DELETE.

That means the database can react to changes without relying on someone to remember to run extra code manually.

Step 1: Create the Main Table

-- SQL Server / MySQL / PostgreSQL
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    Salary DECIMAL(10,2)
);

This syntax works across SQL Server, MySQL, and PostgreSQL. In Oracle, VARCHAR is supported but VARCHAR2 is the preferred type:

-- Oracle
CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FullName VARCHAR2(100),
    Salary NUMBER(10,2)
);

Step 2: Insert Some Sample Data

INSERT INTO Employees (EmployeeID, FullName, Salary)
VALUES
(1, 'Alice Johnson', 60000.00),
(2, 'Brian Smith', 72000.00);

Multi-row INSERT with VALUES works across all four databases.

Step 3: Create the Audit Table

-- SQL Server
CREATE TABLE EmployeeSalaryAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedAt DATETIME DEFAULT GETDATE()
);

This table will store each salary change, including the old value, the new value, and when the change happened.

Auto-incrementing primary keys and current timestamp functions vary across databases:

-- MySQL
CREATE TABLE EmployeeSalaryAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedAt DATETIME DEFAULT NOW()
);

-- PostgreSQL
CREATE TABLE EmployeeSalaryAudit (
    AuditID SERIAL PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedAt TIMESTAMP DEFAULT NOW()
);

-- Oracle
CREATE TABLE EmployeeSalaryAudit (
    AuditID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    EmployeeID NUMBER,
    OldSalary NUMBER(10,2),
    NewSalary NUMBER(10,2),
    ChangedAt DATE DEFAULT SYSDATE
);

Key differences:

SQL Server uses IDENTITY(1,1) and GETDATE()
MySQL uses AUTO_INCREMENT and NOW()
PostgreSQL uses SERIAL (or GENERATED ALWAYS AS IDENTITY in newer versions) and NOW()
Oracle uses GENERATED ALWAYS AS IDENTITY and SYSDATE ## Step 4: Create the Trigger

This is where the databases diverge most significantly.

-- SQL Server
CREATE TRIGGER trg_LogSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
    SELECT
        d.EmployeeID,
        d.Salary AS OldSalary,
        i.Salary AS NewSalary
    FROM deleted d
    INNER JOIN inserted i
        ON d.EmployeeID = i.EmployeeID
    WHERE d.Salary <> i.Salary
       OR (d.Salary IS NULL AND i.Salary IS NOT NULL)
       OR (d.Salary IS NOT NULL AND i.Salary IS NULL);
END;

In SQL Server, triggers can access two special logical tables:

deleted contains the old version of updated rows
inserted contains the new version of updated rows The trigger joins those tables on EmployeeID and logs only rows where the salary actually changed.

A note on the WHERE clause: a simple d.Salary <> i.Salary won't catch changes involving NULL values — in SQL, NULL <> anything evaluates to unknown, not true. The additional IS NULL conditions ensure those changes are captured correctly. This applies to all four database versions below.

-- MySQL
CREATE TRIGGER trg_LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.Salary <> NEW.Salary
       OR (OLD.Salary IS NULL AND NEW.Salary IS NOT NULL)
       OR (OLD.Salary IS NOT NULL AND NEW.Salary IS NULL)
    THEN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
        VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary);
    END IF;
END;

-- PostgreSQL (requires a trigger function)
-- Note: EXECUTE FUNCTION requires PostgreSQL 11+
-- Use EXECUTE PROCEDURE for older versions
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.Salary <> NEW.Salary
       OR (OLD.Salary IS NULL AND NEW.Salary IS NOT NULL)
       OR (OLD.Salary IS NOT NULL AND NEW.Salary IS NULL)
    THEN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
        VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

-- Oracle
CREATE OR REPLACE TRIGGER trg_LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF :OLD.Salary <> :NEW.Salary
       OR (:OLD.Salary IS NULL AND :NEW.Salary IS NOT NULL)
       OR (:OLD.Salary IS NOT NULL AND :NEW.Salary IS NULL)
    THEN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
        VALUES (:OLD.EmployeeID, :OLD.Salary, :NEW.Salary);
    END IF;
END;

Key differences across databases:

SQL Server uses deleted and inserted virtual tables and handles multiple rows as sets in a single statement-level trigger
MySQL uses OLD and NEW and requires FOR EACH ROW — MySQL only supports row-level triggers
PostgreSQL separates the trigger logic into a reusable trigger function, then attaches it to the table — OLD and NEW are used inside the function. EXECUTE FUNCTION requires PostgreSQL 11 or later; use EXECUTE PROCEDURE for older versions
Oracle uses :OLD and :NEW (note the colon prefix) and requires FOR EACH ROW for row-level triggers ## A Note on Statement-Level vs Row-Level Triggers

This is one of the most important distinctions to understand — and one that can produce unexpected results if you're not aware of it.

A row-level trigger fires once for every row affected by the operation. Update 50 rows and it fires 50 times.

A statement-level trigger fires once per SQL statement, regardless of how many rows were affected.

SQL Server triggers are statement-level by default, which is why the SQL Server example joins deleted and inserted as sets rather than referencing single OLD/NEW values
MySQL only supports row-level triggers
PostgreSQL supports both — FOR EACH ROW for row-level, FOR EACH STATEMENT for statement-level
Oracle supports both — FOR EACH ROW for row-level, statement-level is the default without it This matters in practice: if you run a bulk update affecting 1,000 rows, a row-level trigger fires 1,000 times. For audit logging on large tables, that's worth thinking about before you deploy.

Step 5: Test It

UPDATE Employees
SET Salary = 65000.00
WHERE EmployeeID = 1;

Step 6: Check the Audit Table

SELECT *
FROM EmployeeSalaryAudit;

You should see a row showing that Employee 1's salary changed from 60000.00 to 65000.00.

That happened automatically because of the trigger.

Why This Is Useful

This kind of trigger can be useful when you want the database to help maintain a history of important changes.

Instead of depending on application code to remember to log the update, the database handles it consistently — regardless of which application, tool, or user made the change.

A Quick Caution

Triggers are powerful, but they should be used carefully.

Because they run automatically behind the scenes, they can make database behavior harder to understand if too much hidden logic accumulates. A developer looking at the UPDATE statement alone has no visible indication that something else is happening as a result.

For a simple audit trail like this, though, a trigger can be a practical and readable solution — and audit logging is one of the most widely accepted use cases for triggers across all four major databases.

Final Thought

This is a small example, but it demonstrates the central idea: when data changes, the database can respond automatically.

The syntax looks different depending on which database you're working with — but the concept is the same everywhere. A trigger watches for an event, and when that event happens, it acts.

That's what makes triggers useful — and why they're worth understanding.

SQL Bubble Pop covers the query logic and filtering patterns that form the building blocks of trigger design — through daily challenges and active recall that make these concepts click faster.

Download SQL Bubble Pop free on the App Store. Search "SQL Bubble Pop."

TL;DR

A trigger runs automatically when data changes — no manual intervention needed
SQL Server uses deleted and inserted virtual tables; other databases use OLD and NEW; Oracle adds a colon prefix (:OLD and :NEW)
PostgreSQL requires a separate trigger function before attaching the trigger to a table (EXECUTE FUNCTION requires PostgreSQL 11+)
Row-level triggers fire once per affected row; statement-level triggers fire once per SQL statement — know which you're using
Audit logging is one of the most widely accepted and practical use cases for triggers
Use triggers carefully — hidden logic can make database behavior harder to reason about at scale

Before SQL, We Had to Tell Computers Everything. Then One Idea Changed That Forever.

Baldwin Apps — Thu, 28 May 2026 19:37:32 +0000

Here's something most SQL tutorials skip entirely:

SQL wasn't just a new syntax. It was a new way of thinking.

Before SQL existed, most systems were built around procedural programming. You didn't describe a result — you described every step to get there. Fetch the data. Check a condition. Process row by row. Loop again. Handle the edge cases. Store the result. Repeat.

It worked. But it was exhausting. And fragile. And hard to scale.

Then SQL introduced an idea that sounds almost too simple:

Tell the system what you want — not how to do it.

The Old Way Was Expensive

Procedural data access put all the cognitive weight on the developer. You were responsible for:

Deciding how to traverse the data
Managing every loop and condition manually
Anticipating performance edge cases
Writing code that was tightly coupled to how the data was stored

Change the storage structure, and your code broke. Add more records, and your performance tanked. It scaled poorly in both directions — complexity and volume.

The database engine knew things about the data that you didn't. But you weren't allowed to use that knowledge. You had to do it yourself, row by row, the hard way.

One Query. A Radically Different Contract.

SELECT *
FROM customers
WHERE balance > 1000;

That's it. No loops. No manual traversal. No step-by-step instruction manual.

You described the outcome. The database figured out the path.

That's not a small thing. That's a complete inversion of the relationship between the developer and the system.

With SQL, you stopped being the engine and started being the director. You tell the system what matters. The query optimizer decides how to get there — what index to use, what join order makes sense, what execution plan fits the data.

More power. Less code. More room to focus on what the problem actually is.

Why This Still Matters in 2025

You might think this is ancient history — SQL is nearly 50 years old at this point. But the shift it introduced isn't historical trivia.

Every modern analytics dashboard you've ever seen is built on it. Every reporting pipeline. Every business intelligence tool. Every time a developer writes a query to answer a business question, they're standing on that original insight: describe the result, let the system do the work.

The declarative mindset also spread far beyond SQL. It shaped how we think about CSS (describe the layout, not the pixel math), how React works (describe the UI state, not the DOM manipulation), how infrastructure-as-code tools like Terraform operate.

SQL wasn't just influential for databases. It was a template for how humans could collaborate with computers more effectively.

The Mindset Shift Is the Point

This is why I always tell new learners: SQL isn't really about memorizing syntax. The syntax is a few keywords. You can look those up.

The deeper skill is learning to think in outcomes.

What result do you need? What does the data look like? What shape do you want the answer in? Let the engine worry about the how.

That's a trainable skill. And once it clicks, it changes the way you approach problems — not just in databases, but everywhere you interact with systems.

You Can Practice This Right Now

If you've been avoiding SQL because it feels technical or gatekept, I want to offer a different frame:

SQL is one of the most human-readable languages we have. You're not writing machine code. You're describing what you want in something close to plain English. "Select all customers where their balance is greater than 1000" — that is the query, almost word for word.

The barrier is lower than it looks. The mental model is the thing worth building.

Start there. The syntax will follow.

Tiye Baldwin-Anderson is the founder of Baldwin Apps LLC
and the creator of SQL Bubble Pop, a gamified iOS app for learning SQL through play. She writes about SQL, tech literacy, and building things that make learning feel like a game.

COALESCE in SQL: the “Don’t Let NULL Win” Function You’ll Use Everywhere

Baldwin Apps — Wed, 18 Feb 2026 11:07:46 +0000

Cross-posted from Medium. If NULL has ever “blanked out” a result you know should exist, COALESCE is the fix you’ll use forever.

If you’ve ever looked at a query result and thought:

“Why is this blank? I know there’s data here…”

…it was probably NULL doing what NULL does.

Some databases will happily let a missing value sneak through your calculations, concatenations, and reports — then you’re left debugging a spreadsheet like it’s a crime scene.

Enter COALESCE: one of the simplest functions in SQL, and also one of the most useful.

What COALESCE does (in plain English)

COALESCE returns the first non-NULL value from a list of expressions.

Think of it like a fallback chain:

If this value is NULL, try the next one
If that’s NULL too, try the next one
Keep going…
If they’re all NULL, you get NULL

The basic pattern

COALESCE(value1, value2, value3, ...)

✅ Returns the first non-NULL value.

Quick example: “Give me a name… any name”

Let’s say you have a users table and people can have a display name, a full name, or (worst case) nothing.

SELECT
  COALESCE(display_name, full_name, 'Anonymous') AS shown_name
FROM users;

This reads like:

Use display_name if it exists
Otherwise use full_name
Otherwise label them 'Anonymous'

If you’re building apps, dashboards, or any user-facing output, this is basically required.

COALESCE vs NULL: why it matters

NULL isn’t “empty” or “zero” or “blank”.

NULL means: unknown / missing / not provided.

And SQL treats NULL like a wildcard ghost.

NULL breaks math

SELECT 10 + NULL;   -- result: NULL

So if you’re doing totals, averages, costs, scoring, etc., one NULL can nuke your result.

Fix it with COALESCE

SELECT 10 + COALESCE(NULL, 0);  -- result: 10

Real-world use cases you’ll hit constantly

1) Safe totals (avoid NULL turning everything into NULL)

SELECT
  order_id,
  COALESCE(subtotal, 0)
  + COALESCE(tax, 0)
  + COALESCE(shipping, 0) AS total
FROM orders;

This is reporting-grade SQL: stable, predictable, and less likely to make you question reality at 1 a.m.

2) Text concatenation without “NULL poisoning”

Many SQL dialects will produce NULL if you concatenate text with NULL.

SELECT
  COALESCE(first_name, '')
  || ' '
  || COALESCE(last_name, '') AS full_name
FROM customers;

(Depending on your database you may use CONCAT() instead of ||, but the COALESCE idea stays the same.)

3) “Prefer this column, fallback to that one”

Classic: billing address might be missing so you fall back to shipping:

SELECT
  customer_id,
  COALESCE(billing_state, shipping_state) AS state
FROM customer_addresses;

Common gotcha: type mismatches

COALESCE returns a single value, and SQL tries to make all the arguments compatible.

So avoid mixing types like:

COALESCE(age, 'unknown')   -- might error or auto-cast in weird ways

Better:

keep it numeric:

COALESCE(age, 0)

or keep it text:

COALESCE(CAST(age AS VARCHAR), 'unknown')

(Casting syntax varies by database.)

Your turn

Where are NULLs messing with your life right now?

totals?
string output?
missing user fields?
inconsistent imports?

Drop your scenario in the comments — if it’s a common pain point, I’ll write the next article as a fix.

🫧 SQL Bubble Pop — a quick, game-style SQL trainer designed to help you build instinct for joins, views, CTEs, and query logic in short daily sessions.