Is it poor practice to build an SQL query using WHERE 1=1 AND ...

Question

I'm writing a PHP script that builds an SQL query by concatenating the string and adding conditions to the WHERE clause as needed.

Would it be better practice to use WHERE 1=1 so that the first condition is satisfied and the script can just concatenate an AND x = 'y' to the query, or should I write the extra code to check if a clause has been added and if not, add the AND ?

The first solution allows for cleaner code in the script but just seems wrong to me.

Confusing question, I know. Let me know if I need to be more clear.

Rob

Answer 1

No, the SQL optimizer will just throw the 1=1 away and be on its way.

Answer 2

create an array of the conditions as you determine which ones you need. when you're ready to build the query, check if the array is empty... if it is not empty then print "WHERE" followed by the elements joined together with "AND"s.

edit

since you're using PHP, I'll give some example code:

<?php
    $conditions = array();
    if($foo == "bar") {
        $conditions[] = "some_table.foo = 'bar'";
    }
    if($show_future) {
        $conditions[] = "some_table.entry_date > NOW()";
    }
    $sql_where = count($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";
    $sql = "SELECT * FROM some_table $sql_where;";
?>

Answer 3

I wouldn't be too offended to see a 1=1 in SQL queries, if it was explained somewhere.

That said, if I were doing it in Python, I'd probably do something like:

query = (where_clauses or ["1=1"]).join(" AND ")

So that "real" queries wouldn't need the strange 1=1.

Answer 4

In general, I wouldn't worry about performance at all, until you actually hit a performance issue.

That said, something like 1=1 can have surprising performance consequences. For an example which caught me by suprise, see this question. But then again, there are also cases where prefixing 1=1 will make your query faster! The wise programmer optimizes based on measurement. It's just impossible to predict how a change will impact performance.

Answer 5

To expand of Ty W's answer, since you're using PHP:

$clauses = array();

// Optionally add one or more clauses to the array like this:
$clauses[] = "test = 2";

// Now generate the WHERE clause:
$sql = 'SELECT * FROM Table ';
$sql .= count($clauses) ? ('WHERE ' . implode(' AND ', $clauses)) : '';

Answer 6

While the 1=1 thing ain't pretty, code generators often do things that ain't pretty. If this is not code that needs to be maintained or understood by anyone (other than the developer building and debugging the generator), then I don't believe the ugliness matters.

Answer 7

PHP offers a nice little function for this: implode. (http://www.php.net/manual/en/function.implode.php)

You can use it as follows:

$rawConditions = array("x='y'", "z='a'");
$conditions = "WHERE ".implode(" AND ", $rawConditions);
// $conditions == "WHERE x='y' AND z='a'"