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.

Community
  • 1
transistor1
  • 333
  • 1
  • 2
  • 8

3 Answers3

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.

JNK
  • 18,064
  • 6
  • 63
  • 98
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.

Mike Walsh
  • 18,278
  • 6
  • 50
  • 74
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)
Dalibor Karlović
  • 161
  • 2