INSERT

## The two forms SQL accepts an INSERT with or without a list of target columns: ```sql -- named (explicit) form INSERT INTO accounts (id, email, status) VALUES (7, '[email protected]', 'active'); -- positional form INSERT INTO accounts VALUES (7, '[email protected]', 'active'); ``` In the named form, the *n*-th value in the row constructor is bound to the *n*-th column **of the list you wrote**. In the positional form there is no list, and the statement is defined as if you had written every column of the table, in the order the table declares them. The positional form therefore has a hidden dependency: the statement only means what you intended as long as the table's column order is what you assumed when you typed it. ## Why the hidden dependency bites Column order is not a stable property of a table over a system's lifetime. `ALTER TABLE ... ADD COLUMN` appends a column; a table rebuilt from a dump, recreated by a migration, or restored from an export can end up with a different order than the one in the developer's head; two environments can drift apart. Every positional INSERT in the codebase is silently re-interpreted when that happens. The failure mode splits in two: - **Types clash.** You are lucky: the statement fails at once with a conversion or arity error, and someone fixes it. - **Types are compatible.** You are not lucky. Two `VARCHAR` columns swapped, or two integers swapped, are accepted without a murmur, and the wrong data sits in the wrong column until a report looks strange weeks later. Nothing in the database is violated — you told it to put these values in these positions, and it did. The named form removes the dependency entirely. `INSERT INTO accounts (id, email, status) VALUES (...)` means the same thing after any additive schema change, because the binding is by name. ## The list's order is what counts, not the table's A common misreading is that the column list must follow the table's declaration order. It need not: ```sql -- table t(a INT, b INT, c INT) INSERT INTO t (c, a) VALUES (1, 2); -- stores a = 2, c = 1, b gets its default (NULL here) ``` The list is the mapping. Anything absent from it is not supplied by the statement at all. ## Naming columns is what makes defaults usable Omitting a column from the list is how you say "let the table decide": the column takes its declared `DEFAULT`, or `NULL` if it is nullable with no default, or the statement fails if it is `NOT NULL` with no default. That is exactly what you want for audit timestamps, status columns with a sensible default, and generated identity columns. The positional form gives you no way to express "skip this one" — you must supply a value for every column, and engines differ on whether they tolerate a value list shorter than the table (some fill the rest with defaults, others reject the statement as an arity mismatch), which is another reason not to depend on it in portable code. ## Readability and review A named INSERT is self-describing: a reviewer reading the diff can tell that `'active'` is a status without opening the DDL. A positional INSERT of eight values is a puzzle, and a reviewer cannot spot a swapped pair at all. The same argument applies to test fixtures and seed scripts, which are read far more often than they are written. ## When the short form is defensible Ad-hoc exploration in a console, or a throwaway fixture sitting three lines below the `CREATE TABLE` that defines it, is not going to hurt anyone. The rule is about statements that live in source control, migrations, application code and jobs — anything that will still be running after the next schema change. Even there, naming the columns costs one line and buys immunity. ## What interviewers are checking This is a screening question about whether you write deliberately or by positional luck. The strong answer names the silent-corruption case (compatible types swapping) rather than only the error case, mentions defaults as the second reason, and notes that the list's own order is the mapping.