none
T-SQL Where 1=1 and Where 1 = 0 , what is the puprose of this statement ?

    Question

  • Hi friends,

    I am refering some already developed Reports Store Procedures by other experienced developers in SQL Server.

    Most of the Store Proc contains Where 1 = 1 in the Select statement's Where Caluse very first and then it contains rest of the conditions in remaining Where Clauses.

    My question, Why they are using Where 1 = 1 which is always True ?

    Is there any performance gain with this ?

    some time, I have also seen Where 1 = 0 in T-SQL queries.

    can someone explain what is the pupose behind this ?

    thanks.

     

    Saturday, October 15, 2011 9:50 PM

Answers

  • I've seen WHERE 1= 1 sometimes used to faciliate building dynamic SQL ov zero or more search conditions.  For example:

     

    SET @SelectStatement = 'SELECT SomeData FROM dbo.SomeTable WHERE 1  = 1';
    IF @Column1 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column1 = @Column1';
    IF @Column2 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column2 = @Column2';
    

    I sometimes wee WHERE 1 = 0 (or any always false condition) used to create a table of identical structure, sans data:

    SELECT * INTO dbo.NewTable FROM dbo.SomeTable WHERE 0 = 1;
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NModerator Sunday, October 16, 2011 2:27 AM
    • Marked as answer by KJian_ Monday, October 24, 2011 6:04 AM
    Saturday, October 15, 2011 10:35 PM

All replies

  • It's possible they put it there when they were needing to turn off certain sql statements, perhaps during a debugging session.  That's the only thing I can think of.  If they put 1=0 it would cause the query to never return any matches.  Somebody might have done this when they were trying to isolate the cause of a report not working or something like that.  They must have just left the hooks in there.
    Tom Overton
    • Edited by Tom_Overton Saturday, October 15, 2011 10:07 PM
    Saturday, October 15, 2011 10:01 PM
  • I've seen WHERE 1= 1 sometimes used to faciliate building dynamic SQL ov zero or more search conditions.  For example:

     

    SET @SelectStatement = 'SELECT SomeData FROM dbo.SomeTable WHERE 1  = 1';
    IF @Column1 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column1 = @Column1';
    IF @Column2 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column2 = @Column2';
    

    I sometimes wee WHERE 1 = 0 (or any always false condition) used to create a table of identical structure, sans data:

    SELECT * INTO dbo.NewTable FROM dbo.SomeTable WHERE 0 = 1;
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NModerator Sunday, October 16, 2011 2:27 AM
    • Marked as answer by KJian_ Monday, October 24, 2011 6:04 AM
    Saturday, October 15, 2011 10:35 PM
  • Lot of programmers use WHERE 1=1 in dynamic SQL statement,instead of WHERE
    only they start with WHERE 1=1 then rest of the conditions.

    They include this condition so that any run time conditions can be added with an And without doing any conditional check.
    Shatrughna.
    Sunday, October 16, 2011 1:35 AM
  • This is in fact the most correct answer

    Lot of programmers use WHERE 1=1 in dynamic SQL statement,instead of WHERE
    only they start with WHERE 1=1 then rest of the conditions.

    They include this condition so that any run time conditions can be added with an And without doing any conditional check.


    Shatrughna.



    • Edited by sschefer Monday, June 10, 2013 3:44 PM
    Monday, June 10, 2013 3:41 PM
  • Hi,

    WHERE 1 = 0 is used to obtain the column names.

    SELECT * FROM AnyTable WHERE 1 = 0 will return no rows, but you'll obtain all the column headers.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, June 10, 2013 3:49 PM
  • This is usually generated code. It is a kludge used in dynamic SQL; I regard it as a bad code smell that tells you to look for ways to re-write this stuff. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, June 10, 2013 7:01 PM
  • My response comes 2 years later after the orginal question :-)

    Anyways... I use this very often with a CASE and is basically to evaluate extra conditions whether is on a WHERE clause or inside a SELECT items; a simple thought is on a T-script, say for development purposes and later for operations or whoever takes care of it.

    DECLARE @DebugFlag BIT 
    SET @DebugFlag = 1
    SELECT 'Now you see this' AS [OutputMsg]
    WHERE CASE
                   WHEN @DebugFlag = 1 THEN 1
                   ELSE 0
                 END = 1

    Effectively, the condition is met (you'll se the output) when the flag is set to 1, otherwise you won't; making a valid usage of 1=1 or 1=0
    Wednesday, December 18, 2013 9:19 PM