DEV Community: Sukriti Chatterjee

5 SQL Semantics That Trip Developers Up (And How to Fix Them)

Sukriti Chatterjee — Sat, 08 Aug 2026 04:30:00 +0000

Most developers learn SQL by writing basic SELECT statements until the red squiggle goes away. But SQL isn't a standard procedural language like Python, TypeScript, or Go—it is a declarative language based on relational algebra.

Because of this fundamental difference, it is alarmingly easy to write a query that looks logically sound, executes without throwing a single error, but silently produces completely wrong data or tanks your production database performance.

Here is a breakdown of 5 counter-intuitive SQL semantics that trip developers up every single day, along with how the database engine actually processes them.

1. `NULL = NULL` is NOT True (Three-Valued Logic)

In almost every traditional programming language, equality is binary and reflexive: x == x evaluates to TRUE.

In SQL, this statement returns 0 rows:

-- ❌ WRONG: Always returns an empty set
SELECT * FROM users WHERE middle_name = NULL;

What's Actually Happening?

SQL does not operate on standard two-valued boolean logic (TRUE / FALSE). It uses Three-Valued Logic: TRUE, FALSE, and UNKNOWN.

In SQL, NULL does not mean zero or an empty string—it represents an unknown or missing value.

If Person A's middle name is unknown (NULL), and Person B's middle name is unknown (NULL), are their middle names equal? The database engine doesn't know! Therefore, NULL = NULL evaluates to UNKNOWN.

When a WHERE clause evaluates a row, it only keeps records that resolve strictly to `TRUE. UNKNOWN` is dropped.

The Fix

Always use explicit null-checking operators:

-- ✅ CORRECT: Explicitly checks for missing values
SELECT * FROM users WHERE middle_name IS NULL;

2. `WHERE` vs `HAVING`: The Filter Order Nobody Explains Right

Junior developers often think WHERE and HAVING are interchangeable filters, with HAVING just being "the one you use with GROUP BY."

Using them interchangeably can cause massive query latency in production:

-- ❌ UNOPTIMIZED: Filtering raw rows INSIDE HAVING
SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING status = 'ACTIVE';

What's Actually Happening?

SQL clauses do not execute top-to-bottom in the order they are written. The logical query execution pipeline runs in this sequence:

FROM
WHERE (Filters raw rows on disk)
GROUP BY (Groups remaining rows)
HAVING (Filters aggregated groups)
SELECT

When you put a non-aggregate filter inside HAVING, you force the database engine to group every single row in disk memory, compute the aggregations, and then throw away the inactive departments.

The Fix

Filter raw records as early as possible with WHERE so your engine groups a much smaller dataset:

-- ✅ OPTIMIZED: Filter FIRST, aggregate SECOND
SELECT department_id, COUNT(*) 
FROM employees 
WHERE status = 'ACTIVE'
GROUP BY department_id;

Golden Rule: Use WHERE to drop raw records before grouping. Reserve HAVING strictly for aggregate function conditions like COUNT() > 5 or SUM(total) > 1000.

3. `JOIN` vs Subquery: Same Result, Wildly Different Performance

You will often hear developers claim that subqueries and JOINs perform identically because modern query optimizers flatten them. While that holds true for simple subqueries, assuming it for correlated subqueries is dangerous.

-- ⚠️ DANGEROUS: Correlated Subquery
SELECT u.id, u.name 
FROM users u 
WHERE u.id IN (
    SELECT o.user_id 
    FROM orders o 
    WHERE o.total > 500
);

What's Actually Happening?

A correlated subquery references columns from the outer query. If the query optimizer fails to flatten it, the database engine executes the inner subquery once for every single row in the outer table.

If your users table has 100,000 rows, that subquery might run 100,000 separate times! This transforms a linear $O(N)$ set-based operation into an $O(N^2)$ nested-loop bottleneck.

The Fix

Refactor correlated subqueries into set-based JOIN operations:

-- ✅ OPTIMIZED: Set-based Hash/Loop Join
SELECT DISTINCT u.id, u.name 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.total > 500;

4. `GROUP BY` vs `DISTINCT`: Not Interchangeable

When developers want unique records, they often choose between DISTINCT and GROUP BY based on personal syntax preference.

-- ❌ HEAVY OVERHEAD: Using aggregation memory for simple deduplication
SELECT user_id FROM orders GROUP BY user_id;

What's Actually Happening?

Under the hood, these two operations tell the query engine to perform completely different tasks:

DISTINCT is a set operation. It tells the engine to sort or hash the final dataset and remove duplicate rows.
GROUP BY is an aggregation pipeline. It allocates memory buckets to prepare for mathematical metrics (SUM, COUNT, AVG).

If you use GROUP BY without using an aggregate function, you are forcing the database engine to allocate bucket memory for calculations you never intend to run.

The Fix

-- ✅ CLEAN & EFFICIENT: Direct unique set extraction
SELECT DISTINCT user_id FROM orders;

Rule of Thumb: If you aren't calculating metrics across grouped records, do not touch GROUP BY. Use DISTINCT.

5. `LEFT JOIN` + `WHERE` = Silently Becomes an `INNER JOIN`

This is the single most common silent bug in data engineering.

Imagine you want a report of all users, including those who have never placed an order:

-- ❌ SILENT BUG: The LEFT JOIN is destroyed by the WHERE clause
SELECT u.id, u.email, o.order_date 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'COMPLETED';

What's Actually Happening?

The LEFT JOIN runs correctly and includes users without orders, padding their order columns with NULL.
Next, the WHERE clause runs to evaluate WHERE o.status = 'COMPLETED'.
For a user with zero orders, o.status is NULL.
The expression NULL = 'COMPLETED' evaluates to UNKNOWN.
As we learned in Topic 1, UNKNOWN rows are filtered out!

By adding a WHERE condition on the right-hand table, you accidentally wiped out all the NULL-padded rows, silently converting your LEFT JOIN into an `INNER JOIN`.

The Fix

Move right-table conditions directly into the ON clause of the join:

-- ✅ CORRECT: Keeps unmatched users while filtering order criteria
SELECT u.id, u.email, o.order_date 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'COMPLETED';

Summary Checklist for Code Reviews

Checking for NULLs? Use IS NULL or IS NOT NULL, never = NULL.
Filtering rows before grouping? Put the condition in WHERE, not HAVING.
Running subqueries in loops? Refactor correlated subqueries to explicit JOINs.
Deduplicating simple rows? Use DISTINCT instead of GROUP BY.
Filtering a LEFT JOIN? Put right-table predicates in the ON clause to preserve outer rows.

Which of these SQL edge cases has tripped you up in production before? Drop your thoughts or worst database horror stories in the comments below! 🚀

[Boost]

Sukriti Chatterjee — Mon, 13 Jul 2026 15:21:06 +0000

Sukriti Chatterjee

Jul 12

You Don’t Need a Smarter Robot—You Need a Better Assembly Line

#ai #machinelearning #api #architecture

4 min read

You Don’t Need a Smarter Robot—You Need a Better Assembly Line

Sukriti Chatterjee — Sun, 12 Jul 2026 18:30:00 +0000

Rethinking Book Recommendations with TBRly

Go to any tech meetup, scroll through your developer timeline, or open your inbox, and you will hear variations of the same hyper-optimistic narrative: “Why build a traditional app anymore? Anyone can just replace an entire software layer with a raw ChatGPT prompt or a customized system instruction.”

It is a seductive assumption. It implies that natural language is the ultimate user interface and that the raw reasoning power of Large Language Models (LLMs) has made deterministic software architecture obsolete.

But if you actually try to live inside that assumption, the reality falls apart instantly. Raw LLMs possess immense cognitive flexibility, but as an interface for consistent, daily operations, they represent a massive step backwards in user experience.

When you ask a raw AI chatbot like ChatGPT to recommend what you should read next, you are hiring a brilliant, hyper-intelligent robot, handing it a chaotic mental pile of 100 books you might want to read, and expecting it to perfectly organise your brain.

The problem is that the robot doesn't know your library, it forgets what you said ten prompts ago, and it answers by dumping a massive, exhausting wall of text back into your lap. You haven't solved your decision paralysis; you’ve just turned it into a reading assignment.

You don't need a smarter robot. You need a better assembly line.

The future of software isn't just wrapping an API key in a text box and calling it a platform. The value is shifting toward Workflow Engineering—building the deterministic scaffolding, state routing, and silent automation that makes AI actually usable at scale.

The Chaos of the Open Prompt: A UX Nightmare

To understand why raw chat loops fail, let's look at the concrete human problem of managing a massive, chaotic backlog of 100+ unread books scattered across physical shelves, Kindle lists, and browser tabs.

Imagine trying to manage and curate this personal library entirely inside a standard, raw chat interface:

The Data Ingestion Wall: Your first task is manual data entry. You have to sit down and type out 100 titles, authors, genres, and purchase dates. If you miss a comma or misspell an ambiguous title, the model might infer the wrong edition.
The Memory Leak: LLMs are bounded by context windows. As you interact with your library over weeks—asking it to prioritize choices, filter by mood, or log read status—your historical states begin to drift. The moment your chat thread gets too long, older entries slide out of active memory, causing the system to quietly lose track of books you input on day one.
The Content Paradigm: An LLM treats your request as a text generation problem. If you ask for a recommendation, it will return a massive wall of markdown prose. It forces you to read through paragraphs of conversational filler just to extract a single title.
Decision Paralysis Redux: Ask a chatbot for a reading recommendation, and it will happily give you a static bulleted list of ten books. You are right back where you started: staring at a list, paralysed by multi-variable choice data.

A raw chat prompt is high-friction, non-deterministic, and structurally exhausting for the end user. It isn’t an application; it’s an open-ended puzzle.

Moving Beyond the Wrapper: Enter Workflow Engineering

True user retention isn’t driven by how powerful your underlying foundational model is. It is driven by operational mechanics—how cleanly your surrounding system architecture abstracts the friction of data entry and structures context routing before the model ever sees it.

When we designed TBRly, we stopped treating book curation as a chat game and started treating it as a structured data pipeline. Here is how the book recommendation assembly line works under the hood:

1. The Frictionless Intake

Instead of forcing users to type out their massive reading list line-by-line, you drop raw data—like a quick photo of your bookshelf or a messy search query—directly onto the conveyor belt.

The pipeline automatically extracts the text, fetches the book covers, hooks into open-source metadata hubs like the Internet Archive's Open Library API, and formats the data into a clean, structured asset array containing covers, genres, and unique identifiers. The user does zero typing; the structural data pipeline handles the logistics.

2. The Quiet Inspector (Context Routing)

When raw strings enter the platform, they don't go straight to an open prompt. Instead, Google AI acts as a quiet supervisor on the assembly line—instantly parsing book titles, cleaning up title typos, generating cross-lingual contextual metadata tags, and applying semantic tags behind the scenes.

Because this data is structured deterministically into low-latency storage layers like Firestore, the system maintains absolute data fidelity over time. There is no context drift, no lost entries, and no memory leaks.

3. The Sorting Engine ("The Arena")

Your books are automatically pushed off the conveyor belt and funneled into The Arena—a custom, deterministic binary-choice orchestration engine.

Instead of generating a long conversational list of recommendations, the engine systematically structures your backlog into rapid-fire, head-to-head tournament matchups. By forcing simple binary decisions (Book A vs. Book B) and tracking the state transitions via an optimized frequency hash map in $O(1)$ lookup time, the system mathematically strips away multi-variable decision paralysis.

The Value Shift in the AI Era

The tech ecosystem is undergoing a massive architectural realization. The novelty of the raw API call is wearing off. Users do not want to become prompt engineers just to organize their reading data, and they do not want to read conversational essays just to pick their next book.

The magic of a modern book app shouldn't be a blank chat box that forces the user to do the structural thinking. It should be an invisible assembly line that handles the messy logistics, leaving the human with nothing left to do but open the first page.

Bypassing Orchestration Frameworks: Building a Lean AI Book Curator Natively on Gemini

Sukriti Chatterjee — Thu, 09 Jul 2026 04:00:00 +0000

Like most developers with a growing stack of unread physical books, I suffer from a bad case of choice paralysis. Your bookshelf looks at you, you look back at it, and you end up scrolling on your phone instead of reading.

To solve this, I built TBRly—a lightweight web application that allows users to snap a photo of their physical bookshelf, instantly parse the titles, and drop them into a "Deathmatch Arena" where a native AI pipeline forces a choice based on your current vibe.

But this isn't a post about marketing a book tool. It’s a post about why I stripped out the abstraction layers and built an AI engine natively.

The Architecture Pitfall: The Framework Trap

When I started mapping out the backend, the default path seemed obvious: grab an AI orchestration framework, chain a few prompts together, and deploy.

But as a software engineer who values performance and predictability, I ran into immediate friction. Standard frameworks add significant middleware overhead, hide the raw payload structures, and make debugging non-deterministic API behaviors a black box.

For a consumer utility application where latency and snappy rendering matter, I didn't want heavy dependencies. I wanted raw, blazing-fast speed and total control over the context window.

So, I built native abstractions directly over the Google GenAI SDK.

Inside the Core Engine

TBRly relies on two critical technical pillars to take a messy physical reality and turn it into an actionable decision:

1. Vision parsing without heavy OCR pipelines

Instead of running an expensive, multi-stage pipeline that extracts raw bounding boxes, matches text fragments, and pipes strings to a separate model, TBRly passes the user’s phone image directly to a multimodal model.

By utilizing strict structured JSON outputs, the model receives the raw pixel buffer and returns a clean, validated array of objects containing only title and author. If the image is blurry, it flags it immediately without wasting downstream compute cycles.

2. The Vibe Prompting Architecture

Standard category matching (e.g., "Sci-Fi" vs "History") is boring. TBRly introduces a feature called Vibe Prompting.

Instead of searching indexed metadata tags, users can input abstract text strings like “Give me a cozy, rainy-day psychological mystery” or “A fast-paced, mind-bending cyber thriller.”

The backend dynamically wraps this intent into an isolated context block, injects the user's parsed bookshelf list, and runs an internal selection matrix natively to output the final matched book and a single, punchy justification string.

The Stack

Frontend: Next.js (optimized edge execution for fast client rendering)
Backend: FastAPI (async routing to handle asynchronous API streaming seamlessly)
AI Layer: Native Gemini integration via Google GenAI SDK (zero third-party wrapper lag)

Lessons from the Trenches

Building in public alone means running into immediate structural hurdles. Here is what this project taught me so far:

Data Gravity is Real: When handling user images and state, keeping the compute payload close to the model endpoint is the difference between a 4-second loading spinner and a sub-second response.
Lean is Resilient: Bypassing massive middleware wrappers forced me to explicitly write my own error-handling logic for rate limits and context safety filters. The result? A highly deterministic codebase that doesn't break when a wrapper package updates its minor version.

The platform is live, 90% free, and currently navigating its early alpha stages. TBRly App

If you are an engineer who loves reading or someone who wants to tear apart my architectural choices, I’d love your feedback. How are you handling native LLM abstractions in your production side-projects? Let's discuss below!

DEV Community: Sukriti Chatterjee

5 SQL Semantics That Trip Developers Up (And How to Fix Them)

1. NULL = NULL is NOT True (Three-Valued Logic)

What's Actually Happening?

The Fix

2. WHERE vs HAVING: The Filter Order Nobody Explains Right

What's Actually Happening?

The Fix

3. JOIN vs Subquery: Same Result, Wildly Different Performance

What's Actually Happening?

The Fix

4. GROUP BY vs DISTINCT: Not Interchangeable

What's Actually Happening?

The Fix

5. LEFT JOIN + WHERE = Silently Becomes an INNER JOIN

What's Actually Happening?

The Fix

Summary Checklist for Code Reviews

[Boost]

You Don’t Need a Smarter Robot—You Need a Better Assembly Line

You Don’t Need a Smarter Robot—You Need a Better Assembly Line

Rethinking Book Recommendations with TBRly

The Chaos of the Open Prompt: A UX Nightmare

Moving Beyond the Wrapper: Enter Workflow Engineering

1. The Frictionless Intake

2. The Quiet Inspector (Context Routing)

3. The Sorting Engine ("The Arena")

The Value Shift in the AI Era

Bypassing Orchestration Frameworks: Building a Lean AI Book Curator Natively on Gemini

The Architecture Pitfall: The Framework Trap

Inside the Core Engine

1. Vision parsing without heavy OCR pipelines

2. The Vibe Prompting Architecture

The Stack

Lessons from the Trenches

1. `NULL = NULL` is NOT True (Three-Valued Logic)

2. `WHERE` vs `HAVING`: The Filter Order Nobody Explains Right

3. `JOIN` vs Subquery: Same Result, Wildly Different Performance

4. `GROUP BY` vs `DISTINCT`: Not Interchangeable

5. `LEFT JOIN` + `WHERE` = Silently Becomes an `INNER JOIN`