Is there a way to use a wildcard in a "where" statment for MySQL?

Question

I have a query that uses a where clause. At times, this may be used and at others, I may want to omit it completely to get back all results. I can certainly write two different queries but I would like to cut down on any code that I can for simplistic reasons. Is there a way to do this in mysql?

Take a query like:

SELECT * FROM my_table WHERE id = '3'

and:

SELECT * FROM my_table

Is there a way to use the top query and still get back all records?

Answer 1

No, because the predicate in the first query may not actually retrieve all of the records from the table; it may use an index so that it only has to obtain the specific record(s) the query needs to return.

If you wanted to keep a predicate of that same form but still return all of the results, you would need to do something like this:

where id = 3 or id <> 3

or this:

where id = id

Note that to either of these, you'll have to add or id is null if id can be null.

If you just want to have a predicate in your query, this will suffice:

where 1

but this is just redundant, and you may as well just leave the predicate out.

Answer 2

If I understand your question correctly, then YES

SELECT * FROM my_table WHERE 1=1

Answer 3

Your question is dubious. You are really saying that when there is no id==3 all entries should be returned. You can do that easily if you pull all entries and then sort them out using php:

$sql = mysql_query("SELECT * FROM my_table");
while($row = mysql_fetch_array($sql) {
    if($row['id']==3)
        // do something
}

But as the table grows this will put an enormous stress on the database. You should go with the multiple query and enforce some kind of limit on the second query.

// try to get id == 3
SELECT * FROM my_table

// if id == 3 returns 0 results
SELECT * FROM my_table LIMIT 5

Hope it helps!

Answer 4

You'd have to be using dynamic SQL, like

"SELECT * FROM my_table WHERE id " & qualifier

Then set qualifier to "= '3'" or to "1".

Answer 5

If you're building the SQL query as you go along, and you decide at the last minute that you want to negate/ignore the "WHERE" part of your query, you can append OR 1 to your where-clause. Remember that logically, X OR TRUE is true for all X.

sqlite> SELECT id FROM moz_downloads WHERE id < 405 LIMIT 10;
80
403
404
sqlite> SELECT id FROM moz_downloads WHERE id < 405 OR 1 LIMIT 10;
80
403
404
405
407
408
409
410
411
412

Note that I had to stick a LIMIT 10 in there to not get too many results for the demonstration, but the second statement's where-clause is id < 405 or 1.

It depends on the application, but you may or may not generate your queries at runtime. Some queries will always be the same, like SELECT * FROM recent_files, but some queries will be like generated on-the-fly. In the latter case, you might have something like

something = make_safe_for_sql(get_something_from_user())
query = "SELECT * FROM data WHERE something=" + something
if should_ignore_something:
     query += " OR 1"
database.execute(query)

_{Note: Depending on your SQL engine, you might need to do OR 1=1 to evaluate to a boolean true.}

Answer 6

More food for thought...

I notice you quoted the '3'. If your ids are char data you could use the LIKE string comparison operator.

For a single value

SELECT * FROM my_table WHERE id LIKE '3'

For all values

SELECT * FROM my_table WHERE id LIKE '%'

Won't give you any values with NULL id though.