Topic lessonTopic: SQL Server and database foundations
T-SQL basics: SELECT, WHERE, NULL and JOIN results
SELECT chooses columns, WHERE retains matching rows, and JOIN combines rows using a condition. Predict results from the same five documents, changing one condition at a time. Keep a read-only principal separate from an independently authorized editor.
Predict WHERE, IS NULL and INNER/LEFT JOIN results; distinguish ordering from filtering and an UPDATE target.
Understand tables, keys, current database and SELECT permissions. No SQL execution environment is required.
Fix the tables and reader first
Case S0 uses CorpDocs and reader_demo, with SELECT on app.Documents and app.People. The committed T0 data below stays unchanged, with no concurrent writes, triggers or row-level filtering. Results here are predictions under those conditions.
| Document ID | Title | Owner ID | Status | Reviewed on |
|---|---|---|---|---|
| 101 | Handbook | 1 | open | NULL |
| 102 | Diagram | 1 | open | 2026-10-22 |
| 103 | Budget | 2 | draft | NULL |
| 104 | Archive | 3 | open | 2026-10-23 |
| 105 | Unassigned | NULL | open | NULL |
| Person ID | Name |
|---|---|
| 1 | Aki |
| 2 | Mei |
| 3 | Ren |
document_id is unique per document; owner_id references an owner.
person_id identifies a person; multiple documents can reference the same person.
Arrows identify foreign-key references, not a data or network execution path.
A fictional teaching example. Containment, communication and permission relationships have different meanings.
Selecting columns and filtering rows are different
SELECT document_id, title
FROM app.Documents
WHERE status = 'open'
ORDER BY document_id;| Document ID | Title |
|---|---|
| 101 | Handbook |
| 102 | Diagram |
| 104 | Archive |
| 105 | Unassigned |
The SELECT list chooses returned columns. Changing this example to title alone still returns four rows unless another operation such as DISTINCT is added. WHERE retains rows for which the open-status condition is TRUE.
NULL represents a missing or unspecified value
SELECT document_id, title
FROM app.Documents
WHERE reviewed_on IS NULL
ORDER BY document_id;| Document ID | Title | Reviewed on |
|---|---|---|
| 101 | Handbook | NULL |
| 103 | Budget | NULL |
| 105 | Unassigned | NULL |
NULL is distinct from an empty string or zero. In this SQL Server example, reviewed_on = NULL evaluates to the truth value UNKNOWN. WHERE keeps TRUE, so that comparison does not return the same three rows as IS NULL. This is different from evidence that has not been established.
INNER JOIN and LEFT JOIN treat document 105 differently
SELECT d.document_id, d.title, p.name AS owner_name
FROM app.Documents AS d
LEFT JOIN app.People AS p
ON d.owner_id = p.person_id
ORDER BY d.document_id;| Document ID | Title | Owner name |
|---|---|---|
| 101 | Handbook | Aki |
| 102 | Diagram | Aki |
| 103 | Budget | Mei |
| 104 | Archive | Ren |
| 105 | Unassigned | NULL |
Changing LEFT JOIN to INNER JOIN returns the four documents 101–104 with matching owners. Document 105 has a NULL owner_id and does not match the equality condition. LEFT JOIN retains it and supplies NULL for the right-side owner_name.
For UPDATE, separate the authorized editor, column and target
Independent case W0 starts from a fresh T0 copy. editor_demo is granted SELECT and UPDATE on the status column of app.Documents. This does not elevate reader_demo. The SQL below describes a target for reasoning, not an observed execution.
UPDATE app.Documents
SET status = 'open'
WHERE document_id = 103;| Predicate | Target IDs | Reason |
|---|---|---|
| document_id = 103 | 103: one row | Unique primary-key match |
| owner_id = 1 | 101 and 102: two rows | Two documents share the owner |
| No WHERE | 101–105: five rows | No row filter |
Check returned rows and update targets
Choose an option to see the result and explanation.
Check, then explain in your own words
- Fix the target, principal, permissions and five data rows.
- Identify the IDs retained by WHERE and IS NULL.
- Compare INNER and LEFT treatment of the unassigned document.
- Explain ordering with ORDER BY and update targets with WHERE.
Check your understanding: If LEFT JOIN returns five rows, are there five owners?
No. It retains five document rows.
Aki occurs for two documents, and document 105 has NULL owner information. Identify what a result row represents.
Sources and conditions checked
Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.
Scope and conditionsA primary key identifies a row and a foreign key enforces a reference; this sample explicitly allows a NULL owner. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonFix the tables and reader first INNER JOIN and LEFT JOIN treat document 105 differently
- SELECT (Transact-SQL)Checked:
Scope and conditionsSELECT returns rows, and WHERE can filter them. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonSelecting columns and filtering rows are different Check returned rows and update targets
Scope and conditionsRow order is not guaranteed without ORDER BY. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonSelecting columns and filtering rows are different NULL represents a missing or unspecified value INNER JOIN and LEFT JOIN treat document 105 differently
- NULL and UNKNOWN (Transact-SQL)Checked:
Scope and conditionsNULL comparisons yield UNKNOWN; use IS NULL. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonNULL represents a missing or unspecified value INNER JOIN and LEFT JOIN treat document 105 differently Check returned rows and update targets
- FROM clause plus JOIN (T-SQL)Checked:
Scope and conditionsLEFT JOIN preserves unmatched left rows with NULLs on the right. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonINNER JOIN and LEFT JOIN treat document 105 differently Check returned rows and update targets
- UPDATE (Transact-SQL)Checked:
Scope and conditionsT-SQL UPDATE supports a WHERE condition to narrow targeted rows. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonFor UPDATE, separate the authorized editor, column and target Check returned rows and update targets