How does a multi-row INSERT ... VALUES (1,2),(3,4) differ from the same rows inserted by separate statements?

## The construct ```sql INSERT INTO order_items (order_id, sku, qty) VALUES (101, 'A-1', 2), (101, 'B-7', 1), (102, 'A-1', 5); ``` Standard SQL calls the `VALUES` clause a *table value constructor*: it builds an anonymous table of literal rows, and `INSERT` inserts that table into the target. One row constructor per parenthesised group, commas between them. ## What is genuinely different from three statements **It is one statement.** That single fact produces every difference worth naming in an interview: - **One column list governs every row.** You cannot give row 1 three columns and row 2 four. The list is written once, before the `VALUES` keyword, and every row constructor must have the same number of values, positionally compatible with it. - **One row count comes back.** The client sees "3 rows affected", not three separate results. Anything in your code that inspects per-statement counts sees one number. - **All-or-nothing at statement level.** In standard SQL a data-change statement is atomic: if the third row violates a unique or NOT NULL constraint, the statement fails and *no* row from it is inserted, even though the first two were fine. Run the same three rows as three statements and rows 1 and 2 are in the table when row 3 fails — you are left with a partially applied load unless you wrapped them in a transaction yourself. Engines with non-transactional table types, or an explicit ignore-errors option, can deviate from this; check the engine you are on before relying on it. - **One parse, one round trip.** The statement is planned once instead of *n* times, and the client sends one message instead of *n*. This is why multi-row VALUES is the default shape for inserting a small known set of rows. ## What is *not* different The rows themselves are ordinary rows. Defaults, generated columns, constraints and triggers apply to each row exactly as they would from a single-row INSERT. The rows have no order once inserted — a table is not a sequence, and the order you wrote them in confers nothing on later queries. ## Per-row expressions and DEFAULT A value in a row constructor does not have to be a literal. Expressions and the `DEFAULT` keyword are allowed, and they can vary between rows: ```sql INSERT INTO audit_log (event, created_at) VALUES ('login', CURRENT_TIMESTAMP), ('logout', DEFAULT); ``` Here the second row asks the table for the column's declared default while the first supplies a value. That per-row flexibility is a real advantage over shapes that force one uniform template. ## Practical limits A multi-row VALUES list is bounded in practice: engines cap the number of placeholders or the statement size, and a single enormous statement is one long-running unit of work whose failure discards everything. For a handful to a few hundred rows it is the right tool; for bulk loading a large data set you would reach for a different mechanism, and for copying rows that already exist in the database you would use `INSERT ... SELECT` instead of shipping values out to the client and back. ## Portability note Multi-row `VALUES` in an `INSERT` is standard SQL and widely available, but it is not universal across every engine and version — some dialects historically required a different construct to insert several literal rows in one statement. If you are writing SQL that must run on more than one engine, verify the form there rather than assuming. ## The interview answer Say "it is one statement" first and then derive the consequences: one column list, one row count, all-or-nothing, one round trip. Candidates who only say "it is faster" have the least interesting half of the answer, and they miss the failure semantics that actually change how you write the code around it.