4

Which one is better for performance

IF EXISTS(Select null from table)

or

IF EXISTS(Select 1 from table)

?

Igor Suhotin
  • 41
  • 1
  • 2
  • 5
kamaci
  • 72,915
  • 69
  • 228
  • 366

1 Answers1

18

Both perform the same, because the SELECT clause in the EXISTS is never evaluated. You can test using:

... EXISTS(SELECT 1/0 FROM TABLE)

That should trigger a divide by zero error, but won't.

I personally prefer using NULL because it's obvious that nothing is referenced in the table, so it's more visible to others. Selecting a value, like the INT number 1 in the second example, can lead to assumptions about what is happening if not familiar with the EXISTS clause.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Thanks for your comment. By the way lets accept that TABLE has 10 rows and I run a query: SELECT 120/2 FROM TABLE. It returns a table that the column name is 120/ and all the rows has 60.00 value. So it looks like MySQL executes it? Because 120/2 has found as 60.00 but why 1/0 doesn't trigger an error. It writes null at every row for that example so it means that it has evauletad but not triggered? – kamaci Feb 02 '11 at 15:58
  • 1
    division by zero is not considered an error in MySQL. It simply returns NULL –  Feb 02 '11 at 17:11
  • Funny you should mention that... http://stackoverflow.com/questions/3271455/whats-the-best-to-check-if-item-exist-or-not-select-countidor-exist/3271464#3271464 – gbn Feb 02 '11 at 18:23
  • @kamaci: `SELECT 120/2 ...` is not touching the table data itself, nor is that within an EXISTS clause. Also, if you have a specific database in mind -- specify it, because behavior can be very different between them. – OMG Ponies Feb 02 '11 at 18:36
  • I just wanted to mention that it can be evulated it doesn't matter it returns null or trigger an error. If it returns null in MySQL, it means that it "calculates" but doesn't trigger an error so it "evulates". Doesn't it? – kamaci Feb 03 '11 at 07:52
  • I am accepting it as an answer but I should add that operation within select is executed. Even it doesn't trigger an error it calculates it. – kamaci Oct 29 '11 at 21:54