SELECT * FROM tablename WHERE 1

Question

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

is there a "too narrow" flag for tags? because that applies to many, if not all, SQL dialects. I see it frequently in MSSQL and Oracle as well. btw what are the apostrophes? is not the backtick ` used to quote mysql object names? — Cee McSharpface, Aug 07 '16 at 20:52
hm. why not simply `... WHERE TRUE`? I am aware that (in most SQL, including MySQL) TRUE is just a fancy macro for `1` - but still, ain't it a bit more obvious to the reader? — , Aug 08 '16 at 16:55
Possible duplicate of [Why would someone use WHERE 1=1 AND in a SQL clause?](http://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause) — A_Arnold, Aug 08 '16 at 21:13
I've read about some database systems where you can't use a DELETE without making an explicit condition as a protection against accidentally deleting the whole table. Requiring an always-true condition would mean "Yes, I really do mean delete the whole table." — billpg, Aug 09 '16 at 08:50
2 is not valid I think in plsql, the other two should produce the same results. — NappingRabbit, Aug 09 '16 at 18:34
Just FYI. there's a more appropriate way of achieving this `SELECT *` using `TABLE 'tablename'`. — kytwb, Aug 09 '16 at 18:38
I used to use a (1=1) condition as first when developing some complex queries, where each next condition can start with e.g. AND, hence it's easy to comment out line by line — wambach, Aug 12 '16 at 05:53

score 177 · Accepted Answer · edited Aug 09 '16 at 20:12

177

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'

edited Aug 09 '16 at 20:12

Valloric

2,904
2
18
10

answered Aug 07 '16 at 11:52

brent

1,468
1
11
12

11

And also when you are programmatically building a query into a string to execute it later, if you add `WHERE 1=1` you will not need to take care if the condition you are trying to add to the string is the first one (so it will need a `WHERE` in front) or not. – Vincent Olivert Riera Aug 07 '16 at 11:55
45

Been programming for 6 years and never thought of doing this - thanks! – SimonGates Aug 07 '16 at 12:21
2

It may be worth pointing out that this is very different from `ORDER BY 1`, which is discouraged and rather rare: here, the number is the index of the column to sort. – Cee McSharpface Aug 07 '16 at 20:48
6

@dlatikay `ORDER BY 1` syntax is required (instead of column names) if you are doing a union of two or more SELECT statements. – Mark Stewart Aug 07 '16 at 20:58
5

@SimonGates I've been programming for 6 years and never thought about doing anything else lol – WernerCD Aug 08 '16 at 02:24
4

@WernerCD I think it's far more sensible to build up a list of filter clauses in a list of some kind, and then string join them with `" AND "` as the delimiter. – Zev Spitz Aug 08 '16 at 16:57
1

@ZevSpitz My experience is more working in a code editor, building the queries by hand for use in a reports - not dynamically building the queries via code. So `WHERE 1=1 \r\n ...` and commas at start of lines are two things I grew attached to real quick. – WernerCD Aug 08 '16 at 17:54
Actually, Sql Server throws an error if you use `where 1` – A_Arnold Aug 08 '16 at 20:56
1

@ZevSpitz But you still have to conditionally add the `WHERE` keyword depending on whether the list of where clauses is empty. – Barmar Aug 09 '16 at 19:10
@Barmar True, but I think it still makes more sense to do this (when dynamically building an SQL statement in code) than to introduce weird artifacts into the SQL. – Zev Spitz Aug 10 '16 at 00:25
I have bad experiences in the past with SQL engines that either returned the results more quickly with WHERE 1=1 than if there were no where clause and even some that crashed if there were no where clause, so I always add a condition that must be true when I want all the rows. – Flynn Aug 10 '16 at 19:13
@user45959 What RDMS did you use that crashed on a select without a where clause? – Alpha Aug 12 '16 at 11:59

score 76 · Answer 2 · answered Aug 07 '16 at 11:56

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.

This is a superior answer for saying everything the other answer does but crucially pointing out the difference that #2 is not ANSI-compliant. — underscore_d, Aug 08 '16 at 10:18

score 16 · Answer 3 · answered Aug 07 '16 at 11:53

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.

score 14 · Answer 4 · edited May 22 '18 at 03:24

14

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

edited May 22 '18 at 03:24

Ashraf.Shk786

570
1
9
23

answered Aug 07 '16 at 11:56

score 8 · Answer 5 · answered Aug 07 '16 at 11:55

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.

score 7 · Answer 6 · edited Aug 10 '16 at 05:13

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"

4 leading spaces denote a code block. Please [edit] your answer to format it properly, and see [editing help](http://stackoverflow.com/editing-help) for the whole Markdown formatting Bible. — Mathieu Guindon, Aug 08 '16 at 17:58

score 4 · Answer 7 · answered Aug 08 '16 at 20:45

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.

score 3 · Answer 8 · answered Aug 11 '16 at 00:42

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'

score 2 · Answer 9 · edited May 22 '18 at 05:36

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.

score 1 · Answer 10 · edited May 23 '17 at 11:53

1

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

edited May 23 '17 at 11:53

Community

1
1

answered Aug 24 '16 at 18:58

Keshan Nageswaran

7,547
3
24
41

SELECT * FROM tablename WHERE 1

10 Answers10