SQL basics: querying a relational database

Overview

Almost every real application has to remember things between requests: user accounts, orders, messages, blog posts, audit logs. That long-term memory usually lives in a database. The most common kind is a relational database — software (such as PostgreSQL, MySQL, SQLite, or SQL Server) that stores data in tables and lets you read and change it with a language called SQL (Structured Query Language, often pronounced "sequel").

Think of a single table as a spreadsheet with strict rules. Each table has a name (users, orders). Each column has a name and a fixed type — a column declared as an integer can only hold integers, a text column only text. Each row is one record: one user, one order. A relational database is "relational" because tables can reference each other (an orders row points back to the users row that placed it), and SQL lets you follow those references.

Why learn SQL on a platform that teaches C and defensive security? Because SQL injection is one of the most common and most damaging web vulnerabilities ever recorded, and you cannot understand it, find it, or fix it without first being able to read SQL. The next lesson, SQL injection and prepared statements, builds directly on this one. Here the goal is narrow and concrete: get comfortable reading and writing SELECT / INSERT / UPDATE / DELETE, and understand the WHERE, JOIN, and UNION clauses — because those last three are exactly where injection operates.

A quick note on terminology so the rest of the lesson reads smoothly:

• Schema — the structure: which tables exist, their columns, and column types.
• Query — a SQL statement you send to the database. A SELECT is a read query; INSERT/UPDATE/DELETE are write queries.
• Result set — the rows a SELECT returns, themselves shaped like a table.
• Primary key — a column whose value uniquely identifies a row (commonly id).
• Foreign key — a column in one table that holds the primary-key value of a row in another table (this is what JOINs follow).

Why it matters

SQL is the lingua franca of stored data. Banks, hospitals, shopping carts, social networks, and the dashboards behind them all speak it. If you build or audit web software, you will read SQL constantly — in application code, in logs, in incident reports.

For security specifically, reading SQL is non-negotiable. SQL injection happens when untrusted input (from a form field, URL parameter, or HTTP header) is glued directly into a SQL statement, so the input changes the meaning of the query rather than just its data. Two patterns cause most of the damage:

• Tampering with WHERE — input like ' OR '1'='1 turns a row filter into a condition that is always true, so the query returns every row (for example, logging in without a valid password, or dumping an entire user table).
• Appending a UNION SELECT — an attacker bolts a second SELECT onto the original to read data the page was never meant to show, such as password hashes from another table. This is union-based SQL injection.

You cannot recognize, report, or remediate either pattern if you cannot read the underlying query. That is why this "basics" lesson is a security prerequisite, not a detour. (The actual exploitation-and-defense material lives in the next lesson and runs only against a local, intentionally vulnerable lab — never a system you do not own.)

Core concepts

1. Tables, rows, and columns

Definition. A table is a named collection of rows (records). Every table defines a set of columns, and each column has a name and a fixed data type (integer, text, date, boolean, and so on).

Plain language. A table is a spreadsheet with a rigid header. The header (columns) never changes per row; every row must supply a value of the right type for each column.

How it works internally. The database stores rows on disk in a structured file and keeps indexes (sorted lookup structures, often B-trees) on key columns so it can find matching rows fast instead of scanning every row.

Table: users
+----+--------+----------------+
| id | name   | email          |   <- columns (id:int, name:text, email:text)
+----+--------+----------------+
| 1  | ada    | ada@x.com      |   <- row (one record)
| 2  | linus  | linus@x.com    |
| 3  | grace  | grace@x.com    |
+----+--------+----------------+
      ^
      primary key: uniquely identifies each row

When to use / not use. Use a relational table when data is structured and you query it by fields (find user by id, list orders over $100). It is less natural for free-form documents or huge unstructured blobs.

Pitfall. Forgetting that columns are typed. Inserting 'abc' into an integer column is an error in most databases; relying on the database to silently "fix" types leads to surprises.

Knowledge check (explain in your own words): In one sentence, what is the difference between a column and a row?

2. The four verbs: SELECT, INSERT, UPDATE, DELETE

Definition. These four statements cover the standard data operations, sometimes called CRUD — Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE).

Plain language. SELECT reads without changing anything. The other three change the table: INSERT adds rows, UPDATE edits existing rows, DELETE removes rows.

Structure.

SELECT <columns> FROM <table> [WHERE <condition>];
INSERT INTO <table> (<columns>) VALUES (<values>);
UPDATE <table> SET <col = value> [WHERE <condition>];
DELETE FROM <table> [WHERE <condition>];

When to use / not use. Use SELECT for reports and lookups; the write verbs to maintain data. Be extremely careful with UPDATE and DELETE without a WHERE — they affect every row in the table.

Pitfall. Running DELETE FROM users; with no WHERE deletes all users. A misplaced or missing WHERE is one of the most common destructive mistakes in production.

Knowledge check (predict the output): The users table has 3 rows. What does UPDATE users SET email = 'oops@x.com'; (no WHERE) do?

3. WHERE — the row filter (and the injection point)

Definition. The WHERE clause restricts a statement to rows that satisfy a boolean condition.

Plain language. WHERE answers "which rows?" Only rows for which the condition is true are returned (SELECT) or changed (UPDATE/DELETE).

How it works internally. The database evaluates the condition row by row (using an index when one is available) and keeps only the matches.

SELECT * FROM users WHERE id = 42;
                          \_______/
                           condition: keep rows where id equals 42

Why it is the injection point. If application code builds the WHERE clause by concatenating user input as text, the input can inject new SQL logic. Compare:

Intended:   ... WHERE name = 'ada'
Input "ada":     name = 'ada'                     -> matches Ada only
Input "' OR '1'='1":  name = '' OR '1'='1'        -> always true -> ALL rows

The single quote from the input closes the string the developer opened, and the rest becomes live SQL. That is the essence of SQL injection, covered fully next lesson.

When to use / not use. Use WHERE on essentially every targeted read and every UPDATE/DELETE. Do not assemble it from raw user input — use parameters/prepared statements (next lesson).

Pitfall. Using = to compare with NULL. In SQL, WHERE col = NULL is never true; you must write WHERE col IS NULL.

Knowledge check (find the bug): A login query is built as "SELECT * FROM users WHERE name = '" + input + "'". What does the attacker type to make it return every user?

4. JOIN — combining tables on a shared key

Definition. A JOIN produces rows by matching rows from two tables on a condition, usually a foreign key = primary key relationship.

Plain language. JOIN "glues" related rows together so one result row can contain columns from both tables.

users                     orders
+----+-------+           +----+---------+-------+
| id | name  |           | id | user_id | total |
+----+-------+           +----+---------+-------+
| 1  | ada   |  <------\  | 10 |   1     |  50   |
| 2  | linus |         \-| 11 |   1     |  20   |
+----+-------+           | 12 |   2     |  99   |
                         +----+---------+-------+
        JOIN ON orders.user_id = users.id
        =>
        ada   | 50
        ada   | 20
        linus | 99

How it works internally. The engine pairs rows that satisfy the ON condition (often using an index on the join column). An INNER JOIN (the default JOIN) keeps only rows that match on both sides; a LEFT JOIN keeps every row from the left table even when there is no match (filling missing right-side columns with NULL).

When to use / not use. Use JOIN to follow relationships (a user and their orders). Avoid joining on non-indexed columns over huge tables without thought — it can be slow.

Pitfall. Forgetting the ON condition. A JOIN with no condition produces a cross join — every row of one table paired with every row of the other — which explodes the result size.

Knowledge check (concept): With INNER JOIN, what happens to a user who has no orders at all — do they appear in the result?

5. UNION — stacking result sets

Definition. UNION combines the rows of two SELECT statements into one result set.

Plain language. JOIN combines tables side by side (more columns); UNION stacks results on top of each other (more rows).

Strict rules. The two SELECTs must return the same number of columns, in the same order, with compatible types. UNION removes duplicate rows; UNION ALL keeps duplicates (and is faster).

SELECT name, email FROM customers     -- 2 columns
UNION
SELECT name, email FROM suppliers;    -- must also be 2 compatible columns
=> one list containing both customers and suppliers

Why attackers love it. If a page runs a SELECT with, say, two visible columns, an attacker who can inject SQL may append UNION SELECT username, password_hash FROM users to read data from a different table through the original page. This is union-based SQL injection — and it is exactly why the column-count rule matters to an attacker (they must match the original query's column count). Defenders prevent it the same way they prevent all injection: parameterized queries and least-privilege database accounts.

When to use / not use. Use UNION to merge similar lists from different tables. Reach for UNION ALL when you do not need duplicate removal.

Pitfall. Mismatched column counts or types — SELECT a, b ... UNION SELECT a ... fails because the column counts differ.

Knowledge check (predict the output): SELECT 'x' UNION SELECT 'x'; returns how many rows, and why?

Syntax notes

Core SQL shapes, annotated. Square brackets mark optional parts; they are not SQL syntax.

-- READ: pick columns from a table, optionally filtered and ordered
SELECT col1, col2          -- which columns (use * for all, but name them in real code)
FROM   table_name          -- source table
WHERE  condition           -- which rows (optional)
ORDER BY col1 ASC          -- sort (optional; ASC ascending, DESC descending)
LIMIT 10;                  -- cap how many rows come back (optional)

-- CREATE: add a row
INSERT INTO table_name (col1, col2)   -- target columns
VALUES ('text value', 123);           -- matching values, in the same order

-- UPDATE: change existing rows (WHERE is critical!)
UPDATE table_name
SET    col1 = 'new value'
WHERE  id = 42;            -- without WHERE, every row is updated

-- DELETE: remove rows (WHERE is critical!)
DELETE FROM table_name
WHERE  id = 42;            -- without WHERE, every row is deleted

-- JOIN: combine related tables
SELECT u.name, o.total
FROM   users u                 -- 'u' is a table alias (shorthand)
JOIN   orders o ON o.user_id = u.id;   -- match condition

-- UNION: stack two result sets (same column count + compatible types)
SELECT name FROM customers
UNION ALL                      -- ALL keeps duplicates; plain UNION removes them
SELECT name FROM suppliers;

Conventions worth adopting early: SQL keywords are conventionally written in UPPERCASE (the database does not care, but it aids readability); statements end with a semicolon; single quotes '...' delimit text values; double quotes are for identifiers in standard SQL (databases vary). Prefer naming columns explicitly over SELECT * in application code so results stay stable when the schema changes.

Lesson

Most web apps store data in a relational database (data organized into linked tables) and query it with SQL. You don't need to be a database administrator, but you must be able to read SQL to understand injection.

Tables and the four verbs

Data lives in tables: rows of records, with typed columns. The four core statements are:

SELECT name, email FROM users WHERE id = 42;
INSERT INTO users (name, email) VALUES ('ada', 'ada@x.com');
UPDATE users SET email = 'new@x.com' WHERE id = 42;
DELETE FROM users WHERE id = 42;

WHERE

The WHERE clause filters rows. It is also exactly where SQL injection strikes.

If user input is concatenated directly into the WHERE clause, an input like ' OR '1'='1 turns the filter into a condition that is always true. The query then returns every row.

JOIN

JOIN combines rows from two tables based on a relationship between them:

SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id;

Here each user row is matched to its order rows where user_id equals the user's id.

UNION

UNION stacks the results of two SELECT statements that have matching columns.

Attackers abuse UNION SELECT to append their own query (for example, one that pulls password hashes) onto an injectable one. This is union-based SQL injection.

Takeaway

Reading the four verbs plus WHERE, JOIN, and UNION is enough to follow how injection works.

Code examples

Below is a complete, runnable SQLite script. SQLite ships with most systems and needs no server, so you can paste this into a file (e.g. shop.sql) and run sqlite3 :memory: < shop.sql, or paste it line by line into the sqlite3 prompt. It builds a tiny shop schema and exercises every clause from this lesson.

-- shop.sql : a minimal, self-contained demo of SQL basics

-- 1. Define the schema (two related tables)
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,   -- unique row identifier
    name  TEXT NOT NULL,         -- required text
    email TEXT NOT NULL
);

CREATE TABLE orders (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,    -- foreign key -> users.id
    total   INTEGER NOT NULL,    -- amount in whole dollars
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 2. INSERT: add some rows
INSERT INTO users (id, name, email) VALUES
    (1, 'ada',   'ada@x.com'),
    (2, 'linus', 'linus@x.com'),
    (3, 'grace', 'grace@x.com');   -- grace will have no orders

INSERT INTO orders (id, user_id, total) VALUES
    (10, 1, 50),
    (11, 1, 20),
    (12, 2, 99);

-- 3. SELECT + WHERE: read one user by id
SELECT name, email FROM users WHERE id = 1;

-- 4. UPDATE + WHERE: change one row only
UPDATE users SET email = 'ada@new.com' WHERE id = 1;
SELECT name, email FROM users WHERE id = 1;   -- confirm the change

-- 5. JOIN: each user paired with their order totals (INNER JOIN)
SELECT u.name, o.total
FROM   users u
JOIN   orders o ON o.user_id = u.id
ORDER  BY u.name;

-- 6. UNION: stack two reads with matching column shapes
SELECT name FROM users WHERE id = 2
UNION
SELECT name FROM users WHERE id = 3;

-- 7. DELETE + WHERE: remove a single order
DELETE FROM orders WHERE id = 11;
SELECT COUNT(*) FROM orders;   -- how many orders remain?

What it does. Steps 1-2 create the schema and load three users and three orders. Step 3 reads Ada by primary key. Step 4 updates only Ada's email and re-reads it to prove the change is isolated. Step 5 joins users to orders, so each result row carries a name and a total. Step 6 unions two single-row reads into one list. Step 7 deletes one order and counts what is left.

Expected output (in order):

ada|ada@x.com                 -- step 3
ada|ada@new.com               -- step 4 (after update)
ada|50                        -- step 5 (JOIN), sorted by name
ada|20
linus|99
linus                         -- step 6 (UNION)
grace
2                             -- step 7: 3 orders minus 1 deleted = 2

Edge cases to notice. Grace (id 3) never appears in the JOIN result because she has no matching orders row — that is INNER JOIN behavior; a LEFT JOIN would include her with a NULL total. The UNION in step 6 would collapse duplicates if both reads returned the same name; UNION ALL would keep both. And if you removed the WHERE in step 4 or step 7, you would change or delete every row — the single most common destructive SQL mistake.

Line by line

Walking through the key statements and tracing how the data changes.

Step 1 - CREATE TABLE. Two tables are defined. users.id is the primary key (unique per row). orders.user_id is a foreign key that must hold a value present in users.id; this is the relationship JOIN will follow. NOT NULL forbids missing values in those columns.

Step 2 - INSERT. Three users and three orders are loaded. State afterward:

users: (1 ada), (2 linus), (3 grace)
orders: (10 -> user 1, $50), (11 -> user 1, $20), (12 -> user 2, $99)

Step 3 - SELECT ... WHERE id = 1. The engine scans (or index-seeks) for rows where id equals 1, finds Ada, and returns the two requested columns: ada|ada@x.com.

Step 4 - UPDATE ... WHERE id = 1. Only Ada's row matches the condition, so only her email becomes ada@new.com. Linus and Grace are untouched. The follow-up SELECT confirms ada|ada@new.com. Trace the importance of WHERE: if it were absent, all three emails would change.

Step 5 - JOIN. For every users row u, the engine looks for orders rows o where o.user_id = u.id:

After ORDER BY u.name, the output is the three ada/ada/linus rows shown earlier. Grace is absent because INNER JOIN keeps only matched pairs.

Step 6 - UNION. The first SELECT returns linus; the second returns grace. UNION stacks them into a two-row list. Both SELECTs return one text column, so the column-count and type rules are satisfied. Because the two values differ, UNION's duplicate removal has nothing to remove.

Step 7 - DELETE ... WHERE id = 11. One order matches and is removed. The remaining orders are 10 and 12, so COUNT(*) returns 2.

Security-flavored trace. Imagine step 3 were built by concatenation: "SELECT name, email FROM users WHERE id = " + input. With input = "1" you get the intended single row. With input = "1 OR 1=1" the WHERE becomes id = 1 OR 1=1 — always true — returning every user. That single substitution is how an injection turns a one-row lookup into a full table dump; remembering this trace is the whole point of the lesson.

Common mistakes

Mistake 1 - UPDATE/DELETE with no WHERE.

-- WRONG: changes every row
UPDATE users SET email = 'x@x.com';

Why it is wrong: with no WHERE, the condition defaults to "all rows." You just overwrote every user's email. The corrected form scopes the change:

-- RIGHT
UPDATE users SET email = 'x@x.com' WHERE id = 42;

Prevent it by writing the WHERE first, and by testing the same condition with a SELECT before running the UPDATE/DELETE.

Mistake 2 - comparing to NULL with =.

-- WRONG: returns nothing, even for rows with no email
SELECT * FROM users WHERE email = NULL;
-- RIGHT
SELECT * FROM users WHERE email IS NULL;

Why: in SQL's three-valued logic, any comparison with NULL yields "unknown," never "true." Recognize it when a query that should match empty values returns zero rows.

Mistake 3 - mismatched UNION shapes.

-- WRONG: 2 columns vs 1 column
SELECT name, email FROM customers
UNION
SELECT name FROM suppliers;

Why: UNION requires identical column counts and compatible types. Fix by making both SELECTs return the same shape (e.g. add the missing column or drop the extra one).

Mistake 4 - JOIN without an ON condition (accidental cross join).

-- WRONG: every user paired with every order
SELECT * FROM users, orders;

Why: omitting the relationship produces the Cartesian product (3 users x 3 orders = 9 rows). The corrected form states the relationship:

SELECT * FROM users u JOIN orders o ON o.user_id = u.id;

Mistake 5 (the dangerous one) - building WHERE from raw input.

-- WRONG (conceptually): user text concatenated into SQL
-- "SELECT * FROM users WHERE name = '" + name + "'"

Why: the input can break out of the string and inject logic (' OR '1'='1). This is SQL injection. The fix is parameterized / prepared statements, where input is sent as data, never as SQL text — that is the entire subject of the next lesson. Recognize the smell whenever you see string concatenation that mixes SQL keywords and untrusted input.

Debugging tips

"near "...": syntax error". The most common SQL error. Usually a missing comma between columns, a stray or missing single quote around a text value, a missing semicolon, or a keyword typo. Read the message: most engines point at the token where parsing broke.

"no such table" / "no such column". The name is misspelled, the table was never created in this session (in-memory databases reset every run), or you forgot a table alias prefix in a JOIN (o.total vs total). List the schema to check: .tables and .schema users in SQLite.

"UNION ... have different number of columns" (or similar). The two SELECTs do not match in column count. Count the columns on each side and align them.

A query returns 0 rows when you expected some. Check the WHERE: are you comparing text without quotes, comparing to NULL with =, or filtering on the wrong column? Temporarily drop the WHERE to confirm the rows exist, then add conditions back one at a time.

A JOIN returns too many rows. You likely have a missing or wrong ON condition (cross join), or you are joining on a non-unique column so rows multiply. Re-check the ON clause and confirm the join key is what you think it is.

Concrete debugging routine.

1. Reproduce the smallest failing query on its own.
2. SELECT the raw data first (no WHERE/JOIN) to confirm what is actually stored.
3. Add one clause at a time and re-run, watching where the result changes.
4. For destructive statements, run the WHERE inside a SELECT before the UPDATE/DELETE.

Questions to ask when it does not work. Does this table/column exist and is it spelled right? Are my text values quoted? Is my WHERE matching the rows I think? Do my UNION sides have the same shape? Does my JOIN have a correct ON condition?

Memory safety

This is a concept lesson, so there is no C memory model here — but the database equivalents of "memory safety" are data integrity and robustness, and they matter just as much.

• Validate before you write. Check that values are the right type and within range before INSERT/UPDATE. The database's NOT NULL, type, and FOREIGN KEY constraints are your safety rails — define them rather than trusting application code alone.
• Always scope destructive statements. Treat a WHERE-less UPDATE or DELETE the way you would treat a wild pointer in C: assume it will hit everything. Preview with SELECT, and run inside a transaction (BEGIN; ... ROLLBACK/COMMIT;) so you can undo a mistake.
• Never trust input as code. The database analog of a buffer overflow is SQL injection: untrusted bytes changing the meaning of a statement. The defensive habit is the same in spirit as bounds-checking — keep data and code separate. In SQL that means parameterized queries / prepared statements (next lesson), never string concatenation.
• Least privilege. The account your app uses should hold only the permissions it needs (often just SELECT/INSERT/UPDATE on specific tables). Then even a successful injection cannot DROP tables or read system catalogs. This is the database version of dropping privileges in a C daemon.
• Ethics note. Practice any injection-style experiments only against software you own or an intentionally vulnerable lab on localhost. Probing third-party databases is illegal and out of scope here; the aim is to defend, not attack.

Real-world uses

Where this shows up. SQL backs an enormous share of production software: e-commerce catalogs and orders, banking ledgers, hospital records, content management systems, analytics dashboards, and the user/session tables behind almost every login. SQLite (a single-file SQL database) is embedded in web browsers, phones, and countless desktop apps. Reading SQL is a daily skill for backend developers, data analysts, DBAs, and security engineers alike.

A concrete scenario. A web shop renders an order-history page by joining users to orders and filtering by the logged-in user's id — exactly the JOIN + WHERE pattern from this lesson. The same query, if built by concatenating the user id from the URL, is also the textbook injection target.

Professional best practices.

Beginner habits to build now:

• Name columns explicitly instead of SELECT * so results stay stable.
• Always write the WHERE for UPDATE/DELETE, and preview with SELECT first.
• Use clear, consistent table and column names; define NOT NULL and key constraints.
• Quote text values; let the database enforce types.

Advanced habits:

• Use parameterized queries / prepared statements everywhere user input appears — for correctness and security.
• Add indexes on columns you filter or join on frequently; understand the read/write trade-off.
• Wrap multi-statement changes in transactions so they are all-or-nothing.
• Apply least-privilege database accounts and log query errors (without logging secrets, PII, or full statements containing sensitive values) to support detection and incident response.

Practice tasks

Work these against the SQLite shop schema from the code section (recreate it if your in-memory database has reset).

Beginner 1 - Read with a filter.

• Objective: list the name and email of the user whose id is 2.
• Requirements: use SELECT with a WHERE on the primary key.
• Expected output: a single row, linus|linus@x.com.
• Concepts: SELECT, WHERE.
• Hint: name the two columns explicitly rather than using *.

Beginner 2 - Insert and verify.

• Objective: add a new user (id 4, name mads, email mads@x.com), then SELECT it back to confirm.
• Requirements: one INSERT, then one SELECT with WHERE.
• Constraint: id must be unique and name/email are NOT NULL.
• Concepts: INSERT, SELECT, WHERE.
• Hint: match the column order in your VALUES list to the column list.

Intermediate 1 - Scoped update.

• Objective: change user 4's email to mads@new.com without touching any other row.
• Requirements: an UPDATE with a precise WHERE; then a SELECT proving only that row changed.
• Concepts: UPDATE, WHERE.
• Hint: before running the UPDATE, run the same WHERE inside a SELECT to confirm it matches exactly one row.

Intermediate 2 - Join with a sum.

• Objective: for each user who has orders, show their name and the total of all their order amounts.
• Requirements: JOIN users to orders, group by user, and sum total.
• Input/output example: ada -> 70 (50 + 20), linus -> 99. (Grace has no orders.)
• Constraints: do not list users with no orders.
• Concepts: JOIN, aggregation (SUM, GROUP BY).
• Hint: GROUP BY u.name and select SUM(o.total).

Challenge - Combine two sources, then reason about injection.

• Objective (part A): produce a single combined list of all email addresses that belong to either users with an order over $40 or users named grace, with no duplicate emails.
• Requirements: write two SELECTs combined with UNION; the first uses a JOIN + WHERE on total, the second filters by name.
• Constraints: both SELECTs must return exactly one column (email) so the UNION is valid; duplicates must be removed.
• Objective (part B, written): in two or three sentences, explain what the WHERE condition on total would become if the threshold 40 were supplied by concatenating raw user input as 40 OR 1=1, and which rows the query would then return. Do not write any exploit — just describe the effect and state the correct defensive fix.
• Concepts: UNION, JOIN, WHERE, and the WHERE-injection reasoning from this lesson.
• Hint: get each SELECT working on its own before joining them with UNION; for part B, recall the always-true-condition trace.

Summary

• A relational database stores data in tables of rows and typed columns; SQL reads and changes it.
• The four core verbs are SELECT (read), INSERT (add), UPDATE (change), DELETE (remove) — the last two are destructive and need a careful WHERE.
• WHERE filters which rows a statement affects, and is the classic SQL-injection target when built from raw input.
• JOIN combines related tables side by side on a key (INNER keeps only matches; LEFT keeps all left rows). UNION stacks two result sets and requires matching column counts and types; attackers abuse UNION SELECT for union-based injection.
• Most common mistakes: WHERE-less UPDATE/DELETE, comparing to NULL with =, mismatched UNION shapes, ON-less JOINs, and — the dangerous one — concatenating user input into SQL.
• Remember: keep data separate from code. Reading SELECT/INSERT/UPDATE/DELETE plus WHERE/JOIN/UNION is exactly the groundwork you need for the next lesson on SQL injection and prepared statements.

Practice with these exercises

• Allowlist Validation for SQL Identifiers
• ORDER BY Column Allowlist
• Does the query use a placeholder?
• Is the query built by concatenation?