sql simple query

Question

hey yesterday my friend ask me question

select * from user where 1=1

I said this is wrong query but he told me this is true i dont understand it how is it true please explain me how this condition works where 1 = 1?

Answer 1

1=1 is a dummy WHERE clause. It will just return every record from the user table as 1=1 is obviously true.

Put another way, the WHERE clause is applied to each record in users table and returns all records where the WHERE clause is true. 1=1 is obviously true all the time, so all records will match the condition and be returned.

Answer 2

1 is equal to 1 so the above thing 1 = 1 is true, and so the sql query will return all the rows.

Some SQL DB needs a where clause, so you can put a dummy where clause which will be true for all the rows in the tables.

Answer 3

1=1 equates to true and returns all rows in the query

Answer 4

This is common when a query is being built programatically, so for every condition you will add:

 AND (SOMECONDITION)

so the 1=1 starts the WHERE section, it is always true, and it doesn't hurt the performance.

Answer 5

Something very similar has been asked here:

http://stackoverflow.com/questions/242822/why-would-someone-use-where-11-and-conditions-in-a-sql-clause

Answer 6

1=1 is usually used at the top of the where clause for formatting reasons and for ease of debugging. It is correct syntax and has no bearing on the output of the actual query.

It is especially useful when you want to comment out line by line of the where clause to debug. Consider the query

SELECT Columns 
FROM Table
WHERE 1=1
AND Col1 = @Value1
And Col2 IN (@Value2, @Value3)
And Cole Between @Value4 and @Value5

versus

SELECT Columns 
FROM Table
WHERE Col1 = @Value1
And Col2 IN (@Value2, @Value3)
And Cole Between @Value4 and @Value5

It is much easier to comment out any meaningful part of the WHERE clause with the first query

WHERE 1=1
-- AND Col1 = @Value1 
And Col2 IN (@Value2, @Value3)
And Cole Between @Value4 and @Value5

whereas in the second query you would have to do this

SELECT Columns 
FROM Table
WHERE --Col1 = @Value1
-- And 
Col2 IN (@Value2, @Value3)
And Cole Between @Value4 and @Value5

EDIT:

Formatted the queries from above specially for StackOverflow because it only recognizes the /* COMMENT */ and not the -- COMMENT

It is much easier to comment out any meaningful part of the WHERE clause with the first query

WHERE 1=1
/* AND Col1 = @Value1  */
And Col2 IN (@Value2, @Value3)
And Cole Between @Value4 and @Value5

whereas in the second query you would have to do this

SELECT Columns 
FROM Table
WHERE /* Col1 = @Value1 */
/* And */
Col2 IN (@Value2, @Value3)
And Cole Between @Value4 and @Value5