SELECT * FROM tablename WHERE 1
SqlSelect QuerySql Problem Overview
I've been curious. What are the differences between these respective queries:
-
SELECT * FROM `tablename` -
SELECT * FROM `tablename` WHERE 1 -
SELECT * FROM `tablename` WHERE 1=1
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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.
Solution 3 - Sql
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.
Solution 4 - Sql
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')
Solution 5 - Sql
In 1, MySQL does not need to evaluate any WHERE conditions.
In 2 and 3, the where condition is static and not based on the rows' values. It will be evaluated with boolean logic and always be true.
Functionally, there is no difference. You should choose 1 for code clarity.
Solution 6 - Sql
All are the same but 2 and 3 are used to create Dynamic queries for AND/OR conditions
sqlquery =" SELECT * FROM `tablename` where 1 =1 "
we use 2 and 3 format to make dynamic query so we already know "where" keyword is added and we keep adding more filters . Like
sqlquery = sqlquery + "and columna =a"
"AND columna =a " then
after few lines if we have new filters we add "AND coulmnb =b " and so on
You don't have to check the sql query for where keyword as its placed in first or initial query
SELECT * FROM `tablename` WHERE 1=1 AND (columnname1 = 'Value' OR columnname2 = 'Value')
Otherwise we can write sqlquery = "SELECT * FROM tablename"
then
if there is no 'where' clause in sqlquery then
sqlquery = sqlquery + "where columna =a"
else
sqlquery = sqlquery + "and columna =a"
Solution 7 - Sql
They all output the same answer. However the way 2 and 3 are written is mostly is in order to have control of the "Where" statement so it would make it easier to add it or remove it later.
I think that the first and third way are the proper way of writing it. If you need a where statement you do like in number 3 otherwise number 1 would be good enough.
Solution 8 - Sql
In MS SQL 1 and 3 are same , however, option 2 will not work , option 2 is an invalid statement as in MS SQL, WHERE is used to compare some values. For Example:
- Select * from 'myTable where ID = 3 (valid)
- Select * from 'myTable where 1 = 1 is same as Select * from 'myTable where 2= 2 is same as Select * from 'myTable where 3= 3 you get the idea (valid) is same as Select * From 'myTable'
Solution 9 - Sql
- 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. If you heard about sql injections than 2 & 3 are scenarios which are forced to build by hacker to get all the records of table.
- SELECT * FROM table_name where 1=1 : This will give you all the records of the table but it will compare the where statement and then move forward, it's basically added to add or removed more statements after that.