INSERT — Adding Rows
A new customer signs up on the shop's website. Somewhere behind the sign-up button, one statement turns her form into a row in customers. That statement is INSERT, and writing it well is mostly about one habit: always saying which columns you are filling.
The rule
Name the columns, then give the values in that same order.
INSERT INTO customers (customer_id, name, city, email)
VALUES (5, 'Nisha', 'Chennai', 'nisha@example.com');The first bracket lists columns; the second lists values, matched by position. customer_id gets 5, name gets 'Nisha', and so on. Text and dates go in single quotes; numbers do not.
Any column you leave out gets its DEFAULT, or NULL if it has no default. Here joined_on was not mentioned, so it gets whatever default the table defines — or NULL. If a left-out column is NOT NULL with no default, the insert fails.
You can insert several rows in one statement, which is faster than many single inserts:
INSERT INTO products (product_id, name, category, price)
VALUES (1, 'Notebook', 'stationery', 60.00),
(2, 'Gel pen', 'stationery', 25.00),
(3, 'Desk lamp', 'home', 899.00);And you can insert the result of a query instead of literal values:
INSERT INTO pune_customers (customer_id, name)
SELECT customer_id, name FROM customers WHERE city = 'Pune';It is legal to skip the column list — INSERT INTO customers VALUES (…) — but then you must supply every column in the table's exact order, and the statement silently breaks if someone later adds or reorders a column. Always name the columns.
Many tables generate their own ids (for example GENERATED ALWAYS AS IDENTITY in PostgreSQL or AUTO_INCREMENT in MySQL). In that case, leave the id column out and let the database fill it.
The shortcut
Line the two brackets up vertically in your head and count: the number of columns must equal the number of values, and each pair must make sense.
The statement
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date) VALUES (106, 3, 2, 4, '2026-03-05');
Drills
Answer fast — trust the rule.
INSERT INTO customers (customer_id, name, city) VALUES (6, 'Arjun'); — what happens?
A table has status VARCHAR(20) DEFAULT 'pending'. An INSERT does not mention status. What is stored?
Why is INSERT INTO customers VALUES (7, 'Dev', 'Goa', 'dev@example.com', '2026-03-01'); risky in application code?
Which statement copies every Pune customer's id and name into an existing pune_customers table?
Remember
- Name the columns, then give the values in that same order.
- Count both brackets; they must match one for one.
- Left-out columns get DEFAULT or NULL — and a NOT NULL column with no default makes the insert fail.