sql – MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

The Question :

264 people think this question is useful

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET?

Example:

INSERT INTO table (a, b, c) VALUES (1,2,3)

INSERT INTO table SET a=1, b=2, c=3

And what about performance of these two?

The Question Comments :
  • After reading Code Complete and McConnell’s constant emphasis on readability, it seems unfortunate that INSERT INTO table SET is not standard. It seems much clearer. I guess I’ll have to use the INSERT INTO table ([column name, column name b]) VALUES (['value a', 'value b']) syntax anyway though to save myself from trouble if I port over to Postgres.

The Answer 1

203 people think this answer is useful

As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL’s extension.

So they should be exactly equivalent performance wise.

http://dev.mysql.com/doc/refman/5.6/en/insert.html says:

INSERT inserts new rows into an existing table. The INSERT … VALUES and INSERT … SET forms of the statement insert rows based on explicitly specified values. The INSERT … SELECT form inserts rows selected from another table or tables.

The Answer 2

15 people think this answer is useful

I think the extension is intended to allow a similar syntax for inserts and updates. In Oracle, a similar syntactical trick is:

UPDATE table SET (col1, col2) = (SELECT val1, val2 FROM dual)

The Answer 3

5 people think this answer is useful

Since the syntaxes are equivalent (in MySQL anyhow), I prefer the INSERT INTO table SET x=1, y=2 syntax, since it is easier to modify and easier to catch errors in the statement, especially when inserting lots of columns. If you have to insert 10 or 15 or more columns, it’s really easy to mix something up using the (x, y) VALUES (1,2) syntax, in my opinion.

If portability between different SQL standards is an issue, then maybe INSERT INTO table (x, y) VALUES (1,2) would be preferred.

And if you want to insert multiple records in a single query, it doesn’t seem like the INSERT INTO ... SET syntax will work, whereas the other one will. But in most practical cases, you’re looping through a set of records to do inserts anyhow, though there could be some cases where maybe constructing one large query to insert a bunch of rows into a table in one query, vs. a query for each row, might have a performance improvement. Really don’t know.

Add a Comment