Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).

Generally speaking, does this addition to a SQL statment negatively affect query performance? I'm not looking for an answer in regard to a specific database system (because I have used it in DB2, SQL Server, MS-Access, and mysql)-- unless it's impossible to answer without getting into specifics.

share|improve this question
4  
I believe any optimizer would be able to handle such simple condition and simply ignore it so final execution plan would not contains it at all –  sll Nov 16 '11 at 16:15
    
I would think so too-- logically speaking, it seems to make sense that in general a query optimizer would simply ignore it. –  transistor1 Nov 16 '11 at 16:23
5  
You could compare the executions plan wtih and without 1=1 –  Luc M Nov 16 '11 at 16:31
4  
@Luc M: I did just that for SQLite. Turns out it doesn't optimize away the WHILE 1=1 clause. However, it doesn't seem to have any detectable impact on execution time. –  dan04 Nov 16 '11 at 16:37
1  
+1 for getting your hands dirty! –  transistor1 Nov 16 '11 at 16:41
add comment

migrated from stackoverflow.com Nov 16 '11 at 17:55

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 19 down vote accepted

All the major RDBMS, as far as I know, have built in constant evaluations. This should evaluate pretty much instantaneously in any of them.

share|improve this answer
    
+1 This has been my guess as well, but the reason I asked the question was to get a little more detail. I'm going to keep it open a little longer to see if I get any more input. –  transistor1 Nov 16 '11 at 18:53
2  
This is ignored. It is nothing to with the optimiser, just concat of where condituon as per link in question (my answer too) –  gbn Nov 16 '11 at 18:57
add comment

From a SQL Server Perspective if you are doing the WHERE 1=1 to allow for dynamic passing of parameters and skipping a parameter from being evaluated, I would suggest you read a couple articles from SQL Server MV Erland Sommarskog. His approach removes the need to do some other tricks inside of dynamic SQL (like the WHERE Column = Column construct or using a WHERE (Col = Val OR 1=1) and (Col2 = Val2 OR 1=1) construct). The the 1=1 shouldn't cause performance issues as @JNK mentioned (I've +1'd his answer there and that is the one that should be accepted), I think you'll find some good tips from Erland's article around Dynamic SQL and you'll also see he still uses the one 1=1 for the cases where no parameters are passed but he avoids them for individual parameters that aren't passed, he simply doesn't mention them in the resulting where clause at all.

share|improve this answer
    
I'm just browsing the second article (because I'm not writing code for 2008 SP1 at the moment), but I see he is using 1 = 1 in his code. I'm already familiar with sp_executesql, but that doesn't eliminate the push to use 1=1, in and of itself. Maybe I'm missing something? –  transistor1 Nov 16 '11 at 18:39
2  
+1 - Erland is the go-to resource on this sort of thing. –  JNK Nov 16 '11 at 18:40
    
Just quoting from the second link: "On lines 19-29, I compose the basic SQL string. The condition WHERE 1 = 1 on line 29 is there to permit the users to call the procedure without specifying any parameters at all." –  transistor1 Nov 16 '11 at 18:49
2  
Sorry. I mis-typed my point. Will edit. I didn't mean to imply there is a problem with the Where 1=1 construct, just suggesting other tips for readability and hopefully avoiding doing the approach of WHERE (column = value or 1=1) and (column1 = value1 or 1=1), etc. approach. –  Mike Walsh Nov 16 '11 at 19:13
add comment

With MySQL, you can check, running EXPLAIN EXTENDED and later SHOW WARNINGS to see the actual query. tl;dr: it gets optimized away.

mysql> use test
Database changed
mysql> create table test1(val int);
Query OK, 0 rows affected (0.19 sec)

mysql> explain extended select * from test1 where val > 11 and 1 = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                    |
+-------+------+--------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`test1`.`val` AS `val` from `test`.`test1` where (`test`.`test1`.`val` > 11) |
+-------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.