286

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Irmantas
  • 3,331
  • 2
  • 18
  • 10
  • 14
    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. – cluelesscoder Feb 29 '12 at 01:25

3 Answers3

217

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.

Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
  • 8
    How do you INSERT multiple values using `INSERT INTO table SET`? Is this even possible? – pixelfreak Jun 08 '12 at 22:42
  • 4
    What do you mean? The example the OP has says SET a=1, b=2, c=3 which is multiple values in my understanding. – Vinko Vrsalovic Jun 10 '12 at 09:40
  • 13
    I meant, INSERT multiple rows. Like: INSERT INTO table (a, b, c) VALUES (1,2,3), (4,5,6), (7,8,9); – pixelfreak Jun 11 '12 at 03:27
  • 8
    Only INSERT statements that use VALUES syntax can insert multiple rows. – Vinko Vrsalovic Jun 11 '12 at 07:17
  • 8
    @VinkoVrsalovic, not true, insert select can also insert multiple rows when multiple rows are selected – Pacerier Oct 13 '12 at 12:52
  • 1
    @Pacerier True! I was implicitly assuming user supplied values, not the results of another query. – Vinko Vrsalovic Sep 03 '19 at 13:26
  • If it's equal I'd stick to VALUES. In my class here people seem to like new stuff even if there is no benefit. When you stick to the standards you will most likely never do anything wrong. Everything else is just fancy unless there is nothing alike. Just because it's different doesn't mean it's 'cool' but worth to know. – Thielicious Jan 30 '20 at 08:41
  • 1
    "*people seem to like new stuff*" ... ? New ? I've been using SET syntax in all my INSERT/UPDATE for 20 years now. Longer than most kids have existed today. Its hardly a new concept, nor a fad method. – IncredibleHat Jul 20 '20 at 15:49
  • 1
    Really it depends on how you have the data at hand. If it is an array of key, values then the SET method, if it is an array of headers and an array of data the VALUES is the one. – ThurstonLevi Apr 22 '21 at 11:30
  • Using Insert and Set will make ON DUPLICATE KEY UPDATE not work. Documentation https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement .... The effects are not quite identical: For an InnoDB table where a is an auto-increment column, the INSERT statement increases the auto-increment value but the UPDATE does not. – blumanski Jan 21 '22 at 04:54
15

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)
fthiella
  • 48,073
  • 15
  • 90
  • 106
  • 5
    @Pacerier Such as? The only problem I see is a portability one (which, in many contexts doesn't really matter); am I missing something? – Mark Amery Jan 29 '13 at 13:56
  • 1
    @MarkAmery, yep when you look at it, there's no real benefit. The disadvantage is **unnecessary time wasted**, the whole existence of this thread proves my point. – Pacerier Jan 29 '13 at 14:39
  • 8
    @Pacerier I'm not sure I do see your point? What time wasted? There is a benefit, which has already been pointed out: you need only loop over an array of key/value pairs once to create your INSERT statement, instead of twice as you would need to using the VALUES syntax, which leads to shorter, clearer, and faster-to-write code. – Mark Amery Jan 29 '13 at 14:44
  • @MarkAmery but this oracle trick doesn't *have* that benefit. You name all the columns first, then all of the values. – hobbs Feb 02 '13 at 02:12
  • @hobbs I assumed he was referring to the MySQL extension that is the topic of the question. Perhaps I misunderstood. I know nothing about Oracle. – Mark Amery Feb 02 '13 at 11:40
  • @MarkAmery, Yes you do not see what I was getting at. These extra non-interoperable features are wasting our time when we need to migrate. And in the tech world, that **happens**. Tons of developer time is wasted filtering out the intricacies of special features that are only nice-to-have and which offers no real benefit. This thread already has 157k views; tell me, **how many hours of developer time could be saved if MySQL did not create the new `insert set` syntax?** – Pacerier Oct 18 '14 at 22:49
  • 14
    @Pacerier That's a fair point, and there's a tradeoff to be weighed. Against the feature you have portability problems and time wasted researching the difference between `INSERT ... SET ...` and `INSERT ... VALUES ...`. For the feature you have shorter, faster-to-write code, increased readability, and the elimination of typos caused by mixing up your column order when writing your `VALUES` clause. My gut tells me that on net the good outweighs the bad, but your judgement may differ. – Mark Amery Oct 18 '14 at 23:01
  • I know this is old but I just want to point out how bad this syntax is at first sight. I can see this causing confusion. – Mark Carpenter Jr Jan 26 '18 at 20:41
  • @Parcerier: Here's one where this syntax comes in handy. Don't see how that's possible with the 'VALUES' approach, unless I'm missing something. https://stackoverflow.com/questions/17893691/how-to-insert-point-data-into-mysql-using-pdo-bindparam – guido Jun 26 '18 at 16:49
8

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.

Aaron Wallentine
  • 2,318
  • 24
  • 22