Intuition The ONE core ideaA view is nothing but a SELECT query that you gave a name , so the database treats that name as if it were a table — but the table has no data of its own, it's recomputed from real ("base") tables every time you look. Everything else in this topic (why views are safe, why some can be edited and some can't) falls out of that one fact: a view is a window , not a box .
Before you can reason about views, you need to be fluent in the pieces the parent note assumes you already know. This page builds every one of them from zero. If a word below already feels obvious, good — read it anyway, because the picture attached to it is what makes updatable views click later.
Definition A note on the math markup (… a > b AVG = COUNT SUM typeset math (LaTeX) — it changes how the symbol looks , never what it means . Read a > b
Definition Table (base table)A table is a named grid of data stored on disk. Each row is one record (one employee, one item). Each column is one attribute (name, salary). The word base table just means "a real table that actually holds data" — as opposed to a view, which does not.
Picture the grid literally. The whole topic is about the difference between a grid that holds data and a name that only points at data.
What the figure shows: the grid employees with a header row (blue) naming three columns — emp_id, name, salary. The yellow arrow points along a column (all salaries stacked vertically); the green arrow points along a row (one person's full record read across). Fix this one picture in your mind: columns run down, rows run across.
Intuition Why we start hereEvery view is defined on top of one or more base tables. When you later ask "can I edit this view?", the real question is "can the database figure out which base-table row to change?" You cannot ask that question until you can see the base grid clearly.
Related deeper reading once you know the basics: Normalization explains why data is split across several such grids in the first place.
Before SELECT and JOIN, you need the idea of a key — it decides everything about updatability later.
A primary key is a column (or group of columns) whose value is unique for every row and never empty. It is the row's "name tag". In employees, emp_id is the primary key: no two employees share an emp_id, so given an emp_id there is exactly one row.
A foreign key is a column in one table that points at the primary key of another table. employees.dept_id is a foreign key referencing departments.dept_id: it says "this employee belongs to that department". Many employees may share the same dept_id — foreign keys are not required to be unique.
What the figure shows: two grids. In employees, the emp_id cells are outlined yellow — each value appears once (unique = primary key). The dept_id column has arrows crossing to the departments grid, landing on its yellow dept_id primary key. Notice two employees point at the same department — that is the "many-to-one" shape that will haunt joins.
Intuition Why keys decide updatabilityTo edit a view row, the database must find the one base row behind it. That is only possible if a unique identifier — the primary key — survives inside the view's columns. If your view's SELECT list forgets to include emp_id, then even a simple single-table view can become non-updatable, because the engine has no unique handle to locate the target row. Rule of thumb: carry the primary key through the projection.
SELECT is the command that reads a table and returns a new grid. You name the columns you want, the table to read from (FROM), and optionally a filter (WHERE).
SELECT name , salary -- which columns FROM employees -- from which table WHERE salary > 50000 ; -- keep only these rows Read it as three questions in order:
WHAT columns? → name, salary (chops the grid vertically ). This chosen column list is called the projection .FROM where? → employees (which grid).WHICH rows? → salary > 50000 (chops the grid horizontally ).
What the figure shows: the same employees grid. The blue box highlights the two chosen columns (name, salary) — that is the vertical cut. The red box highlights the surviving rows where salary > 50000 — that is the horizontal cut. What's left after both cuts is the view's result. Notice the projection here dropped emp_id — remember from §2 that dropping the primary key can cost you updatability.
SELECT * — the "give me every column" shorthand
SELECT * means "all columns of the source". It is convenient but risky for views: if someone later adds a column to the base table, the view silently starts exposing it — which can leak a secret column (e.g. salary) you meant to hide. And relying on * makes it easy to forget whether the primary key is present , the very thing updatability needs. Best practice in views: list columns explicitly, and always include the key.
Intuition Why the topic needs thisA view is literally a stored SELECT. The SELECT's two cuts — vertical (columns) and horizontal (rows) — are exactly the two "security surfaces" the parent note talks about: hide columns (like salary) or hide rows. If you don't see SELECT as "a pair of scissors on the grid", the security story sounds like magic.
The > symbol here is just comparison — "greater than". Below is the full toolkit of comparison symbols you'll meet.
More on how SELECT combines grids lives in SQL SELECT and JOINs .
WHERE condition keeps only the rows for which condition is true. It is a read-time filter : it decides what you see , not what is allowed to exist .
That last sentence is the seed of one of the parent's big "mistakes". Hold onto it.
What the figure shows: on the left, the full base table holds four prices, including a red $150. The dashed yellow box on the right is the view built with WHERE price < 100 — only the three cheap green rows pass through the "read" arrow. The $150 row is still in the base table ; the view simply never displays it. That single picture is the whole "WHERE filters reading, not existence" lesson.
Intuition Why this matters for viewsWhen you build a view with WHERE price < 100, you're saying "only show cheap items". But the base table can still contain a 150 i t e m — t h e ‘ W H E R E ‘ j u s t hi d es i t f r o m t h e v i e w . L a t er y o u ′ l l see t ha t an ‘ I N S E R T ‘ t h r o ug h t h e v i e w c an s n e ak a WHERE filters looking, not writing.
Definition NULL and three-valued logicNULL is SQL's word for "no value / unknown " — an empty cell. It is not zero and not an empty string; it means the data is missing. Because of this, SQL comparisons don't just return true or false — they return a third answer, UNKNOWN , whenever NULL is involved. For example salary > 50000 is UNKNOWN when salary is NULL, because you can't compare a number to "we don't know".
The rule that matters: a WHERE clause keeps a row only when the condition is exactly TRUE . So both FALSE and UNKNOWN rows are filtered out . This is why you must test missing data with IS NULL (not = NULL, which is always UNKNOWN and matches nothing).
Intuition Why NULL matters for viewsTwo later ideas depend directly on this: (1) a WHERE filter silently drops NULL rows, so a view can hide rows you didn't expect to hide; and (2) an outer join (next section) fills missing partners with NULL — and those manufactured NULLs are exactly why writing through outer-join views is ambiguous. NULL is the thread connecting filters and joins.
Definition Table aliases (employees e)
Writing FROM employees e gives the table employees a short nickname e, called an alias . After that, e.name means "the name column of the employees table ". The dot . reads as "belonging to". Aliases exist because a join mentions several tables, and two of them might both have a column called dept_id — the alias removes the ambiguity: e.dept_id versus d.dept_id.
ON clause
A join combines two tables into one wider grid by matching rows. The ON keyword states the match condition — which columns must be equal for two rows to be stitched together:
ON e.dept_id = d.dept_id reads as "pair an employee row with a department row only when their dept_id values are equal".
Here e.dept_id is a foreign key (§2) and d.dept_id is the primary key it points at — that key relationship is exactly what makes the match meaningful.
SELECT e . name , d . dept_name -- columns from BOTH tables FROM employees e -- alias e JOIN departments d -- alias d ON e . dept_id = d . dept_id ; -- match rule What the figure shows: the two grids on the left, joined by arrows on dept_id, produce the wide grid on the right. Look carefully: department "Sales" (dept_id = 1) appears in two output rows because two employees belong to it. The green highlight marks that duplicated department cell — one base department row, printed twice in the view.
Intuition Why joins threaten "updatability"A joined grid can list the same department row next to many employees. So if you tried to edit dept_name through the joined view, the database would ask: "which of the many base rows did you mean?" That ambiguity is the exact reason multi-table joins usually make a view read-only . You cannot understand that rule without first picturing the one-to-many stitch. Full mechanics: SQL SELECT and JOINs .
Definition Outer joins — LEFT / RIGHT / FULLA plain JOIN (inner join) keeps only rows that match on both sides . The outer variants keep the unmatched ones too, padding the missing side with NULL (the "unknown" value from §4):
LEFT JOIN — keep every row of the left table, even with no partner on the right.RIGHT JOIN — keep every row of the right table.FULL JOIN — keep unmatched rows from both sides.
Why this matters for updatability: outer joins can manufacture rows that exist on only one base table (the other side is all NULL). Writing to such a row is ambiguous or impossible — there is no real base row on the padded side. So outer-join views are even less updatable than inner-join ones; engines almost always make them read-only unless you supply the mapping yourself, using an INSTEAD OF trigger (see Triggers ).
What the figure shows: four separate salary boxes on the left flow through the blue "AVG (squash)" arrow into a single yellow box 50000 on the right. The dashed red arrow going backwards is crossed by the question "which numbers?" — because countless different four-number sets average to 50000, the reverse arrow has no unique answer. That is irreversibility, drawn.
Intuition Why aggregates kill updatabilitySquashing is a one-way street . If ten salaries average to 50 , 000 not remember the ten originals — infinitely many sets of ten numbers average to 50 , 000 60 , 000 the reason aggregate views can't be edited.
Definition GROUP BY / HAVINGGROUP BY dept_id bundles rows that share a dept_id into one group, so aggregates run per-group. HAVING is like WHERE but filters the groups after aggregation. Both rely on squashing, so both make a view non-updatable.
DISTINCT throws away duplicate rows, keeping one copy of each. Once two identical view rows have been merged into one, the database can't tell which original base row you meant to edit — so DISTINCT also breaks the one-to-one mapping.
Definition Set operations, and theirALL twins
These stack or compare two whole result grids:
UNION stacks two grids together; INTERSECT keeps rows common to both; EXCEPT keeps rows in the first grid but not the second.By default all three remove duplicate rows (an implicit DISTINCT).
The ALL variants — UNION ALL, INTERSECT ALL, EXCEPT ALL — keep duplicates instead. UNION ALL is also much faster, since it skips the duplicate-removal step.
Why this matters for updatability: a row in any of these views may come from either of two source tables (UNION) or exist only after comparing two grids (INTERSECT/EXCEPT) — so there is no single source row to edit. And the plain (non-ALL) forms also merge duplicates like DISTINCT, destroying the one-to-one mapping a second way. Either way: read-only.
Mnemonic The one-to-one testAsk of every view: "Does each row I see point back to exactly one real row in one base table, and does the view still carry that row's key? " If yes → editable. If a join duplicated it, an outer join padded it with NULL, an aggregate squashed it, DISTINCT/UNION merged it, or the projection dropped the key → read-only.
Definition The three write commands
INSERT — add a new row.UPDATE — change values in existing rows (UPDATE t SET col = val WHERE ...).DELETE — remove rows.
Together with SELECT (read), these are the four core operations. On a view, all three writes must be translated into the same operation on a base table — which only works when the one-to-one mapping (and a surviving key) holds.
Base table = grid of rows and cols
Key = unique row name tag
WHERE filters rows on read
NULL = unknown, filtered out
JOIN stitches two grids on a key
Outer JOIN pads with NULL
Aggregate squashes many rows to one
DISTINCT UNION and set ops merge rows
INSERT UPDATE DELETE = writes
Updatable only if one to one mapping and key survive
Read the map top-down: base tables (with their keys ) feed SELECT; SELECT gains filters (which drop NULL/unknown rows), joins, outer joins that pad with NULL, aggregates, and set operations; a view is just a named SELECT; and whether that view is updatable is decided by whether the one-to-one row mapping and the primary key survived all of that.
Test yourself — you're ready for the main note when you can answer each aloud.
What is a base table, in one picture? A named grid on disk where each row is one record and each column is one attribute; it actually holds data.
What is a primary key? A column whose value is unique and non-empty for every row, so it names exactly one row.
What is a foreign key, and must it be unique? A column pointing at another table's primary key; it is not required to be unique, so many rows may share the same value.
Why must a view carry the base table's key to stay updatable? Because the engine needs a unique handle to locate the single base row behind each view row; drop the key and there is no unique target.
What are the two "cuts" a SELECT makes on a grid? A vertical cut (choose columns, called projection) and a horizontal cut (WHERE chooses rows).
What do <> and != mean in SQL? Both mean "not equal"; <> is the standard spelling, != is accepted by most engines.
What is NULL, and what does a comparison with NULL return? NULL means an unknown/missing value; any comparison involving it returns UNKNOWN, not true or false.
Which rows does a WHERE clause keep under three-valued logic? Only rows where the condition is exactly TRUE; both FALSE and UNKNOWN rows are filtered out.
What does a table alias like employees e do, and how do you use it? It nicknames the table e; you then write e.column to name a column of that table, resolving ambiguity between tables.
What does the ON clause in a JOIN specify? The match condition — which columns must be equal for two rows to be stitched together, e.g. e.dept_id = d.dept_id.
How does an outer (LEFT/RIGHT/FULL) join affect updatability? It can create rows padded with NULL on one side, with no real base row there, making the view ambiguous to write and usually read-only.
What is the difference between UNION and UNION ALL? UNION removes duplicate rows (implicit DISTINCT); UNION ALL keeps duplicates and is faster.
Why is SELECT * risky in a view? New base columns get exposed silently (possible leak) and it hides whether the primary key is present, both threatening security and updatability.
Does WHERE control what you can write, or only what you can read? Only what you read — it hides rows from view but does not stop rows existing in the base table.
Why is AVG irreversible? Many different sets of numbers share the same average, so "set the average" has infinitely many possible base changes — no unique one.
What single question decides if a view is updatable? Does each view row map back to exactly one keyed row of one base table (with real columns you can change)?
Name the three write commands that must be translated to the base table. INSERT, UPDATE, DELETE.
