SQL Basics: DDL, DML and Joins

7 pages · ~5 min✓ Reviewed

Start here. Next up: Relational Model and Relational Algebra.

DBMSPage 1

SQL Mental Model and Tables

1. Model and Statement Families

A table = rows (records) by typed columns
SQL is declarative: you state the result, the engine picks the plan

Family	Job	Statements
DDL	defines structure	CREATE, ALTER, DROP
DML	changes and reads data	INSERT, UPDATE, DELETE, SELECT

2. CREATE TABLE and Types

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Need	Type
Whole numbers	`INT` / `BIGINT`
Exact money	`DECIMAL(p,s)`
Approximate	`FLOAT`
Text	`VARCHAR(n)` / `TEXT`
Flag	`BOOLEAN`
Date, date + time	`DATE` / `TIMESTAMP`

3. Constraints

NOT NULL: value required
UNIQUE: no duplicate values
DEFAULT: value used when none given
CHECK (price > 0): row must pass the test
PRIMARY KEY: row identifier
FOREIGN KEY: must match a key in another table

Money

Store money in DECIMAL(p,s), never FLOAT: float is approximate.

DBMSPage 2

Primary and Foreign Keys

1. Keys and the FK link

Primary key = unique + NOT NULL row identifier
Only one PK per table
May be composite: PRIMARY KEY (order_id, product_id)
Foreign key values must exist in the referenced table's key

sql

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
    ON DELETE CASCADE
);

orders is the child, customers the parent

2. Referential actions on delete

Action	Effect on child rows
`CASCADE`	Deletes the child rows too
`SET NULL`	Clears the reference to NULL
`RESTRICT` / `NO ACTION`	Blocks the parent delete

3. Surrogate vs natural key

Surrogate: auto-increment id, no business meaning
Natural: real data like email or ISBN
Natural keys change, so every FK pointing at them breaks
Prefer a surrogate PK plus a UNIQUE constraint on the natural key

DBMSPage 3

INSERT, UPDATE, DELETE and SELECT

1. Changing rows

INSERT takes many rows; list the columns so schema changes don't break it
No WHERE on UPDATE or DELETE = every row changes

sql

INSERT INTO users (email, name)
VALUES ('a@x.com','Asha'), ('b@x.com','Bo');
UPDATE orders SET status = 'shipped' WHERE id = 7;
DELETE FROM orders WHERE id = 7;

2. SELECT and run order

SELECT col1, col2 FROM t WHERE cond ORDER BY col DESC LIMIT 10
Avoid SELECT * in production code
A SELECT alias works in ORDER BY, not in WHERE

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

3. WHERE operators

Operator	Meaning
`= <> < >`	compare
`AND` `OR` `NOT`	combine conditions
`IN (1,2)`	any listed value

DBMSPage 4

Joins

1. Join Types

Join	Unmatched rows
`INNER`	dropped
`LEFT`	left kept, right cols = NULL
`RIGHT`	mirror: right kept, left cols = NULL
`FULL OUTER`	both sides kept

INNER JOIN keeps only rows with a match on both sides
FROM orders o JOIN customers c ON c.id = o.customer_id

2. Anti-Join

LEFT JOIN + IS NULL on the right key
Finds left rows with no match

sql

SELECT c.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

customers with no orders

3. ON vs WHERE

Right-table filter in WHERE turns a LEFT JOIN into an inner join
Put it in ON: it limits which orders count as a match
Then WHERE o.id IS NULL finds customers with no shipped order

DBMSPage 5

GROUP BY, HAVING and NULL Semantics

1. GROUP BY and aggregates

GROUP BY collapses rows into one row per group
COUNT, SUM, AVG, MIN, MAX run per group
Every non-aggregated SELECT column must be in GROUP BY
Otherwise error, or an arbitrary value in lax MySQL modes

sql

SELECT dept, COUNT(*), AVG(salary)
FROM emp
GROUP BY dept;

one output row per dept

2. WHERE vs HAVING

FROM
WHEREfilters rows
GROUP BYmakes groups
HAVINGfilters groups
SELECT

WHERE runs before grouping, so it cannot use aggregates
HAVING runs after grouping: HAVING COUNT(*) > 5

3. NULL semantics

Expression	Result
`NULL = NULL`	not true, use `IS NULL` / `IS NOT NULL`
`5 + NULL`, `x > NULL`	NULL

DBMSPage 6

SQL Cheat Sheet

1. DDL and DML

DDL defines structure; DML changes rows
ON DELETE CASCADE removes child rows with the parent

sql

CREATE TABLE t (id INT PRIMARY KEY, fk INT REFERENCES p(id) ON DELETE CASCADE);
ALTER TABLE t ADD COLUMN c TEXT;
DROP TABLE t;
INSERT INTO t (a,b) VALUES (1,2);
UPDATE t SET a=1 WHERE id=3;
DELETE FROM t WHERE id=3;

2. Query skeleton

SELECT
FROMJOIN ... ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

3. Joins

Join	Returns
`INNER`	matches only
`LEFT`	all left + NULLs
`FULL`	all both sides
Anti-join	`LEFT JOIN` + `WHERE r.id IS NULL`

4. NULL rules

IS NULL, never = NULL

DBMSPage 7

Check yourself

Asha has 2 orders and Bo has none. What does this return?

Asha 2, Bo 0
COUNT(o.id) skips the NULL row; COUNT(*) would give Bo 1

SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name

We want every customer with their shipped-order count, but Bo vanishes. Fix?

WHERE o.status = 'shipped' rejects Bo's NULL row, so the join acts inner
Use ON o.customer_id = c.id AND o.status = 'shipped' and no WHERE on o.status

... LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'

Why does this return no rows, though Bo has no orders and one customer_id is NULL?

NOT IN expands to id <> 1 AND id <> 2 AND id <> NULL
id <> NULL is unknown, so the AND is never true
Use NOT EXISTS or filter NULLs out

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders)