Query Design

Database 08 Sep 2026 8 min read

Get Changed Rows Directly with SQLite RETURNING

A database write often creates information the application immediately needs. An INSERT may generate an ID and timestamp. An UPDATE may calculate a new counter. A DELETE may need to return enough data for an audit event. The familiar approach is to write first and query afterward, but SQLite has a cleaner option for many of these cases: RETURNING. Here’s the idea: INSERT INTO jobs (name) VALUES ('resize-images') RETURNING id, name, created_at; The write and the values I care about stay in one SQL statement. That is convenient, but the more interesting part is understanding exactly what RETURNING promises—and what it does not.

Database SQLite

Database 04 Sep 2026 12 min read

Use EXISTS and NOT EXISTS for Relationship Checks in SQL

Many SQL queries do not actually need data from a related table. They only need to answer a yes-or-no question: Does this customer have at least one paid order? Is this project missing an active owner? Does any inventory row satisfy this product requirement? A common first attempt is to join the tables and then remove duplicates. That can work, but it makes the query produce more rows than the problem requires and then asks a later operation to repair the result.

Database SQL

Database 03 Sep 2026 13 min read

Use SQL Window Functions Without Losing Row Detail

Many SQL problems ask for a calculation across several rows while still returning each original row. For example, you may need to show every order together with the customer’s running spend, rank products inside each category, or compare today’s measurement with the previous one. A regular aggregate such as SUM() or AVG() can calculate across rows, but a GROUP BY query usually collapses those rows into one result row per group.

Database SQL

Database 03 Sep 2026 9 min read

Understand SQL NULL with Three-Valued Logic

NULL is one of the easiest SQL concepts to recognize and one of the easiest to reason about incorrectly. The problem starts when NULL is treated as if it were an ordinary value such as 0, an empty string, or the word "unknown". It is none of those. In SQL, NULL represents the absence of a known value, and comparisons involving that absence often produce a third logical result: UNKNOWN.

Database SQL