Menu
SELECT * FROM table_name : it will give you all the records of the table with running any where statement. SELECT * FROM table_name WHERE 1 : this where condition is always true, its mostly used by hacker to get into any system.
You should use the WHERE clause to filter the records and fetching only the necessary records. The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.
WHERE clause Syntax. The basic syntax for the WHERE clause when used in a MySQL SELECT WHERE statement is as follows. “WHERE” is the keyword that restricts our select query result set and “condition” is the filter to be applied on the results. The filter could be a range, single value or sub query.
The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.
2 and 3 are the same in MySQL, functionally 1 is also the same.
where 1 is not standard so, as others have pointed out, will not work in other dialects.
People add where 1 or where 1 = 1 so where conditions can be easily added or removed to/from a query by adding in/commenting out some "and ..." components.
i.e.
SELECT * FROM `tablename` WHERE 1=1
--AND Column1 = 'Value1'
AND Column2 = 'Value2'
As you know, all three produce the same results. (In a boolean context, MySQL treats the integer "1" as true -- in fact, any number that is not "0" is treated as true).
The MySQL optimizer is explicitly documented to remove constant conditions in the WHERE clause:
Constant condition removal . . .:
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
Hence, all three will be compiled into exactly the same code.
They are all functionally equivalent and should have the same performance characteristics.
That said, the first and third are standard SQL. The second will cause some sort of boolean expression error in many databases. So, I would advise you to avoid that (I'm not sure whether it works or not in MySQL's strict SQL mode).
Often the third is used when constructing dynamic WHERE clauses. It makes it easy to add additional conditions as AND <condition> without worrying about lingering ANDs.
If you are asking about the differences in performances and results, there isn't any , 2 and 3 are the same WHERE TRUE , and they will result the same as the first one.
1 - SELECT * FROM table_name
Results in all the data from table_name (no filter)
2 - SELECT * FROM table_name WHERE 1
1 will be evaluated as TRUE , therefore - no filter - every record will be returned .
3 - SELECT * FROM table_name where 1=1
Same as the last one, 1=1 is a TRUE expression , therefore - no filter - every record will be selected.
All are the same but 2 and 3 are used to easily handle AND/OR conditions
like:
SELECT * FROM `tablename` WHERE 1=1 AND (columnname1 = 'Value' OR columnname2 = 'Value')
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