Scripts & Serendipity

Understanding NULL in SQL: Common Pitfalls and How to Handle Them NULL is one of the first things every SQL learner meets and one of the last things fully understood. It doesn't mean zero, it doesn't mean empty string, and it doesn't behave like a normal value in comparisons — which is exactly where the bugs come from. 📘 What NULL Actually Means NULL represents the absence of a value — "unknown," not "empty." A NULL phone number isn't the same as an empty string phone number; one means "we don't have this data," the other means "we know it's blank." 🧪 Why NULL = NULL Doesn't Work SELECT * FROM customers WHERE phone = NULL; -- ❌ Always returns zero rows SELECT * FROM customers WHERE phone IS NULL; -- ✅ Correct NULL isn't equal to anything — not even another NULL. In three-valued SQL logic, any comparison involving NULL evaluates to "unknown," not true or false, so the row is excluded either wa...

How to Write a SQL UPSERT (Compared Across MySQL, PostgreSQL, and BigQuery) An "UPSERT" — update if a row exists, insert if it doesn't — is one of those operations every database supports, but with completely different syntax. If you've moved between databases, you've probably had to relearn this every time. Here's all three side by side. 📘 Why UPSERT Matters Without UPSERT, handling "insert or update" logic means checking for existence first, then branching — two round trips and a race condition risk under concurrent writes. UPSERT does it atomically, in one statement. 🧪 MySQL: INSERT ... ON DUPLICATE KEY UPDATE INSERT INTO inventory (product_id, quantity) VALUES (101, 50) ON DUPLICATE KEY UPDATE quantity = quantity + 50; Requires a UNIQUE or PRIMARY KEY constraint on product_id for MySQL to know what counts as a "duplicate." 🧪 PostgreSQL: INSERT ... ON CONFLICT DO UPDATE INSERT INTO inventory (product_id, quantity) VALUES ...

SQL Window Functions Explained: ROW_NUMBER, RANK, LAG, LEAD Window functions let you calculate something "across" a set of related rows without collapsing them into one — they're one of the most powerful tools in SQL once they click, and one of the most confusing before they do. 📘 The Shared Pattern Every window function follows the same basic shape: function_name() OVER ( PARTITION BY column -- optional: split into groups ORDER BY column -- optional: define row order within each group ) 🧪 ROW_NUMBER — a unique sequential number SELECT employee_name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept FROM employees; Every row gets a unique number, 1, 2, 3... within its department, ordered by salary descending. No ties — even if two people have identical salaries, one gets 1 and the other gets 2, arbitrarily. 🧪 RANK vs DENSE_RANK — handling ties SELECT employee_name, salary, RANK()...

Common Table Expressions (CTEs) in SQL — A Beginner's Guide A Common Table Expression (CTE) is a named, temporary result set you can reference within a single query. Think of it as giving a subquery a name and using that name like a regular table — it makes complex queries dramatically more readable. 📘 Basic Syntax WITH high_earners AS ( SELECT employee_id, name, salary FROM employees WHERE salary > 80000 ) SELECT * FROM high_earners WHERE name LIKE 'A%'; The WITH ... AS (...) block defines the CTE, and it can then be queried just like a table in the main SELECT below it. 🎯 Why Use a CTE Instead of a Subquery? Compare the same logic without a CTE: -- Without CTE — nested and harder to read SELECT * FROM ( SELECT employee_id, name, salary FROM employees WHERE salary > 80000 ) high_earners WHERE name LIKE 'A%'; Functionally identical, but for anything more complex than this toy example, nested subqueries get hard to follow fast. CTEs le...

SQL WHERE vs HAVING: When to Use Each WHERE and HAVING both filter rows, which is exactly why they're so easy to mix up. The difference comes down to one thing: when in the query's execution each one runs. 📘 The Core Rule WHERE filters rows before grouping happens. HAVING filters groups after GROUP BY has already collapsed the rows. This is why HAVING can filter on aggregate functions (like COUNT or SUM) and WHERE cannot. 🧪 Why WHERE Can't Filter on Aggregates -- ❌ This throws an error in most databases SELECT department, COUNT(*) AS employee_count FROM employees WHERE COUNT(*) > 5 GROUP BY department; This fails because WHERE runs before COUNT(*) has even been calculated — at the point WHERE executes, there's no "count" yet to filter on. 🧪 The Correct Version, Using HAVING SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5; This works because HAVING runs after grouping and aggregatio...

GROUP BY vs PARTITION BY in SQL — What's the Difference? Both GROUP BY and PARTITION BY organize rows into groups — but they do fundamentally different things with those groups. Mixing them up is one of the most common points of confusion once you move past basic SQL. 📘 The Core Difference GROUP BY collapses rows: multiple input rows become one output row per group. You lose the individual row-level detail. PARTITION BY (used with window functions) keeps every row, but lets you calculate something "within" each group without collapsing anything. 🧪 GROUP BY in Action SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; Output: one row per department. If you had 50 employees across 5 departments, you get back exactly 5 rows. 🧪 PARTITION BY in Action SELECT employee_name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary FROM employees; Output: all 50 employee rows, unchanged — but each ro...

SQL JOIN Types Explained (INNER, LEFT, RIGHT, FULL) with Examples JOINs are the single most-used SQL concept and also the most commonly half-understood one. Once you can picture what each JOIN type actually keeps and discards, the syntax stops being something to memorize and starts being something you can reason through. 📘 The Setup Imagine two tables: -- customers customer_id | name 1 | Alice 2 | Bob 3 | Carol -- orders order_id | customer_id | amount 101 | 1 | 50 102 | 1 | 20 103 | 4 | 75 -- note: customer_id 4 doesn't exist in customers Carol (id 3) has no orders. Order 103 belongs to customer_id 4, who doesn't exist in the customers table. This mismatch is exactly what makes JOIN behavior visible. 🧪 INNER JOIN — only matching rows SELECT c.name, o.order_id, o.amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id; Result: Alice's two orders only. Carol is exc...