26

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.

Emanuil Rusev
  • 34,563
  • 55
  • 137
  • 201
  • 5
    The reason for this is quite simple - if you're dynamically generating a query string, it's easier to state it's always true than it is to have a special case that rearranges the statement to remove the 'WHERE' clause when there's no need for it. – Rushyo Sep 15 '10 at 18:49
  • 2
    Especially in PHP, it's common to construct SQL statements by concatenating strings together. So presumably in phpMyAdmin they had $sql = "SELECT * FROM `item` WHERE " . $whereClause where they defined the $whereClause variable elsewhere. One (crude) way to solve the problem where you don't actually want a where clause is to use this technique. – Kirk Woll Sep 15 '10 at 18:50
  • phpmyadmin will also always use LIMIT, even when there's no need for it, for the very same reason. – o0'. Sep 15 '10 at 19:10

3 Answers3

47

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.
Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
8

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";
Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
4

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.

flq
  • 22,247
  • 8
  • 55
  • 77