Using the always true 1=1 condition to help with SQL statement formatting

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...

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