INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

Predict WHERE, IS NULL and INNER/LEFT JOIN results; distinguish ordering from filtering and an UPDATE target.

Prerequisite

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.

Documents: five committed rows in CorpDocs.app.Documents
Document IDTitleOwner IDStatusReviewed on
101Handbook1openNULL
102Diagram1open2026-10-22
103Budget2draftNULL
104Archive3open2026-10-23
105UnassignedNULLopenNULL
People: three owner rows
Person IDName
1Aki
2Mei
3Ren
Separate document identity from its owner reference
Documents

document_id is unique per document; owner_id references an owner.

People

person_id identifies a person; multiple documents can reference the same person.

Document 101: Handbookowner_id = 1 1: Aki
Document 102: Diagramowner_id = 1 1: Aki
Document 103: Budgetowner_id = 2 2: Mei
Document 104: Archiveowner_id = 3 3: Ren
Document 105: Unassignedowner_id = NULL Owner unspecified

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

Read open document IDs and titles in ID order
SELECT document_id, title
FROM app.Documents
WHERE status = 'open'
ORDER BY document_id;
Four rows satisfy WHERE
Document IDTitle
101Handbook
102Diagram
104Archive
105Unassigned

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

Read documents with a NULL review date
SELECT document_id, title
FROM app.Documents
WHERE reviewed_on IS NULL
ORDER BY document_id;
Three rows retained by IS NULL
Document IDTitleReviewed on
101HandbookNULL
103BudgetNULL
105UnassignedNULL

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

LEFT JOIN retains documents without an assigned owner
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;
LEFT JOIN result: retain all five left-side documents
Document IDTitleOwner name
101HandbookAki
102DiagramAki
103BudgetMei
104ArchiveRen
105UnassignedNULL

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.

Illustrative update of document 103 only
UPDATE app.Documents
SET status = 'open'
WHERE document_id = 103;
SET specifies the change; WHERE specifies target rows
PredicateTarget IDsReason
document_id = 103103: one rowUnique primary-key match
owner_id = 1101 and 102: two rowsTwo documents share the owner
No WHERE101–105: five rowsNo row filter

Check returned rows and update targets

Choose an option to see the result and explanation.

Which ordered document IDs have status=open in CorpDocs?
Read the answer and reason

Answer: 101,102,104,105 · Those are four rows; 103 is draft.

How many rows result from Documents INNER JOIN People on owner_id?
Read the answer and reason

Answer: Four · 101–104 match; 105 with NULL owner does not.

Will WHERE reviewed_on = NULL select the three NULL dates?
Read the answer and reason

Answer: No; use IS NULL · Equality against NULL yields UNKNOWN.

In W0, editor_demo has SELECT and UPDATE on status, and document_id=103 is unique. How many rows match UPDATE ... WHERE document_id=103?
Read the answer and reason

Answer: One · One primary-key row matches. This predicts a target, not an observed execution.

Check, then explain in your own words

  1. Fix the target, principal, permissions and five data rows.
  2. Identify the IDs retained by WHERE and IS NULL.
  3. Compare INNER and LEFT treatment of the unassigned document.
  4. 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.