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

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 4

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.