WHERE 1=1

[DbHd]

What is the significance of the WHERE clause in the example below? (There is no column named "1")

SELECT * from vwAcme WHERE 1=1

 

[HappyCoda]

The expression (1 = 1) will always evaluate as true, hence the SQL is effectively the same as:

SELECT * FROM vwAcme


For interest, I have used such a clause when compiling SQL statments dynamically in VB code for use subsequent use as a form recordsource. I build up a WHERE clause based on user selections from another form like so:

strWhere = "WHERE (1=1)"
For Each *condition* In *list of possible conditions*
If *condition is selected* Then
strWhere = StrWhere & " AND " & *condition*
End If
Next *condition*

The (1=1) eliminiates the need to test in the code whether a condition is the first to be added the clause and hence don't add an "AND" at the front.

Cheers,
HappyCoda

 

[DbHd]

Thanks Happy!

 

[HappyCoda]

You're very welcome.