CODEWITHSUNDEEP
sql
Emanuil Rusev
Emanuil Rusev

Reputation: 35235

What does "WHERE 1" mean in SQL?

Sometimes phpMyAdmin generates queries like:

SELECT * 
FROM  `items` 
WHERE 1 
LIMIT 0 , 30

I wonder if WHERE 1 has any meaning in a query like that.

Upvotes: 26

Views: 40234

Answers (3)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

It doesn't. It means ALWAYS TRUE so it won't have any filtering impact on your query. Query planner will probably ignore that clause.

It's usually used when you build a client side query by concatenating filtering conditions.

So, if your base query is stored in a string like this (example is in PHP, but it certainly applies to many other languages):

$sql = "select * from foo where 1 ";

Then you can just concatenate a lot of filtering conditions with an AND clause regardless of it being the first condition you are using or not:

// pseudo php follows...
if ($filter_by_name) {
    $sql = $sql . " and name = ? ";
}
if ($filter_by_number) {
    $sql = $sql . " and number = ? ";
}
// so on, and so forth.

Upvotes: 47

flq
flq

Reputation: 22849

I'd guess it's a string-concatenation artefact: if no where conditions are specified, a "1" is output. That way it does not have to be decided whether the WHERE keyword should be output or not.

Also you can always output it and simply concatenate conditions with "AND" and "OR". You don't have to decide that the first condition should not start with AND, OR keyword.

Upvotes: 4

Paul Schreiber
Paul Schreiber

Reputation: 12589

WHERE 1 is a synonym for "true" or "everything."

It's a shortcut so they don't have to remove the where clause from the generated SQL.

Otherwise, you would write something like this:

$sql = "SELECT * FROM `errors`";
if ($hasWhereClause == true) {
  $sql .= " WHERE $whereClause";
}
$sql .= "LIMIT 0 , 30";

Upvotes: 8

Related Questions