Sometimes I encounter query which have a WHERE clause that starts with:
select ... from ... where 1=1 and ...
When asked the reason for this predicate, most of the time (if not all) “Because it’s easy if you want to comment out some of the conditions”. Say you have a query that looks like this:
select t.* from t where col1 = 'value' and id = 3 ;
And you want to comment out the first condition (col1 = ‘value’), you would have to rearrange the whole query. Simply commenting out the WHERE condition would lead to this statement and this is not valid syntax:
SQL> select t.* 2 from t 3 and t.id = 3 4 / and t.id = 3 * ERROR at line 3: ORA-00933: SQL command not properly ended
The other day I noticed something weird with Joins, ANSI style. Let me show you what I found.
First off, let’s create a couple of simple tables. Both of them have a single column with values ranging from 1 through 10.
create table t as select rownum id from all_objects where rownum <= 10; create table s as select * from t;
Now lets join these two tables using the ANSI style join syntax:
select t.* , s.* from t join s on (t.id = s.id)
And add a couple of predicates.
where t.id = 3 and s.id = 3
Now lets comment out the WHERE clause, and lo and behold… the query still runs.
SQL> select t.* 2 , s.* 3 from t 4 join s 5 on (t.id = s.id) 6 ---- where t.id = 3 7 and s.id = 3 8 / ID ID ---------- ---------- 3 3 SQL>
So, even if the WHERE keyword is missing, it doesn’t seem to be a problem. At least not on Oracle 9iR2 or Oracle 10gR2.
Whether this is a bug or a feaure, I don’t know…
Reponse to Borkur Steingrimsson
Well, I sure didn’t expect it 😉 But it makes sense that the ON-condition is extended on the next line, now that you mention it.
Just to reproduce your query (as it doesn’t look that great in the comment section)
SQL> select 2 * 3 from t 4 join s 5 USING (id) --Notice the difference here 6 â€”- where t.id = 3 7 and s.id = 3 8 /
I just tried out this query, with and without the WHERE clause, but both failed. Appearently the USING clause also extends to the next line, outside of the parenthesis.
SQL> with t as 2 (select rownum id 3 from all_objects 4 where rownum <= 10 5 ) 6 , s as 7 (select * 8 from t 9 ) 10 select * 11 from t 12 join s 13 USING (id) ----Notice the difference here 14 where t.id = 3 15 and s.id = 3 16 / and s.id = 3 * ERROR at line 15: ORA-25154: column part of USING clause cannot have qualifier SQL> SQL> ed Wrote file afiedt.buf 1 with t as 2 (select rownum id 3 from all_objects 4 where rownum <= 10 5 ) 6 , s as 7 (select * 8 from t 9 ) 10 select * 11 from t 12 join s 13 USING (id) ----Notice the difference here 14 ---- where t.id = 3 15* and s.id = 3 SQL> / and s.id = 3 * ERROR at line 15: ORA-00933: SQL command not properly ended
Comment by Niall Litchfield (but nicely formatted)
The comment out issue is one reason why I use the writing style
SELECT first_col , second_col ... ... , last_col FROM first_tab join second_tab on (join clause) WHERE first_condition and second_condition and ... GROUP BY first groupby , second groupby ... HAVING first having , second having ... ORDER BY first_col , second_col
i.e KEYWORDS on their own, start lines with a , (if appropriate) I also find it more readable.
Incidentally many sql generators – programs that generate sql based on forms etc – will start the where with 1=1 so they don’t have to work out which is the first condition.