Menu
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.
785
If you have worked with SQL databases before, you might have come across the statement WHERE 1=1. It is a common statement that is used to return all the records from a given table. The statement where 1=1 in SQL means true. It is the same operation as running the select statement without the where clause.
Essentially, where 1 = 1 means no where clause. It will always be true, so all records will be returned. Some people believe, erroneously, that it makes queries go faster. In most cases, it is useless, and the Optimizer will often optimize it away.
The statement 'select 1' from any table name means that it returns only 1. For example, If any table has 4 records then it will return 1 four times.
A query like this can be used to ping the database. The clause: WHERE 1=0. Ensures that non data is sent back, so no CPU charge, no Network traffic or other resource consumption. A query like that can test for: server availability.
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. If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
