What value lands in a column that an INSERT's column list omits?
answer
- the statement supplies nothing, so the table decides
- three outcomes, and one of them is an error
- supplying NULL is not the same as staying silent
- a keyword lets you ask for the default explicitly
basics
~20 sAn omitted column takes its declared DEFAULT; with no default it takes NULL if nullable, and the statement fails if it is NOT NULL with no default. Writing NULL explicitly is different — it overrides the default.
solid answer
~50 sOmitting a column from the list means the statement supplies nothing for it, so the table decides: the column's declared `DEFAULT` expression is evaluated, or `NULL` is stored if the column is nullable with no default, or the statement is rejected if the column is `NOT NULL` with no default and nothing generates a value for it. The trap is that `VALUES (..., NULL)` is *not* the same as omitting — an explicit NULL is a supplied value, so it overrides the default and violates a NOT NULL constraint. When you want the default in a statement that must still list the column (a multi-row VALUES where only some rows want it), write the `DEFAULT` keyword in that position. Standard SQL also has `INSERT INTO t DEFAULT VALUES` for a row that takes every column's default, though engines spell that special case differently.
go deeper
Know the everyday case: leave a column out and it gets whatever the table declares as its default, or NULL if it allows nulls. Do not assume it becomes zero or an empty string.
Be able to give the full three-branch rule and, crucially, distinguish omitting a column from passing an explicit NULL — that distinction is what the question is testing.
Demonstrate that you notice this in generated SQL: ORM and framework inserts that bind every mapped field turn unset values into explicit NULLs and quietly defeat the schema's defaults.
Take a position on where a value should be decided — in the schema's DEFAULT clause or in application code — and make it consistent, since split ownership is what produces rows whose defaults were never applied.
## The rule A column that an INSERT's column list does not mention is *not supplied by the statement*. Resolution then follows the table definition, in this order: 1. The column has a declared `DEFAULT` expression → that expression is evaluated and stored. 2. No default, but the column is nullable → `NULL` is stored. 3. No default and `NOT NULL`, with nothing else generating a value → the statement fails with a constraint or missing-value error. ```sql CREATE TABLE tickets ( id INT PRIMARY KEY, title VARCHAR(200) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'open', assignee VARCHAR(50), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); INSERT INTO tickets (id, title) VALUES (5, 'Cannot log in'); -- status = 'open', assignee = NULL, created_at = now ``` That statement is the everyday reason to write a column list at all: it lets the schema own the columns the schema should own. ## Omitting is not the same as writing NULL This is the distinction interviewers actually probe. `NULL` in a value list is a value you supplied — a real, known-to-be-unknown marker — so the default never enters the picture: ```sql INSERT INTO tickets (id, title, status) VALUES (6, 'Slow page', NULL); -- fails: status is NOT NULL, and the explicit NULL overrides DEFAULT 'open' ``` People hit this through application code and ORMs that build a full column list and bind whatever the object field holds; an unset field becomes an explicit NULL rather than an omission, and the column's carefully chosen default is never used. ## The DEFAULT keyword When the column has to appear in the list but you still want the table's default, write the keyword `DEFAULT` in that position: ```sql INSERT INTO tickets (id, title, status) VALUES (7, 'Typo on invoice', DEFAULT), (8, 'Card declined', 'urgent'); ``` This is exactly what a multi-row VALUES needs, since all rows share one column list but each row may want something different. `DEFAULT` is legal only in this position — it is not a general expression you can compare against or pass to a function. ## DEFAULT VALUES For a row that takes the default of every column, standard SQL offers a dedicated form: ```sql INSERT INTO tickets DEFAULT VALUES; ``` It inserts exactly one row with every column resolved as if it had been omitted, which of course still fails if some column is NOT NULL with no default and no generated value. It is genuinely useful for tables that are mostly identity plus timestamps — creating a session or a job row whose only content is its key. Not every engine accepts this spelling; some require a different construct for an all-defaults row, so check the dialect before putting it in portable code. ## Where the default value comes from A `DEFAULT` clause holds an expression, not just a constant: `DEFAULT CURRENT_TIMESTAMP`, `DEFAULT 0`, `DEFAULT 'pending'`. It is evaluated per row at insert time, so ten rows inserted in one statement each get a fresh evaluation of the expression. What the expression may contain (whether it can call arbitrary functions or reference other columns) varies by engine and is a schema-definition question rather than an INSERT question. ## A related subtlety: DEFAULT versus "the empty string" and zero A column with no default is not implicitly zero or empty — the absence of a value is NULL, which is not `0`, not `''`, and not false. Candidates who say "an omitted integer column becomes 0" are importing a habit from a programming language's zero-values, and it is one of the most common wrong answers here. ## How to answer Give the three-branch rule (default → NULL → error), then immediately draw the line between *omitting* a column and *supplying NULL* for it, because that distinction is what the question is really testing. Mention the `DEFAULT` keyword as the way to ask for the default when the column must stay in the list, and note `DEFAULT VALUES` as the all-defaults row, flagging that its spelling is not uniform across engines.
- How do you ask for a column's default when the column must appear in the list?Write the keyword `DEFAULT` in that value position: `INSERT INTO tickets (id, title, status) VALUES (7, 'Typo', DEFAULT)`. It is legal only as an entry in a row constructor, not as a general expression. This matters most in a multi-row VALUES, where all rows share one column list but individual rows may want the table's default.
- Why do ORM-generated inserts so often bypass a column's declared default?They typically build a full column list covering every mapped field and bind whatever the object holds, so an unset field is sent as an explicit NULL rather than being omitted. An explicit NULL is a supplied value and overrides the default, which then either stores NULL or trips a NOT NULL constraint. The fix is to leave the column out of the generated statement.
- What does INSERT INTO t DEFAULT VALUES insert?Exactly one row in which every column is resolved as if omitted: declared default, else NULL if nullable, else an error for a NOT NULL column with no default. It is handy for tables that are little more than an identity column plus timestamps. Engines differ on the spelling of an all-defaults row, so verify it on yours.
saying these in an interview costs you the question
- Says an omitted numeric column defaults to 0 or an empty string
- Treats an explicit NULL as equivalent to omitting the column
- Thinks omitting a NOT NULL column without a default is silently allowed
- Believes DEFAULT can be used as a general expression anywhere
- Assumes a DEFAULT clause is evaluated once at table creation