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:

SELECT order.id
, customer.name
, product.name
, order.price
, order.status
, order.date_created
, order.date_updated
FROM order
JOIN customer
ON customer.store_id = order.store_id
AND customer.id = order.customer_id
JOIN product
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
LIMIT 50

It is a nice query, it’s almost there.

  • 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:

The screenshot shows a diff between the original code and the modified one. The modified code has the first line with WHERE commented, and the second line that originally started with an AND has the AND replaced by WHERE.

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:

The screenshot shows a diff between the original code and the modified one. The modified code now has a complete new line added and one removed

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:

The screenshot shows a diff between the original code and the modified one. The original one has WHERE followed by 1=1 and all conditionals have a trailing AND and stay in a single line. The modified code only adds a comment to the condition that needs the change

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store