What is the purpose of using WHERE 1 1 in SQL statements

0 votes

Possible Duplicates:
Why would a sql query have “where 1 = 1”
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?

That is a common occurrence in various query instances, and it probably applies to all SQL engines.

People (and especially ORM frameworks) frequently add the always-true condition WHERE 1 = 1 or something similar to a query if there are no conditions declared for it.

So instead of:

SELECT id, name FROM users;

They use:

SELECT id, name FROM users WHERE 1 = 1;

The only reason I can think of for this is if you add conditions dynamically, in which case you don't have to worry about the initial AND being stripped, however, it still happens very frequently that the 1 = 1 condition is stripped if there is an actual condition in the query.

An actual example from CakePHP (generated by the framework):

(no conditions)

SELECT `User`.`id`, `User`.`login`
FROM `users` AS `User` WHERE 1 = 1 
ORDER BY `User`.`id` ASC;

(with the condition)

SELECT `User`.`id`, `User`.`login`
FROM `users` AS `User` 
WHERE `User`.`login` = 'me@example.com'
LIMIT 1;

Is there any reason for adding that extra condition?

Aug 28, 2022 in Database by Kithuzzz
• 38,000 points
2,773 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes
It's also a typical habit to start with "where 1=1" and then append " and the customer when generating the SQL query programmatically because it's just simpler. id=:custId' based on whether a customer id is supplied. Therefore, you can always add the query's second portion, which should begin with "and..."

I hope this helps you.
answered Aug 29, 2022 by narikkadan
• 86,360 points

edited Mar 5