Where 1=1

Back when I first started writing SQL I was taught a valuable trick that I always get questioned about anytime I give a query to someone. The statement? “where 1=1”

Let’s think about. Most queries start with one table and expand outwards. Take this example.

Select *
From order_header

Simple enough. Now let’s add another table.

Select *
From order_header oh
,order_line ol
Where oh.oh_id = ol.oh_id

Still that’s not so bad. It gets interesting though when you get to three tables.

Select *
From order_header oh
,order_line ol
,order_detail od
Where oh.oh_id = ol.oh_id
and ol.ol_id = od.ol_id

Now we are cooking. So you working along and say you only want to see orders from a certain date range. Your query turns into this:

Select *
From order_header oh
,order_line ol
,order_detail od
Where oh.oh_id = ol.oh_id
and ol.ol_id = od.ol_id
and oh.creation_date > ‘1-JAN-10’

Still everything is straight forward. But what if you all of a sudden want to see just the order line and order detail table. Using comments you need to do the following.

Select *
From order_line ol
,order_detail od
–Where oh.oh_id = ol.oh_id
where ol.ol_id = od.ol_id
and oh.creation_date > ‘1-JAN-10’

So you had to comment the original where clause, add a new where clause and remove the order_header table. That’s three steps. Now if you are like me you are always changing the parameters of a query and when you are humming along you don’t want to have to take time to do unnecessary work. So what if you wrote your original query like this.
Select *
From order_header oh
,order_line ol
,order_detail od
Where 1=1
and oh.oh_id = ol.oh_id
and ol.ol_id = od.ol_id
and oh.creation_date > ‘1-JAN-10’

If you wanted to make some more changes now all you have to do is comment a line within the “and” clauses. You don’t have to re-write “where”. You can just use comments.

Select *
From order_line ol
,order_detail od
Where 1=1
–and oh.oh_id = ol.oh_id
and ol.ol_id = od.ol_id
and oh.creation_date > ‘1-JAN-10’

Now this is a pretty basic example but anytime I write a query I always start with where 1=1. When I get to more complicated queries it just saves time. As you comment and uncomment code don’t you think it would be easier just to comment that first clause and not re-write “where”?

My final query is a great example of how when searching for data it’s helpful to have 1=1. Sometimes I want to search by table names and sometimes I want to search by owner. It’s simple to comment the “and” clauses and move on to your next task. Everything in the name of efficiency!

SELECT DISTINCT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE 1=1
–AND TABLE_NAME LIKE ‘%USE%’
AND OWNER LIKE ‘ %QAM%’
ORDER BY OWNER ASC