20

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.

4
  • 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, 2011 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, 2011 at 16:23
  • 6
    You could compare the executions plan wtih and without 1=1
    – Luc M
    Nov 16, 2011 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, 2011 at 16:37

3 Answers 3

24

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

2
  • +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. Nov 16, 2011 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, 2011 at 18:57
8

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.

4
  • 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? Nov 16, 2011 at 18:39
  • 2
    +1 - Erland is the go-to resource on this sort of thing.
    – JNK
    Nov 16, 2011 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." Nov 16, 2011 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, 2011 at 19:13
6

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)
1
  • 1
    Great answer. Btw MySQL server v5.7.18 says 'EXTENDED' is deprecated and will be removed in a future release. From mysql doc: In older MySQL releases, extended information was produced using EXPLAIN EXTENDED. That syntax is still recognized for backward compatibility but extended output is now enabled by default, so the EXTENDED keyword is superfluous and deprecated. Its use results in a warning, and it will be removed from EXPLAIN syntax in a future MySQL release. It was removed in MySQL v 8.0.
    – mikep
    Mar 9, 2019 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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