Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

The first time, many many years ago, that I've come across this odd peculiarity in a SQL statement, I was a little confused. After all, why would "1=1", which always evaluates to true have any place in a SQL statement? It seemed completely superfluous. Then, as I worked with SQL more, I started to appreciate the use of this redundant condition as a nice way of formatting certain types of SQL statements.

Since "1=1" evaluates to true all the time a SQL statement such as "SELECT 1 FROM DUAL WHERE 1=1" is functionally the same as "SELECT 1 FROM DUAL". We may as well have not had that clause there to begin with. This opens up an interesting possibility, if your SQL statement is only using AND conditions, you can add the "1=1" condition and not affect what the statement returns i.e. the highlighted rows of the logical conjunction table below are at play...
andlogic.png


So what? Well we can use this to our advantage with formatting SQL statements.

Now formatting of code is very much a subjective matter and I won't get into philosophical discussions of that. This post is just my own opinion on how to format a very specific kind of SQL statement and should not be treated as a general rule for formatting SQL. So let's see how I prefer my SQL to be laid out...Simply put - I want to see one and only one condition per line and have them align for readability.

So lets come up with a hypothetical SQL statement...
 SQL
SELECT *
FROM emp
WHERE sal > 1000000
AND deptno = 10


I find that a little difficult to read because of the way the conditions between the WHERE and the AND clauses do not line up. So we can do some shuffling around and messing about with spaces...
 SQL
SELECT *
FROM emp
WHERE sal > 1000000
AND deptno = 10


...or...
 SQL
SELECT *
FROM emp
WHERE sal > 1000000
AND deptno = 10




Neither is ideal in my opinion because adding spaces like that can become messy. So what I prefer is to use "1=1" in the WHERE clause and have the rest of my conditions using the AND clause, which will naturally line up...
 SQL
SELECT *
FROM emp
WHERE 1 = 1
AND sal > 1000000
AND deptno = 10


Effectively, the WHERE line is ignored. What about SQL statements that use a combination of AND and OR clauses? Well since applying the OR operator to an always true condition will evaluate to true all the time, this approach can't be used. It's possible to substitute "1<>1" in place of "1=1" if an OR clause is involved, but I think that is a little confusing and should be avoided.

-i

Skip down to comments...
Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
 
comments powered by Disqus
Other posts you may like...