SELECT * FROM tablename WHERE 1

sql select-query
Stephen Alexander picture Stephen Alexander · Aug 7, 2016 · Viewed 18.4k times · Source

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

Answer

brent picture brent · Aug 7, 2016

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'

Related questions

Wildcard as parameter of stored procedure

Suppose, I have a stored procedure, that accepts one parameter - id, and returns some data corresponding to this id. What if I want to retrieve all such data, like using wildcard in SELECT query? How to do this?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More