DevPath
Tracks

Databases and SQL

The relational model, tables, keys, SELECT, JOIN, GROUP BY, and normalization, with a runnable SQLite example and a hand-built join and group-by.

beginner45 min#sql#relational#join#group-by#normalization#sqlite

Prerequisites: Hash Tables

Why it matters

Most applications are a thin layer over a database, and SQL has outlived every framework fashion since the 1970s. A developer who can express "total spent per customer, including customers with no orders" in four lines, instead of looping in application code, writes faster, more correct, and more efficient software. SQL questions also appear in a large share of interviews.

Intuition

A relational database is a set of spreadsheets with rules. Each sheet (a table) describes one kind of thing: customers, orders. Each row is one instance, each column one attribute. Sheets refer to each other through ids, not by copying data: an order stores customer_id = 1 rather than the customer's name and email.

SQL is declarative: you describe the result you want ("customers with their order totals, largest first") and the database engine decides how to compute it. That separation lets the engine use indexes and clever plans without you changing the query.

How it works

Keys. A primary key uniquely identifies a row (customers.id). A foreign key is a column that points at another table's primary key (orders.customer_id), and the database can enforce that it never points at nothing.

The core clauses, in the order you write them and the order they logically run:

  1. FROM and JOIN: pick the tables and combine rows.
  2. WHERE: keep rows that match a condition (before grouping).
  3. GROUP BY: collapse rows sharing a value into one group.
  4. HAVING: filter groups (after aggregation).
  5. SELECT: choose columns and compute expressions and aggregates (COUNT, SUM, AVG, MIN, MAX).
  6. ORDER BY and LIMIT: sort and cut.
FROM / JOINcombine tables
WHEREfilter rows
GROUP BYmake groups
HAVINGfilter groups
SELECTcompute columns
ORDER BY / LIMITsort and cut
Logical execution order, which differs from the order you write the clauses. SELECT runs late, so WHERE cannot use a column alias or an aggregate defined there.

Joins

  • INNER JOIN: only rows with a match on both sides.
  • LEFT JOIN: every row from the left table; right columns are NULL when nothing matches.
 customers            orders
 1 Ada                1  cust 1  30
 2 Linus              2  cust 1  25
 3 Grace              3  cust 2  40      Grace has no orders

 LEFT JOIN on customers.id = orders.customer_id
 Ada   30      Ada   25      Linus 40      Grace NULL

Here are the same two tables and the LEFT JOIN result as grids.

customers.idname
1
Ada
2
Linus
3
Grace
customers: Grace (highlighted) has no matching row in orders.
orders.idcustomer_idamount
1
1
30
2
1
25
3
2
40
orders: customer_id points back at customers.id. No order has customer_id 3.
nameorders.idamount
Ada
1
30
Ada
2
25
Linus
3
40
Grace
NULL
NULL
LEFT JOIN result: Ada appears twice (two matches), and Grace is kept with NULL on the right side. An INNER JOIN would drop her row.

NULL means "unknown or missing" and behaves oddly: NULL = NULL is not true. Test with IS NULL, and use COALESCE(x, 0) to substitute a default.

Normalization removes duplication that causes update anomalies.

  • 1NF: each cell holds one value, no repeating groups (no column called phones holding "555-1,555-2").
  • 2NF: every non-key column depends on the whole key (matters for composite keys).
  • 3NF: non-key columns depend only on the key, not on each other. If orders stores customer_id and customer_city, the city depends on the customer, so move it to customers.

Rule of thumb: store each fact once. Denormalize deliberately later for read speed, knowing you must keep copies in sync.

OperationTimeNotes
Lookup by primary keyO(log n)Backed by an index; see next lesson
Filter without an indexO(n)Full table scan
Hash join of A and BO(|A| + |B|)Build a hash table on one side, probe with the other
GROUP BY via hashingO(n)Or O(n log n) when sorting first

Code: join and group by

Python uses its built-in sqlite3. Node does not guarantee a bundled SQL engine, so the JavaScript tab implements the same logic by hand with a hash join and a group-by over plain arrays. That also shows what the engine does underneath.

import sqlite3
 
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    amount INTEGER NOT NULL
);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Linus'), (3, 'Grace');
INSERT INTO orders VALUES (1, 1, 30), (2, 1, 25), (3, 2, 40);
""")
 
rows = con.execute("""
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total, COUNT(o.id) AS n_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total DESC
""").fetchall()
 
for name, total, n in rows:
    print(name, total, n)

Both versions print three lines: Ada 55 2, Linus 40 1, Grace 0 0. Grace survives because of the LEFT JOIN; an INNER JOIN would drop her. COUNT(o.id) counts only non-NULL values, so she has 0 orders, whereas COUNT(*) would have wrongly reported 1.

After the LEFT JOIN, GROUP BY c.id puts the joined rows into one bucket per customer, and the aggregates collapse each bucket into one row.

group (c.id)amounts in buckettotaln_orders
Ada
30, 25
55
2
Linus
40
40
1
Grace
NULL
0
0
One row per bucket. Grace's bucket holds a single NULL: COUNT(o.id) skips it (0) and COALESCE turns the NULL sum into 0.

Common pitfalls

  • WHERE versus HAVING: WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after.
  • Selecting non-grouped columns: every selected column must be in GROUP BY or inside an aggregate. SQLite tolerates violations and returns arbitrary values, which hides bugs.
  • Join row explosion: joining on a non-unique key multiplies rows, and SUM then overcounts. Check cardinality: one customer to many orders is fine, many to many needs care.
  • NULL surprises: WHERE x != 5 also excludes rows where x is NULL. COUNT(column) skips NULLs.
  • SELECT * in application code: it breaks when columns are added and fetches data you do not need.
  • Over-normalizing or under-normalizing: copying the customer name into every order invites inconsistency; splitting a table into ten tiny ones for no reason makes every query a join marathon.

Practice

Customers with no orders easy

Hint

LEFT JOIN, then look for the missing side.

Show solution

SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL. Rows where the right side did not match have NULL in every o column. An alternative is WHERE NOT EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id).

Big spenders medium

Hint

You need to filter on an aggregate.

Show solution

SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 50. WHERE SUM(amount) > 50 is an error because the sum does not exist until groups are formed. With our data this returns customer 1 with 55.

Normalize a messy table medium

Hint

Find what repeats, and what depends on what.

Show solution

Given orders(id, customer_name, customer_email, product, product_price), customer details repeat for every order and product price repeats for every sale. Split into customers, products, and orders(id, customer_id, product_id, quantity). Now a price or email change is one update. Store the price at purchase time in orders if history matters.

Second highest salary hard

Hint

Think of ordering and skipping, or a subquery for the maximum.

Show solution

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) handles ties and returns NULL if there is no second value. Alternatively SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1, which returns no row in that case. Window functions (DENSE_RANK) generalize this to the n-th highest.

Interview questions

  1. INNER JOIN versus LEFT JOIN? Inner keeps only matching pairs; left keeps every left row and fills right columns with NULL when unmatched.
  2. What is the difference between WHERE and HAVING? Row filter before grouping versus group filter after aggregation.
  3. What is a primary key versus a foreign key? Unique row identifier versus a reference to another table's key, used to enforce relationships.
  4. Why normalize, and when would you denormalize? Normalize to avoid redundancy and update anomalies; denormalize for read performance when joins are a measured bottleneck, accepting the sync burden.
  5. How would you prevent SQL injection? Parameterized queries, least-privilege database users, and never concatenating user input.

Interviewers look for: stating the join type before writing it, handling NULLs and empty groups, asking about duplicates and ties, and explaining a query in terms of its logical execution order.

Quick check

You want every customer listed, even those with zero orders, plus their order count. Which is correct?

Finished this lesson?

Further resources

Related lessons