What is this 1=1 doing here?
I’ve been working with SQL for almost 10 years now and everywhere I go it seems that I leave a trail of 1=1, and I get this question sometimes.
What is this 1=1 doing here? Is it necessary?
It’s not strictly necessary but it’s there for convenience, to simplify the process of updating queries, especially during debugging or on the initial development of a new query, when adding, removing, and consistently modifying conditions makes for a much faster process.
Let's take the query below as an example:
ON customer.store_id = order.store_id
AND customer.id = order.customer_id
ON product.store_id = order.store_id
AND product.id = order.product_id
WHERE order.store_id = 1
AND order.date_created >= NOW()::DATE - '7 DAYS'::INTERVAL
AND product.category IN ('cat1', 'cat2')
AND product.status = 'pending'
ORDER BY order.date_created DESC
It is a nice query, it’s almost there.
- It has a large number of fields to select so they are each one in a single line with a leading comma
- Each condition for the joins takes its line too
- The tables in the query have short names already so no aliases are added. Aliases with only a couple of characters that don’t have much meaning are a very common practice but it only makes for a less readable query.
But let's say that we want to temporarily remove the condition “order.store_id = 1” to see the orders of all stores, the change would go something like this:
Not good. In this case, having to replace AND for WHERE is so inconvenient to toggle comment on/off that would be more practical to remove the conditional completely, leaving something like:
This is again not great, because you need to modify the line that you care about and another one that has nothing to do with your change and you removed completely the code for your conditional
When we introduce 1=1 after WHERE, this change requires almost no effort, I can just use the shortcut from my editor to toggle comment on and off:
This approach makes this change effortless. I can toggle comment, I can move the order of the conditionals and if I decide to ship this change, instead of a comment I can delete a single line of code and my PR will look much more clear on what exactly was changed.
SQL can be quite difficult to understand and maintain at times but having the code nicely formatted makes a world of difference for developers that spend any time doing this type of work.