CODEWITHSUNDEEP
sqlselect-query
Stephen Alexander
Stephen Alexander

Reputation: 1283

SELECT * FROM tablename WHERE 1

I've been curious. What are the differences between these respective queries:

  1. SELECT * FROM `tablename`

  2. SELECT * FROM `tablename` WHERE 1

  3. SELECT * FROM `tablename` WHERE 1=1

Upvotes: 130

Views: 30602

Answers (10)

Ghayyour Ahmed Butt
Ghayyour Ahmed Butt

Reputation: 393

  1. SELECT * FROM table_name : it will give you all the records of the table with running any where statement.
  2. 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.
  3. 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.

Upvotes: 2

user5373973
user5373973

Reputation:

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')

Upvotes: 14

Keshan Nageswaran
Keshan Nageswaran

Reputation: 8188

Result - Gives all the records in the table specified instead of tablename for all three queries

SELECT * FROM tablename WHERE 1 - Check this answer

SELECT * FROM tablename WHERE 1=1 - Check this answer

For more Info about WHERE clause optimizations check these : MYSQL, SQLite, SQL

Upvotes: 1

Krishneil
Krishneil

Reputation: 1490

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:

  1. Select * from 'myTable where ID = 3 (valid)
  2. 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'

Upvotes: 3

Jin Thakur
Jin Thakur

Reputation: 2773

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"

Upvotes: 7

brent
brent

Reputation: 1488

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'

Upvotes: 178

CedF
CedF

Reputation: 66

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.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 76

delx
delx

Reputation: 404

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.

Upvotes: 8

sagi
sagi

Reputation: 40491

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.

Upvotes: 16

Related Questions