SQL WHERE Clause condition evaluation

Question

I have an easy question, however I couldn't find the answer for this. In SQL WHERE clause which is better?

TO_CHAR (DAT, 'YYYYMMDD') BETWEEN '20080101' AND '20131231'

or

DAT BETWEEN TO_DATE('20080101','YYYYMMDD') AND TO_DATE('20131231','YYYYMMDD')

Are the condition values evaluated only once and then tested for every row in the table, or does the SQL engine recalculate it every time?

score 1 · Accepted Answer · edited Mar 08 '19 at 10:47

1

Any argument that involves constants and literals will only be evaluated once. The second, however, is much better - it allows you to index the dat column and then use this index to improve performance, while the first query will not allow the index to be used.

edited Mar 08 '19 at 10:47

Daniel E.

2,440
1
14
24

answered Dec 13 '14 at 15:20

Mureinik

297,002
52
306
350

Yeah, that's what I thought! Thank you! – user2346329 Dec 13 '14 at 15:54
"The second is much better" … "while the second one will not", I guess it should be "first" in the second phrase. If someone with edit rights (or even OP, if he sees this) could correct the sentence that would be great :) – bugybunny Dec 07 '18 at 12:08

score 1 · Answer 2 · answered Mar 08 '19 at 11:18

And here's the BEST :

WHERE DAT BETWEEN '2008-01-01' AND '2013-12-31'

SQL has literals for date/time types too so there just isn't any need to invoke any of these scalar functions.

BTW you tagged the question SQL. That means your question relates to standard SQL, not to any particular engine and/or its implemented dialect (hover over the tag and read what it says). The standard mandates the date format used in this example. Specific engines might support additional formats for date literals, e.g. '12/31/2013' or '31.12.2013'.

SQL WHERE Clause condition evaluation

2 Answers2