May 20, 2014 at 10:02 am
Now i Have not come accross the use of Where 1=1 before, strange it seemed to a colleague of mine. I was wondering what the point of this? I understand that you can then do something like;
SELECT *
From tablename
WHERE 1= 1
AND A > 1
AND B = A
So that the And parts of the where clause are in some sore of sync, but I really don't see what is wrong with;
SELECT *
From tablename
WHERE A > 1
AND B = A
I can see no conceivable benefits of the 1=1 approach other than Its a lazy way to code, what do others think? There is no benefits in speed of the execution , it only adds unnecessary lines to and SQL that are not needed and is susceptible to SQL Injection. All in all I have survived this far 14 =years without using this and try I as might with talking to my colleague I cannot see a single benefit of having this.
Gordon Barclay
May 20, 2014 at 10:16 am
That's very useful when you're adding conditions to dynamic code. That way you can always add conditions with AND instead of validating if there's a previous condition generated to decide between WHERE or AND.
If dynamic code is not in play, then the only reason would be the ease to comment the conditions when developing/testing the code. Otherwise, there's no gain on the internals of SQL Server, the engine would surely ignore it.
May 20, 2014 at 10:17 am
This is often used when dynamically creating the where clause, simplifies the script as all conditions start with the "AND".
😎
May 20, 2014 at 12:00 pm
Erikur - I disagree that it simplifies the script, what is wrong with the following that needs simplifying? Even more complex SQL statements with multiple tables, joins and sub-queries, doesn't suddenly become the panacea of SQL with this extra line.
SELECT *
From tablename
WHERE A > 1
AND B = A
If you want to just run the select then just highlight as shown above in bold and introduce other parts of the where clause where needed, secondly you can add these to the join e.g
select a.value
from tablea a inner join tableb b on a.id =b.id and a.value <> '"a"
As I said I understand what it does but I cannot see the point in it, it doesn't make your SQL execute quicker or slower, it's confusing if you don't use this approach and is a remedy for a problem that doesn't exist, well at least to me.
Thanks for the answers though and I shall give this a miss as I am all for doing something if there is a benefit to it but where there clearly isn't then I'll leave alone.
Gordon Barclay
May 20, 2014 at 12:09 pm
Gordon Barclay (5/20/2014)
Erikur - I disagree that it simplifies the script, what is wrong with the following that needs simplifying?
You missed "when dynamically creating the where clause"
Personally, I hate seeing it, but it does often get used when writing dynamic SQL so that the person writing the code doesn't have to figure out whether it's the first predicate of the where clause or not.
You said earlier:
and is susceptible to SQL Injection
Which is not the case. A predicate cannot make a query susceptible to SQL injection. Un-parameterised dynamic SQL does that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2014 at 12:47 pm
Fair point I guess. As the SQL i am using is not dynamic then it's a moot point at best, even when I have though It still doesn't necessarily make it any more readable. If you haven't used this and you see the following
'Select * from Table a WHERE 1=1 and field1 =' + @Variable
is not really that much easier to understand than
'Select * from Table a WHERE field1 =' + @Variable
Or am I missing the point?
Gordon Barclay
May 20, 2014 at 1:12 pm
Gordon Barclay (5/20/2014)
Fair point I guess. As the SQL i am using is not dynamic then it's a moot point at best, even when I have though It still doesn't necessarily make it any more readable. If you haven't used this and you see the following'Select * from Table a WHERE 1=1 and field1 =' + @Variable
is not really that much easier to understand than
'Select * from Table a WHERE field1 =' + @Variable
Or am I missing the point?
You're probably missing the point.
First of all, your code would be subject to SQL Injection, that's no way to create a parametrized query.
Second, sometimes people want to avoid the filter when the variable is null or empty.
Here's an example:
DECLARE @Parameter1 int,
@Parameter2 varchar(100) = 'SomeValue'
--We would only use @Parameter2 to demonstrate this.
DECLARE @Query nvarchar(4000)
SET @Query = 'Select * from Table a where 1 = 1 ' + CHAR(13)
IF @Parameter1 > 0 --Checks for positive values only
SET @Query = @Query + 'AND columnX = @_Parameter1 ' + CHAR(13)
IF @Parameter2 > '' --Checks for not empty and not NULL
SET @Query = @Query + 'AND columnY = @_Parameter2 ' + CHAR(13)
EXECUTE sp_executesql N'@_Parameter1 int, @_Parameter2 varchar(100)', @_Parameter1 = @Parameter1, @_Parameter2 = @Parameter2
As you see, you don't need to check which is the first condition as all can begin with AND.
Gail uses an alternative on her article about catch-all queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
I hope the intention is clearer now.
May 20, 2014 at 1:28 pm
I do actually state -
Luis Cazares (5/20/2014)
Gordon Barclay (5/20/2014)
Fair point I guess. As the SQL i am using is not dynamic then it's a moot point at best,
Though the rest makes a valid point and I understand now what the reason for using this is, many thanks.
Gordon Barclay
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply