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

Solution 1:

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:

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:

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:

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