Since some time I have been adding
WHERE 1=1 to all my queries.
I get queries like this:
SELECT * FROM emp e WHERE 1=1 AND e.ename LIKE 'A%' AND e.deptno = 20
Lots of people ask me what’s the use of this
You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a lot easier.
If my query has a lot of predicates and I want to see what happens then I usually comment those predicates out by using — (two dashes). I use my own CommentLine plug-in for this. This is easy for the second and higher predicates. But if I want to comment out the first predicate, then it get a bit harder. Well, not harder, but more work.
If I didn’t use the
WHERE 1=1 and I wanted to comment out the ename predicate then I would have to do something like this:
SELECT * FROM emp e WHERE /*e.ename LIKE 'A%' AND */e.deptno = 20
I agree, it’s not hard to do, but I think it’s a lot more work than just adding — (two dashes) in front of a line:
SELECT * FROM emp e WHERE 1 = 1 -- AND e.ename LIKE 'A%' AND e.deptno = 20
And, as I don’t like typing or at least, I want to make it as easy for me as possible, I am using another one of my plug-ins, Template, where I defined a template
w1 which results in
WHERE 1 = 1 AND
making it easy for me to write the queries.
I think adding this extra predicate has no (or hardly any) influence on the execution time of the query. I think the optimizer ignores this predicate completely.
I hope this explains a bit why I write my queries like this.