DEV Community: Real Dev

Why SQL NOT IN returns no rows when the column has NULL

Real Dev — Tue, 27 Oct 2026 03:13:22 +0000

You write a query to drop a few blocked customers. The blocked list has two real ids and one NULL. You expect everyone else. You get nothing.

That is the NOT IN plus NULL trap. It shows up in interviews and in production reports. Here it is on a small SQLite dataset, with every result taken from a real run on Python 3.13's sqlite3.

The tiny dataset

Five customers. One of them has no city yet.

SELECT id, name, city FROM customers ORDER BY id;

1 | Asha | Pune
2 | Ravi | Delhi
3 | Meera | NULL
4 | Kabir | Pune
5 | Neha | Mumbai

A blocked list. Two real ids, and one NULL (a bad import, a left join that leaked, a nullable column you forgot about).

SELECT customer_id FROM blocked;

2
4
NULL

The query that returns nothing

You want customers who are not blocked:

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM blocked)
ORDER BY id;

(no rows)

No rows. Asha, Meera, and Neha should have come back. They did not.

The same thing happens with a literal list that contains NULL:

SELECT id, name
FROM customers
WHERE id NOT IN (2, 4, NULL)
ORDER BY id;

(no rows)

Still nothing. So this is not a subquery quirk. It is how NOT IN works once NULL is in the list.

What NOT IN really means

x NOT IN (a, b, c) is the same as:

x <> a AND x <> b AND x <> c

SQL comparisons against NULL are not true and not false. They are unknown. AND needs every part to be true. One unknown makes the whole thing unknown. WHERE only keeps rows where the condition is true. Unknown rows are dropped.

You can probe that with simple expressions. SQLite prints 0 for false, 1 for true, and NULL for unknown:

SELECT
  5 = 2,
  5 = NULL,
  5 <> 2,
  5 <> NULL,
  (5 <> 2) AND (5 <> NULL);

5 = 2 -> 0
5 = NULL -> NULL
5 <> 2 -> 1
5 <> NULL -> NULL
(5 <> 2) AND (5 <> NULL) -> NULL

So for Neha (id 5):

5 <> 2 is true
5 <> 4 is true
5 <> NULL is unknown

true AND true AND unknown is unknown. Neha is out. The same unknown hits every other id. The whole result set vanishes.

Three ways that work

1. Drop NULLs from the list

If you still want NOT IN, keep only known ids in the subquery:

SELECT id, name
FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM blocked WHERE customer_id IS NOT NULL
)
ORDER BY id;

1 | Asha
3 | Meera
5 | Neha

Asha, Meera, Neha. That matches what you meant.

2. Prefer NOT EXISTS

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b
  WHERE b.customer_id = c.id
)
ORDER BY c.id;

1 | Asha
3 | Meera
5 | Neha

Same three names. A NULL in blocked.customer_id never equals c.id, so it does not match, and the customer stays. This is usually the safer pattern for "rows in A with no match in B".

3. LEFT JOIN, then keep the misses

SELECT c.id, c.name
FROM customers c
LEFT JOIN blocked b ON b.customer_id = c.id
WHERE b.customer_id IS NULL
ORDER BY c.id;

1 | Asha
3 | Meera
5 | Neha

Again the same three. The join finds no partner for Asha, Meera, and Neha. Filtering on b.customer_id IS NULL keeps those misses. (A NULL sitting alone in blocked does not join to anyone, so it does not remove extra rows.)

The cousin trap: `= NULL`

The same three-valued idea breaks a different beginner query. Meera has a NULL city. This looks like it should find her:

SELECT id, name, city
FROM customers
WHERE city = NULL
ORDER BY id;

(no rows)

Nothing. city = NULL is unknown for every row, including Meera. Use IS NULL:

SELECT id, name, city
FROM customers
WHERE city IS NULL
ORDER BY id;

3 | Meera | NULL

What to say in an interview

If someone asks why NOT IN returned an empty set, say:

NOT IN expands to a chain of <> checks joined by AND.
Any compare with NULL is unknown, not false.
WHERE drops unknown.
One NULL in the list is enough to drop every candidate row.
Fix with WHERE col IS NOT NULL inside the subquery, or switch to NOT EXISTS / LEFT JOIN.

Then show a three-row table with one NULL in the list, like the demo above. That usually settles it.

A habit that saves you later

When you write NOT IN against a nullable column, either filter the NULLs or don't use NOT IN. Treat a nullable subquery column as a bug waiting for one bad import. The query that "worked in staging" can go quiet the day NULL shows up.

I work on Schoolabe, a free coding practice site. Short SQL drills like this live in the SQL learning section.

Python's mutable default argument bug, run one call at a time

Real Dev — Tue, 20 Oct 2026 03:48:15 +0000

This function looks fine. It adds a tag to a list and returns the list. If you don't pass a list, it starts a new empty one. At least that's what it seems to do.

def add_tag(tag, tags=[]):
    tags.append(tag)
    return tags

print(add_tag("python"))
print(add_tag("sql"))
print(add_tag("dsa", []))
print(add_tag("git"))

['python']
['python', 'sql']
['dsa']
['python', 'sql', 'git']

The first call is fine. The second call returns ['python', 'sql'], even though we never passed a list. The third call passed its own list, so it got a clean result. Then the fourth call picked up where the second one left off.

So the "new empty list" is not new. It's the same list every time you skip the argument.

The default is built once

Python evaluates a default value when it runs the def line. It does not do it again on each call. The result gets stored on the function object, in __defaults__, and every call that skips the argument gets that one stored object.

You can look at it directly.

def add_tag(tag, tags=[]):
    tags.append(tag)
    return tags

print(add_tag.__defaults__)
first = add_tag("python")
second = add_tag("sql")
print(add_tag.__defaults__)
print(first is second)
print(first is add_tag.__defaults__[0])

([],)
(['python', 'sql'],)
True
True

Before any call, the stored default is an empty list. After two calls it holds both tags. first and second are the same object, and that object is the one sitting in __defaults__.

If you want proof of when the default gets built, put a print inside the code that builds it.

def make_default():
    print("  building the default list")
    return []

print("before def")

def add_tag(tag, tags=make_default()):
    tags.append(tag)
    return tags

print("after def")
add_tag("a")
add_tag("b")
print("after two calls")

before def
  building the default list
after def
after two calls

"building the default list" prints once, between "before def" and "after def". The two calls don't print it at all. They reuse what was built when the function was defined.

This is also why the bug hides in tests. If each test calls the function once, every test passes. It shows up when the same process calls it twice, which is what a web server or a long script does all day.

The same bug in a class

Classes hit this a lot, because __init__ is just a function with defaults.

class Cart:
    def __init__(self, items=[]):
        self.items = items

    def add(self, item):
        self.items.append(item)

asha = Cart()
ravi = Cart()
asha.add("notebook")
print("asha:", asha.items)
print("ravi:", ravi.items)
print(asha.items is ravi.items)

asha: ['notebook']
ravi: ['notebook']
True

Ravi never added anything, but his cart has a notebook. Both carts point at the one list that was built when __init__ was defined. In a real app, that's one user seeing another user's data.

The fix is None

Use None as the default and build the list inside the function. The body runs on every call, so each call gets its own list.

def add_tag(tag, tags=None):
    if tags is None:
        tags = []
    tags.append(tag)
    return tags

print(add_tag("python"))
print(add_tag("sql"))
mine = ["dsa"]
print(add_tag("git", mine))
print(mine)

['python']
['sql']
['dsa', 'git']
['dsa', 'git']

Two calls with no list now give two separate results. And when you do pass a list, the function still adds to it, which is what a caller who passes a list usually wants.

Use if tags is None, not if not tags. The short version looks the same but treats an empty list you passed in as "no list".

def keep_count(tags=None):
    tags = tags or []
    tags.append("x")
    return tags

shared = []
keep_count(shared)
keep_count(shared)
print(shared)

[]

The caller passed shared and expected two "x" values in it. It's still empty. An empty list is falsy, so tags or [] threw it away and made a fresh one each time. The appends went into lists nobody kept.

Dataclasses refuse to do it

If you write the class with @dataclass, Python stops you from making this mistake for a list field.

from dataclasses import dataclass, field

try:
    @dataclass
    class Cart:
        items: list = []
except ValueError as e:
    print("ValueError:", e)

@dataclass
class Cart:
    items: list = field(default_factory=list)

a, b = Cart(), Cart()
a.items.append("pen")
print(a, b)

ValueError: mutable default <class 'list'> for field items is not allowed: use default_factory
Cart(items=['pen']) Cart(items=[])

The error happens when the class is defined, so you find out right away. field(default_factory=list) calls list() for each new instance, and the two carts end up with separate lists. Note that the check is not perfect. On 3.13 it rejected list, dict, set and bytearray defaults, but a mutable class of your own can still slip through if it is hashable.

When people do it on purpose

You will sometimes see a mutable default used as a cache that lives between calls.

def fib(n, memo={}):
    if n < 2:
        return n
    if n not in memo:
        memo[n] = fib(n - 1) + fib(n - 2)
    return memo[n]

print(fib(80))
print(len(fib.__defaults__[0]))

23416728348467685
79

Every result goes into memo, and memo survives between calls. After one call it holds 79 entries.

It works. But a reader can't tell if it was on purpose or an accident, and nobody can clear the cache without reaching into __defaults__. functools.lru_cache does the same job and says what it's doing. If you do keep a trick like this, leave a comment.

What to check in your own code

Search for =[], ={} and =set() in function signatures. Each one is either a bug waiting for a second call or a cache that needs a comment.

The same goes for any default that is an object you can change, like a Cart() or a datetime.now() call. The second one isn't mutable, but it has the same timing problem. It runs once, at definition time, so every call gets the same time.

When you review code, ask one question about each default value. Is this built once, and is that what we want?

I work on Schoolabe, a free coding practice site, and small "predict the output" problems like these are what we put in the logic building section.

A 30-Day DSA Plan for Placements, Built Around Patterns (Not Problem Counts)

Real Dev — Mon, 19 Oct 2026 03:18:59 +0000

A common way to prepare for placement coding rounds is to open a list of hundreds of problems and start at number one. A month later, a new problem still feels like a blank page.

A better approach: learn a small set of patterns well enough that you recognise them in problems you've never seen. This post gives you a 30-day plan built that way, with a working Python template for each core pattern.

Thirty days won't make you an expert. But it can take you from "I don't know where to start" to "I can usually tell what kind of problem this is."

Before Day 1: are you ready for DSA?

DSA assumes you can already write loops, conditionals and functions comfortably. If a palindrome check or "second largest element" still feels slow, spend a week or two on basic logic first. Our logic building track covers conditions, loops, recursion, arrays and strings in that order, and it's a reasonable warm-up.

Pick one language for the whole month. The examples below use Python because it keeps templates short.

How to spend each day

Aim for about 2 hours:

20 min: learn or revise the pattern (read, or trace it by hand on paper).
80 min: solve 2–3 problems that use it. Give yourself about 25 minutes per problem before you look at a hint.
20 min: write a short note for each problem: what the pattern was, and what clue in the question should have pointed you to it.

That last step matters most: you're learning the clues, not collecting solutions.

Week 1 (Days 1–7): Arrays, strings and hashing

Days 1–2: Array basics, reversing, rotating, and stating the time complexity of everything you write.
Days 3–4: Strings: counting characters, anagrams, palindromes.
Days 5–6: Hash maps and sets: the "have I seen this before?" pattern.
Day 7: Revise and solve 3 mixed problems without notes.

Pattern: hash map lookup. The clue is "find a pair or duplicate," where the brute-force answer is two nested loops.

def two_sum(nums, target):
    seen = {}                      # value -> index
    for i, x in enumerate(nums):
        if target - x in seen:
            return [seen[target - x], i]
        seen[x] = i
    return []

assert two_sum([2, 7, 11, 15], 9) == [0, 1]

One pass, O(n) time, O(n) extra space. Say that trade-off out loud in an interview.

Week 2 (Days 8–14): Two pointers, sliding window, prefix sums, binary search

Days 8–9: Two pointers on sorted arrays.
Days 10–11: Sliding window (fixed and variable size).
Day 12: Prefix sums.
Day 13: Binary search, including "binary search on the answer."
Day 14: Revision plus one timed set: 3 problems in 75 minutes.

Pattern: two pointers. The clue is a sorted array, or "pairs, triplets, or in-place partitioning."

def pair_with_sum_sorted(nums, target):
    lo, hi = 0, len(nums) - 1
    while lo < hi:
        s = nums[lo] + nums[hi]
        if s == target:
            return (lo, hi)
        if s < target:
            lo += 1
        else:
            hi -= 1
    return None

assert pair_with_sum_sorted([1, 3, 4, 6, 9], 10) == (0, 4)

Pattern: variable sliding window. The clue is "longest/shortest contiguous subarray or substring such that…"

def longest_unique_substring(s):
    last = {}          # char -> last index seen
    start = best = 0
    for i, ch in enumerate(s):
        if ch in last and last[ch] >= start:
            start = last[ch] + 1     # shrink window past the repeat
        last[ch] = i
        best = max(best, i - start + 1)
    return best

assert longest_unique_substring("abcabcbb") == 3

Pattern: prefix sum + hash map. The clue is "count subarrays whose sum equals k." Unlike a sliding window, it also works with negative numbers.

def count_subarrays_with_sum(nums, k):
    count, running = 0, 0
    freq = {0: 1}                  # prefix sum -> how many times seen
    for x in nums:
        running += x
        count += freq.get(running - k, 0)
        freq[running] = freq.get(running, 0) + 1
    return count

assert count_subarrays_with_sum([1, 1, 1], 2) == 2

Pattern: binary search. Write one version you trust and reuse it every time:

def lower_bound(nums, target):
    """First index i with nums[i] >= target."""
    lo, hi = 0, len(nums)
    while lo < hi:
        mid = (lo + hi) // 2
        if nums[mid] < target:
            lo = mid + 1
        else:
            hi = mid
    return lo

assert lower_bound([1, 2, 4, 4, 7], 4) == 2

Week 3 (Days 15–21): Linked lists, stacks, queues, recursion, trees

Days 15–16: Linked lists: reversal, fast/slow pointers (finding the middle, detecting a cycle).
Day 17: Stacks: valid parentheses, monotonic stack ("next greater element").
Day 18: Recursion and backtracking basics (subsets, permutations).
Days 19–20: Binary trees: DFS (pre/in/post-order) and level-order BFS.
Day 21: Revision.

If pointers feel abstract, watch them move: our DSA visualizers let you step through arrays, linked lists, stacks/queues and sorting in the browser. Use them alongside problems, not instead of them.

Pattern: monotonic stack. The clue is "next greater/smaller element" or "how many days until…"

def next_greater(nums):
    res = [-1] * len(nums)
    stack = []                     # indices with no answer yet
    for i, x in enumerate(nums):
        while stack and nums[stack[-1]] < x:
            res[stack.pop()] = x
        stack.append(i)
    return res

assert next_greater([2, 1, 3, 2, 5]) == [3, 3, 5, 5, -1]

Week 4 (Days 22–30): Graphs, heaps, intro to DP, mocks

Days 22–23: Graphs: adjacency lists, BFS, DFS, counting connected components.
Day 24: Heaps: top-k elements, merging sorted lists (heapq in Python, priority_queue in C++).
Days 25–27: Dynamic programming basics: climbing stairs, house robber, coin change, longest common subsequence.
Days 28–29: Two full mock rounds: 2 problems, 60 minutes, talk out loud as you go.
Day 30: Go back through your notes and redo every problem you marked "needed a hint."

Pattern: BFS for shortest path in an unweighted graph or grid. The clue is "minimum number of steps/moves."

from collections import deque

def shortest_path_grid(grid):
    """Steps from top-left to bottom-right; 0 = open, 1 = wall."""
    n, m = len(grid), len(grid[0])
    if grid[0][0] or grid[n-1][m-1]:
        return -1
    q = deque([(0, 0, 0)])
    seen = {(0, 0)}
    while q:
        r, c, d = q.popleft()
        if (r, c) == (n - 1, m - 1):
            return d
        for dr, dc in ((1, 0), (-1, 0), (0, 1), (0, -1)):
            nr, nc = r + dr, c + dc
            if 0 <= nr < n and 0 <= nc < m and not grid[nr][nc] and (nr, nc) not in seen:
                seen.add((nr, nc))
                q.append((nr, nc, d + 1))
    return -1

assert shortest_path_grid([[0, 0, 0],
                           [1, 1, 0],
                           [0, 0, 0]]) == 4

Pattern: 1-D dynamic programming. The clue is "number of ways" or "min/max cost," where each answer builds on smaller answers.

def coin_change(coins, amount):
    INF = float("inf")
    dp = [0] + [INF] * amount      # dp[a] = fewest coins to make a
    for a in range(1, amount + 1):
        for c in coins:
            if c <= a:
                dp[a] = min(dp[a], dp[a - c] + 1)
    return dp[amount] if dp[amount] != INF else -1

assert coin_change([1, 2, 5], 11) == 3

For DP, write the recurrence in words first, then code it.

A quick "which pattern?" cheat sheet

If the problem says…	Try…
sorted array, pair/triplet	Two pointers
longest/shortest contiguous…	Sliding window
count subarrays with sum k	Prefix sum + hash map
"seen before", duplicates, pairs	Hash map / set
minimum/maximum value that satisfies a condition	Binary search on the answer
next greater/smaller	Monotonic stack
minimum steps, levels	BFS
all combinations/subsets	Backtracking
number of ways, min cost	Dynamic programming
top k, k-th largest	Heap

Mistakes to avoid

Watching solutions instead of struggling. Twenty-five honest minutes teach you more than a ten-minute video.
Skipping complexity analysis. State it before the interviewer asks.
Never practising under time pressure. Do one timed set a week from Week 2.

Save the templates above in one file, run it (every assert should pass silently), and add to it as you learn new patterns. By Day 30 it's your revision sheet.

All the best for placements!

— Schoolabe team

I Added a Fourth Shard in Python and 75% of the Keys Moved

Real Dev — Sat, 17 Oct 2026 14:34:58 +0000

Every sharding answer starts the same way. Hash the key, take it modulo the number of shards, done. It works right up to the day someone says "we need one more shard."

Then it breaks, and it breaks badly: almost every key lands on a different machine than it did yesterday.

You can say that in an interview, but it lands better if you have watched it happen. So I wrote a short Python script, used 100,000 fake keys like user:0 and user:1, and counted. I hash with md5, because Python's built-in hash() for strings changes between runs and would make the numbers useless. The code blocks in this post are one file, in order. Paste them together and it runs.

import bisect
import hashlib
from collections import Counter

def h(s):
    return int(hashlib.md5(s.encode()).hexdigest(), 16) % (2**32)

KEYS = [f"user:{i}" for i in range(100_000)]

def moved_modulo(old_n, new_n):
    moved = sum(1 for k in KEYS if h(k) % old_n != h(k) % new_n)
    return moved / len(KEYS)

print("hash(key) % N, 100,000 keys")
for a, b in [(3, 4), (10, 11), (10, 20), (10, 9)]:
    print(f"  {a} -> {b} shards: {moved_modulo(a, b):.1%} of keys move")

Here is what came out:

hash(key) % N, 100,000 keys
  3 -> 4 shards: 75.0% of keys move
  10 -> 11 shards: 91.0% of keys move
  10 -> 20 shards: 50.0% of keys move
  10 -> 9 shards: 89.9% of keys move

Three to four is the one everyone quotes, and 75% is right. The 10 to 11 line is why this post exists. Going from 10 shards to 11 moves 91% of keys, not 9%. A key only stays put when h % 10 and h % 11 happen to match, which is one hash in eleven.

I noticed because of Schoolabe's own system design lesson, Lesson 9: When Hash Modulo Breaks. As of 17 October 2026 it lists "10 â†’ 11 shards: ~9% relocate" under "Naive resharding cost", and my code gave the mirror image of that. About 9% is the right figure for consistent hashing, so the number seems to have landed on the modulo line. I checked that too, with the Ring code from this post and 11 nodes named S0 to S10. Across 30 arrangements and 100,000 keys, 150 points per node moved 8.0% to 11.5% of keys (mean 9.1%). 100 points gave 6.8% to 11.0%, 200 points gave 8.0% to 11.4%, and one point per node gave 0.0% to 22.9%. The ideal is 1/11, or 9.09%.

The formula above the lesson's list, (1 - 1/oldN), doesn't fit either: it gives 67% for 3 to 4 shards, where the lesson itself says 75%. The rule that does fit is that a key stays only if h % old equals h % new, so the share that stays is min(old, new) divided by lcm(old, new). The comparison table further down has the same swap. It lists adding a node to a 10-node cluster as ~9% for modulo and ~10% for the ring, and removing one as ~10% for modulo. My run gives 91% for 10 to 11 and about 90% for 10 to 9, both for modulo.

Doubling is the mild case, and growth is rarely that tidy. A key survives 10 to 20 when h % 20 lands below 10, which is half the time, so half move.

The ring

A ring answers a different question: who is the next node clockwise from this key? You hash the node names onto a circle, hash each key onto the same circle, and walk clockwise until you hit a node. That node owns the key. In code it is a sorted list and a binary search:

class Ring:
    def __init__(self, nodes, vnodes=1, salt=""):
        self.points = sorted(
            (h(f"{salt}{n}#{v}"), n) for n in nodes for v in range(vnodes)
        )
        self.hashes = [p[0] for p in self.points]

    def lookup(self, key):
        i = bisect.bisect_left(self.hashes, h(key))
        return self.points[i % len(self.points)][1]

def moved_ring(old_nodes, new_nodes, vnodes, salt=""):
    a = Ring(old_nodes, vnodes, salt)
    b = Ring(new_nodes, vnodes, salt)
    return sum(1 for k in KEYS if a.lookup(k) != b.lookup(k)) / len(KEYS)

print()
print("ring, add a 4th node to 3")
for v in (1, 150):
    m = moved_ring("ABC", "ABCD", v)
    print(f"  {v:>3} point(s) per node: {m:.1%} of keys move")

ring, add a 4th node to 3
    1 point(s) per node: 28.0% of keys move
  150 point(s) per node: 25.3% of keys move

Only keys that now sit between D and its counterclockwise neighbour change owner, so about a quarter move, against 75% for modulo. That is the ideal 1/4. These particular node names gave 25.3%; the sweep below shows how much that varies.

def load(nodes, vnodes, salt=""):
    r = Ring(nodes, vnodes, salt)
    c = Counter(r.lookup(k) for k in KEYS)
    return [c[n] for n in nodes]

print()
print("keys per node with 4 nodes")
for v in (1, 150):
    L = load("ABCD", v)
    print(f"  {v:>3} point(s) per node: {L}")

full, minus_c = Ring("ABC", 150), Ring("AB", 150)
moved = [k for k in KEYS if full.lookup(k) != minus_c.lookup(k)]
print()
print("remove C from 3 nodes: moved keys that lived on C:",
      sum(full.lookup(k) == "C" for k in moved), "of", len(moved))

print()
print("30 different arrangements of the same node names, 3 -> 4 nodes")
for v in (1, 150):
    moves, smallest, largest = [], [], []
    for s in range(30):
        salt = f"{s}:"
        moves.append(moved_ring("ABC", "ABCD", v, salt))
        L = load("ABCD", v, salt)
        smallest.append(min(L))
        largest.append(max(L))
    print(f"  {v:>3} point(s): moved {min(moves):.1%} to {max(moves):.1%}, "
          f"smallest node held {min(smallest)}, largest held {max(largest)}")

keys per node with 4 nodes
    1 point(s) per node: [31186, 10531, 30259, 28024]
  150 point(s) per node: [25136, 24419, 25098, 25347]

remove C from 3 nodes: moved keys that lived on C: 33830 of 33830

Removal is the mirror image: with C gone, only keys that lived on C changed owner, all 33830 of them.

The part the diagram hides

Look at the one-point-per-node line in the load output. B holds about a third of what A holds. Four random points on a circle make uneven arcs, and a node's share is its arc. Virtual nodes fix that by giving each machine many points so the arcs average out. With 150 points per node the four counts sit between 24.4k and 25.3k.

Except that is one arrangement. Every number above comes from one fixed set of node names, and md5 decides where the points land. So I ran 30 different arrangements of the same names, by putting a different prefix in front of each name before hashing.

30 different arrangements of the same node names, 3 -> 4 nodes
    1 point(s): moved 0.8% to 71.4%, smallest node held 213, largest held 71383
  150 point(s): moved 21.5% to 28.2%, smallest node held 21055, largest held 29917

With one point per node, the share of keys that move on 3 to 4 nodes ranged from 0.8% to 71.4%, and in one arrangement a node held 213 keys out of 100,000. With 150 points it stayed between 21.5% and 28.2%, and no node fell below 21,055. The 28.0% I showed earlier was luck, and so, a little, was the tidy 25.3%.

For an interview, explaining the plain ring is not enough. Say that one point per node gives uneven load, and that virtual nodes fix it. The next lesson in the same course, Virtual Nodes and CDN Routing, covers that and where the ring shows up in caches and CDNs. Lesson 9 also has a short section on how Redis Cluster uses 16,384 fixed slots instead of a literal ring.

Real keys are skewed and a few hot ones can swamp any node. Nothing here handles replication or a node dying mid-move. The ring tells you where data lives; moving it safely is the harder half, and I haven't tried that.

Change the key count or the 150 and see how far that 21.5% to 28.2% moves.

Written by the Schoolabe team, who build schoolabe.com. Its homepage says tutorials, GATE CS prep, quizzes and practice are free, and that lessons and practice problems run in the browser.

The Python Copy Bug That Looks Fine in Code Review

Real Dev — Sat, 17 Oct 2026 05:06:29 +0000

Here is a bug that is easy to ship. A function takes a dict of default settings and hands each new user a copy. Someone adds a tag to one user. Every future user now has that tag too.

The code looks harmless. Each block below runs on its own, and the output under it is what Python 3.13 printed.

defaults = {"theme": "dark", "tags": ["new"]}
user_a = defaults.copy()
user_a["tags"].append("beta")

print("1. shallow copy, then append")
print("   defaults:", defaults)
print("   user_a  :", user_a)
print("   same inner list?", user_a["tags"] is defaults["tags"])

1. shallow copy, then append
   defaults: {'theme': 'dark', 'tags': ['new', 'beta']}
   user_a  : {'theme': 'dark', 'tags': ['new', 'beta']}
   same inner list? True

.copy() made a new dict, so user_a is not defaults. But the new dict holds the same list object under "tags". You copied the outer box and kept pointing at the same contents. That is what "shallow" means. The rest of the interview answer is where it bites.

When you explain it out loud, draw it: two dict boxes, each with an arrow labelled tags going to the same list box. The drawing usually answers the follow-up questions. The is check in the code is the same picture in text. It asks whether two names point at one object, not whether the contents look equal.

The same thing happens with the other usual ways to copy. list(x), x[:] and x.copy() all build a new outer list and share the inner ones:

nested = [["a"], ["b"]]
clones = [list(nested), nested[:], nested.copy()]
print("inner lists shared with the original:", [c[0] is nested[0] for c in clones])

inner lists shared with the original: [True, True, True]

The bug is not specific to .copy(): it appears wherever you wanted an independent copy and wrote the cheap one.

Schoolabe's Python interview questions page lists "What is the difference between shallow copy and deep copy in Python?" in its sample of the question bank. It describes the category as "copying vs references" gotchas that interviewers probe with short follow-ups. Interviewers rarely stop at a definition; they show five lines and ask what prints.

Follow-ups worth rehearsing

The fix everyone knows is copy.deepcopy, which walks the structure and copies everything it finds:

import copy

defaults = {"theme": "dark", "tags": ["new"]}
user_b = copy.deepcopy(defaults)
user_b["tags"].append("beta")

print("2. deepcopy, then append")
print("   defaults:", defaults)
print("   user_b  :", user_b)

2. deepcopy, then append
   defaults: {'theme': 'dark', 'tags': ['new']}
   user_b  : {'theme': 'dark', 'tags': ['new', 'beta']}

Then there is the trap inside the trap: a shallow copy is not always broken. If you replace a value instead of mutating it, the copy is fine.

defaults = {"theme": "dark", "tags": ["new"]}
user_c = defaults.copy()
user_c["theme"] = "light"
user_c["tags"] = user_c["tags"] + ["beta"]

print("3. shallow copy, but rebind instead of mutate")
print("   defaults:", defaults)
print("   user_c  :", user_c)

3. shallow copy, but rebind instead of mutate
   defaults: {'theme': 'dark', 'tags': ['new']}
   user_c  : {'theme': 'light', 'tags': ['new', 'beta']}

The + builds a brand new list and assigns it to the key in the copy only. append changes the list both dicts share. A small difference in how you write the line decides whether the bug exists, and that is the cause behind advice like "just always use deepcopy".

The list-of-lists cousin needs no call to copy at all:

grid = [[0] * 3] * 3
grid[0][0] = 9
print("repeated rows ->", grid)

grid = [[0] * 3 for _ in range(3)]
grid[0][0] = 9
print("comprehension   ->", grid)

repeated rows -> [[9, 0, 0], [9, 0, 0], [9, 0, 0]]
comprehension   -> [[9, 0, 0], [0, 0, 0], [0, 0, 0]]

Multiplying a list repeats the reference, not the row. Three rows, one list. The comprehension fixes it because [0] * 3 is evaluated again for every row, so each row is a fresh list. If someone answers the grid question correctly but can't say why the comprehension works, keep asking; that is the part that matters. I think this is the better thing to ask in a mock interview, because it tells you more about whether someone understands references than the shallow versus deep definition does. You see it in grid and DP problems, where people build a table of zeros.

Is deepcopy just the answer, then?

Not by reflex. It copes with cycles, which surprised me: a list that contains itself deep-copies fine, and the last line of the next block checks it. So correctness isn't the objection. Cost is.

import copy
import time

rows = [list(range(100)) for _ in range(2000)]
times = []
for _ in range(10):
    start = time.perf_counter()
    copy.deepcopy(rows)
    times.append((time.perf_counter() - start) * 1000)
print(f"deepcopy of 2,000 lists of 100 ints: {min(times):.0f} to {max(times):.0f} ms over 10 runs")

loop = [1]
loop.append(loop)
clone = copy.deepcopy(loop)
print("self-referencing list deep-copied fine:", clone[1] is clone and clone is not loop)

deepcopy of 2,000 lists of 100 ints: 26 to 35 ms over 10 runs
self-referencing list deep-copied fine: True

(Your timings will differ.)

That timing is from one machine, so run it yourself. The deepcopy figure held steady across my runs at roughly 25 to 35 ms. I won't quote a ratio against a shallow copy, because a shallow copy of that list is so quick that the timer mostly measures noise.

For a settings object copied once per signup, who cares. Inside a loop that runs a million times, you will care a great deal. The cheaper habit is to not share mutable things in the first place: build new lists with + or a comprehension, or store tags as a tuple so append isn't even possible. Then the shallow copy is safe and you never need the slow one.

My tests only used lists, dicts and ints. Objects with custom __copy__ or __deepcopy__ methods, or ones holding file handles or locks, behave differently, and I didn't try any of them. If an interviewer pushes in that direction, say you would check the class before assuming.

Write down what you think prints before you run it.

Written by the Schoolabe team, who build schoolabe.com. Its homepage says tutorials, quizzes and practice are free, and that lessons and practice problems run in the browser.

Belady's Anomaly: FIFO vs LRU vs Optimal, Checked in Python

Real Dev — Sat, 17 Oct 2026 04:55:09 +0000

Page replacement questions in GATE CS are mostly counting. You get a reference string, a number of frames and a policy, and you write a row of letters. The idea is easy; the marks go when you lose track of the oldest page late in the paper.

So the useful skill is a method you can run without thinking, plus a way to check yourself on practice problems. I wrote a small Python checker for the second part.

Schoolabe's GATE memory management chapter uses this reference string with 3 frames as its worked example:

1 2 3 4 1 2 5 1 2

It says to count FIFO carefully with a queue, and it doesn't print the answer. Here are the three policies, then the numbers. The code blocks in this post are one file, in order; paste them together and it runs.

import random
from collections import OrderedDict, deque

def fifo(refs, frames):
    q, mem, faults, trace = deque(), set(), 0, []
    for p in refs:
        if p in mem:
            trace.append("hit")
        else:
            faults += 1
            if len(mem) == frames:
                mem.remove(q.popleft())
            q.append(p)
            mem.add(p)
            trace.append("F")
    return faults, trace

def lru(refs, frames):
    mem, faults, trace = OrderedDict(), 0, []
    for p in refs:
        if p in mem:
            mem.move_to_end(p)
            trace.append("hit")
        else:
            faults += 1
            if len(mem) == frames:
                mem.popitem(last=False)
            mem[p] = True
            trace.append("F")
    return faults, trace

def opt(refs, frames):
    mem, faults, trace = [], 0, []
    for i, p in enumerate(refs):
        if p in mem:
            trace.append("hit")
            continue
        faults += 1
        trace.append("F")
        if len(mem) < frames:
            mem.append(p)
            continue
        rest = refs[i + 1:]
        # evict the page whose next use is furthest away (or never)
        victim = max(mem, key=lambda x: rest.index(x) if x in rest else float("inf"))
        mem.remove(victim)
        mem.append(p)
    return faults, trace

lesson = [1, 2, 3, 4, 1, 2, 5, 1, 2]
print("Lesson string with 3 frames:", lesson)
for name, policy in (("FIFO", fifo), ("LRU", lru), ("OPT", opt)):
    n, t = policy(lesson, 3)
    print(f"  {name}: {n} faults   {' '.join(t)}")
print("FIFO on the same string with 4 frames:", fifo(lesson, 4)[0], "faults")

Lesson string with 3 frames: [1, 2, 3, 4, 1, 2, 5, 1, 2]
  FIFO: 7 faults   F F F F F F F hit hit
  LRU: 7 faults   F F F F F F F hit hit
  OPT: 5 faults   F F F F hit hit F hit hit
FIFO on the same string with 4 frames: 7 faults

Check the FIFO row by hand. Pages 1, 2, 3 fill the frames. Page 4 evicts 1, so the next 1 faults and evicts 2, so the next 2 faults and evicts 3. Page 5 evicts 4. Then 1 and 2 are both still in memory and hit. Seven faults.

For the hand trace I write the full frame contents under every reference, not just F or hit. For FIFO I underline the oldest page, and for LRU I rewrite the frames in recency order each time. It is slower, but when you go wrong you can see where.

LRU also gets seven here, so LRU does not always beat FIFO on a given string. Optimal gets five, and that is your floor. It needs the future, so no real system can do it. The chapter calls it the offline lower bound, and if your hand trace beats it, you have miscounted.

The anomaly

The chapter says FIFO can show Belady's anomaly, where more frames give more faults, and calls it a favourite trap. The lesson string above doesn't show it: FIFO with 4 frames also gives 7. The textbook string is the same one extended by three pages, 1 2 3 4 1 2 5 1 2 3 4 5.

belady = [1, 2, 3, 4, 1, 2, 5, 1, 2, 3, 4, 5]
print("Classic string:", belady)
for name, policy in (("FIFO", fifo), ("LRU", lru)):
    for frames in (3, 4):
        print(f"  {name}, {frames} frames: {policy(belady, frames)[0]} faults")

Classic string: [1, 2, 3, 4, 1, 2, 5, 1, 2, 3, 4, 5]
  FIFO, 3 frames: 9 faults
  FIFO, 4 frames: 10 faults
  LRU, 3 frames: 10 faults
  LRU, 4 frames: 8 faults

FIFO goes from 9 faults to 10 when it gets a fourth frame. The anomaly was described by Belady, Nelson and Shedler in "An anomaly in space-time characteristics of certain programs running in a paging machine", Communications of the ACM 12(6), June 1969, pp. 349-353 (doi 10.1145/363011.363155).

LRU on the same string drops from 10 to 8, as you would expect. The reason is the stack property: the pages LRU holds with k frames are always a subset of what it holds with k+1 frames. FIFO keeps pages by arrival order, not by use, so it has no such property.

How common is this? I guessed "not very" and wanted a number, so I drew random strings of 12 references over pages 1 to 5, with a fixed seed so you can reproduce it, and compared 3 frames against 4.

random.seed(2026)
trials, bad_fifo, bad_lru = 1_000_000, 0, 0
for _ in range(trials):
    refs = [random.randint(1, 5) for _ in range(12)]
    if fifo(refs, 4)[0] > fifo(refs, 3)[0]:
        bad_fifo += 1
    if lru(refs, 4)[0] > lru(refs, 3)[0]:
        bad_lru += 1
print(f"{trials:,} random strings, 12 refs over pages 1-5, 3 frames vs 4:")
print(f"  FIFO faulted more with 4 frames: {bad_fifo}")
print(f"  LRU faulted more with 4 frames:  {bad_lru}")

1,000,000 random strings, 12 refs over pages 1-5, 3 frames vs 4:
  FIFO faulted more with 4 frames: 1
  LRU faulted more with 4 frames:  0

One hit in a million. A first attempt with 100,000 strings found none, so I'd have written "never" if I'd stopped there. A single hit can't pin down a rate, though, so I also counted exhaustively with a small C program: of all 5^12 = 244,140,625 strings of 12 references over five pages, FIFO faults more with 4 frames than with 3 in exactly 120 of them. That is about 1 in 2 million. It also explains why the textbook string looks hand-built. Someone had to go looking for it.

Don't hunt for the anomaly in the trace. Look at the wording: of these three policies only FIFO can fault more with more frames, so a question asking which one can has its answer before you count anything. If the policy is LRU or Optimal, the anomaly can't be the point.

The checker is also good for something duller. Take any previous-year question with a string and a frame count, run all three policies, and compare with your hand trace. Schoolabe's GATE hub says its previous-year questions show questions and options only for now, and tells you to cross-check the official IIT answer key yourself. That is fair advice for any source, this one included.

CLOCK, the chapter's fourth policy, is not covered here.

Written by the Schoolabe team, who build schoolabe.com. The GATE CS section lists free subject tutorials, topic quizzes and previous-year papers.

SQL Joins Explained with One Tiny Dataset (Plus 5 Interview Questions)

Real Dev — Thu, 15 Oct 2026 16:13:35 +0000

Most join tutorials show you a Venn diagram and move on. Then an interviewer asks, "Why did your row count go up after the join?" or "Why did your LEFT JOIN behave like an INNER JOIN?", and the diagram doesn't help.

In this post we'll use one small dataset (two tables, ten rows in total) and run every join type on it, so you can see exactly which rows survive. Then we'll work through five interview-style questions that test whether you really understand joins, not just the syntax.

Everything below runs on SQLite 3.39 or newer (and on PostgreSQL). If you're on MySQL, everything works except FULL OUTER JOIN, which MySQL doesn't support. We show the usual workaround below.

The setup

Copy this into any SQL editor:

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  dept_id    INTEGER REFERENCES departments(id),
  manager_id INTEGER REFERENCES employees(id),
  salary     INTEGER
);

INSERT INTO departments VALUES
  (1, 'Engineering', 'Bengaluru'),
  (2, 'Sales',       'Mumbai'),
  (3, 'HR',          'Pune'),
  (4, 'Legal',       'Delhi');

INSERT INTO employees VALUES
  (1, 'Asha',  1, NULL, 150000),
  (2, 'Ravi',  1, 1,     90000),
  (3, 'Priya', 2, 1,     80000),
  (4, 'Karan', 2, 3,     60000),
  (5, 'Neha',  NULL, 1,  70000),
  (6, 'Arjun', 3, 1,     65000);

Two details are there on purpose, because they're what make joins interesting:

Neha has no department (dept_id is NULL).
Legal has no employees.

Keep an eye on those two rows. The difference between join types comes down to what happens to them.

INNER JOIN: only matching rows

SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

name	dept
Asha	Engineering
Ravi	Engineering
Priya	Sales
Karan	Sales
Arjun	HR

Neha is gone (she matches no department) and so is Legal (it matches no employee). An inner join keeps a row only when the ON condition is true for both sides. JOIN on its own means INNER JOIN.

LEFT JOIN: keep everything from the left table

SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

You get the same five rows plus Neha | NULL. Every employee shows up. Where there's no match, the right-hand columns are filled with NULL. Legal still doesn't appear, because departments is the right table here.

RIGHT JOIN: the mirror image

SELECT e.name, d.name AS dept
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Now Legal appears as NULL | Legal, and Neha disappears. In practice most people just swap the table order and write a LEFT JOIN, because it reads more naturally.

FULL OUTER JOIN: keep everything from both

SELECT e.name, d.name AS dept
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Seven rows: the five matches, Neha | NULL, and NULL | Legal. On MySQL you can get the same result with a LEFT JOIN ... UNION ... RIGHT JOIN of the same two tables.

CROSS JOIN: every combination

SELECT COUNT(*) AS combos
FROM employees CROSS JOIN departments;

That returns 24 (6 employees × 4 departments). You rarely want this on purpose. Usually it shows up by accident when someone forgets the ON condition. If your result ever has "rows × rows" in it, check your join condition first.

Note: SQL doesn't promise any row order unless you write ORDER BY. Your rows may come back in a different order from the tables above. That's fine.

5 interview questions on joins

These are the kinds of questions that separate "I've used joins" from "I understand joins." Try each one yourself before you read the answer.

1. Find departments that have no employees

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;

Result: Legal. This is the anti-join pattern: LEFT JOIN, then keep only the rows where the right side didn't match. Check a column that can never be NULL in a real match, such as the primary key e.id. NOT EXISTS is an equally good answer, and it's worth mentioning both.

2. List each employee with their manager's name

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

This is a self join: the same table, under two aliases. Interviewers like to ask why it's a LEFT JOIN. The answer: Asha has no manager, and with an INNER JOIN she would disappear from the list.

3. Why did my LEFT JOIN turn into an INNER JOIN?

Compare these two queries:

-- Version A: filter in WHERE
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.salary > 70000;

-- Version B: filter in ON
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id AND e.salary > 70000;

Version A returns 3 rows (Engineering twice, Sales once). Version B returns 5: the same 3, plus HR | NULL and Legal | NULL.

Why? WHERE runs after the join. The unmatched rows have e.salary = NULL, and NULL > 70000 is not true, so WHERE throws them away. Your LEFT JOIN quietly becomes an inner join. If the question is "show every department, and any well-paid employees in it," the filter on the right table belongs in ON.

4. Count employees per department, including empty ones

SELECT d.name, COUNT(*) AS wrong, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name;

name	wrong	headcount
Engineering	2	2
Sales	2	2
HR	1	1
Legal	1	0

COUNT(*) counts rows, and the LEFT JOIN produced one row for Legal (full of NULLs). COUNT(e.id) counts only non-NULL values, which gives the right headcount of 0. Mixing these two up is a classic way to fail this question.

The same idea explains the "why did my row count go up?" question. In a one-to-many join, each department row is repeated once for every matching employee. That's correct behaviour. Just remember it before you SUM a column from the "one" side.

5. Highest-paid employee in each department

Version with a correlated subquery:

SELECT d.name AS dept, e.name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.dept_id = e.dept_id
);

Version with a window function:

SELECT dept, name, salary
FROM (
  SELECT d.name AS dept, e.name, e.salary,
         RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
  FROM employees e
  JOIN departments d ON e.dept_id = d.id
)
WHERE rnk = 1;

Both return Asha (Engineering), Priya (Sales) and Arjun (HR). A good follow-up to bring up yourself is ties: RANK() keeps every employee tied for the top salary, while ROW_NUMBER() would pick exactly one of them.

A quick mental checklist

Before you run any join, ask yourself:

Which rows must not disappear? That table goes on the left of a LEFT JOIN.
Is this one-to-one or one-to-many? If it's one-to-many, expect repeated rows and be careful with aggregates.
Where does each filter go? Filters on the optional (right) table usually belong in ON.
What am I counting? COUNT(*) counts rows. COUNT(col) counts non-NULL values.

Try it in the browser

Reading about joins only gets you so far. You need to write a few that go wrong. Our hands-on SQL practice has chapters on INNER JOIN, outer joins and NULL handling, with exercises you run in the browser. Nothing to install.

A good exercise: extend the dataset above with a projects table (id, name, dept_id) and write a query that lists every department with its number of employees and its number of projects. If you get inflated counts, go back to question 4.

Happy querying!