How does INSERT ... SELECT match the query's result columns to the target table's columns?

## The form ```sql INSERT INTO order_archive (order_id, customer_id, total) SELECT o.id, o.customer_id, SUM(i.qty * i.unit_price) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.closed_at < DATE '2024-01-01' GROUP BY o.id, o.customer_id; ``` The query takes the place of the `VALUES` clause. There is no `VALUES` keyword — the two forms are alternatives, and you cannot mix them in one statement. ## Matching is positional, and only positional The *n*-th expression of the select list is inserted into the *n*-th column of the INSERT's column list. The select list's column names and aliases are irrelevant: naming an expression `AS total` does not steer it toward a column called `total`. Two rules must hold: - **Degree.** The select list must have exactly as many expressions as the column list has columns. - **Type compatibility.** Each pair must be assignable; the engine applies the same assignment rules it would to a literal. Which means the dangerous case is a pair of same-typed columns in the wrong order: ```sql -- both INT: this succeeds, and every row is stored backwards INSERT INTO order_archive (order_id, customer_id) SELECT customer_id, id FROM orders; ``` Nothing is violated, so nothing complains. The defence is to keep the two lists visually aligned, one column per line, and to read them as a pair during review. ## Any query is a legal source The source is a full query expression, so it may include joins, `GROUP BY`, `DISTINCT`, `ORDER BY`, set operations, window functions, common table expressions, and even a bare `VALUES` constructor. That generality is the point: `INSERT ... SELECT` moves rows inside the database instead of pulling them to the client and pushing them back, and it can transform them on the way — computing a total, mapping a status, stamping a literal: ```sql INSERT INTO order_archive (order_id, total, archived_by) SELECT id, total, 'nightly-job' FROM orders WHERE closed_at < CURRENT_DATE - 30; ``` A constant in the select list is an ordinary expression and needs no special syntax. ## Defaults still apply Columns absent from the INSERT's column list behave exactly as they do in a VALUES insert: declared default, else NULL if nullable, else an error. So an archive table with `archived_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP` gets its stamp without the query producing one. ## Zero rows is a success If the source query returns no rows, the statement inserts nothing and succeeds with a row count of zero. This is correct and often exactly what you want, but it means a typo in the `WHERE` clause, a join that eliminates everything, or a source table that is empty in this environment all present as "the job ran fine". Jobs built on `INSERT ... SELECT` should check the reported row count rather than only the absence of an error. ## ORDER BY in the source You may write `ORDER BY` in the source query, and engines generally accept it, but a table has no row order, so it confers nothing on later reads of the target — a subsequent `SELECT` without its own `ORDER BY` may return the rows in any order. Treat any effect it seems to have as an artifact of a particular engine, not as a guarantee of the language. ## Degenerate but legal shapes `INSERT INTO t (a, b) SELECT * FROM ...` is legal but re-introduces exactly the positional fragility that a column list was supposed to remove, since `*` expands to whatever the source table currently has. Naming the source expressions costs nothing and keeps the statement stable. The source may also be a `VALUES` constructor used as a query, which is how you write a small literal load that is then filtered, joined, or ordered — the same table value constructor a multi-row `VALUES` clause uses. ## What interviewers look for Three things: that you say **positional, not by name**, that you name the silent-swap failure mode, and that you know an empty source is a success rather than an error. A candidate who adds that omitted columns still take defaults, and that any query — join, aggregate, CTE — can be the source, is comfortably above the bar.