locked
what does this operator "!>" means? RRS feed

  • Question

  • hi,
    guys can you let me know what does "!>" mean in t-sql?
    please provide some examples for better understanding,Thanks,
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Friday, February 5, 2010 1:12 PM

Answers

  • !<=not less than
    !>=not greater than
    !=not equal to=<>


    Example:[Continuation to your previous thread]

    declare   @Table1 table (ID int)

    insert  @Table1 values(1)
    insert  @Table1 values(3)
    insert  @Table1 values(5)
    insert  @Table1 values(7)
    insert  @Table1 values(9)
    insert  @Table1 values(11)
    insert  @Table1 values(13)
    insert  @Table1 values(14)



    declare @num int
    set @num=1
    ;with cte as
    (select  @num as num
    union all
    select  num+1 from  cte where num+1!>20 --not greater than 20
    )

    select num from cte where num not in (select id from @Table1)

    Friday, February 5, 2010 1:18 PM
  • Hi

    Transact-SQL comparison operators.
    Operator Meaning

    = (Equals)

    Equal to

    > (Greater Than)

    Greater than

    < (Less Than)

    Less than

    >= (Greater Than or Equal To)

    Greater than or equal to

    <= (Less Than or Equal To)

    Less than or equal to

    <> (Not Equal To)

    Not equal to

    != (Not Equal To)

    Not equal to (not ISO standard)

    !< (Not Less Than)

    Not less than (not ISO standard)

    !> (Not Greater Than)

    Not greater than (not ISO standard)



    Example for not equal to

    USE AdventureWorks;
    GO
    DECLARE @MyProduct int;
    SET @MyProduct = 750;
    IF (@MyProduct != 0)
       SELECT ProductID, Name, ProductNumber
       FROM Production.Product
       WHERE ProductID = @MyProduct
    GO

    Rajesh Jonnalagadda http://www.ggktech.com
    • Proposed as answer by Naomi N Friday, February 5, 2010 2:56 PM
    • Marked as answer by Nai-dong Jin - MSFT Wednesday, February 10, 2010 3:31 AM
    Friday, February 5, 2010 1:40 PM
  • SQL Server will translate the !< or !> to the appropriate comparison operator (>= or <=), so the answer is it actually does not even use this operator.  I would recommend that you stick to the normal operators like >= and <=.

    Here is a sample and the query plan

    declare   @t table (ID int PRIMARY KEY CLUSTERED)
    
    insert  @t values(1)
    insert  @t values(3)
    insert  @t values(5)
    insert  @t values(7)
    insert  @t values(9)
    insert  @t values(11)
    insert  @t values(13)
    insert  @t values(14)
    
    set statistics profile on;
    
    select *
    from @t
    where id !< 5
    
    set statistics profile off;
    
    /*
    select *  from @t  where id !< 5
      |--Clustered Index Seek(OBJECT:(@t), SEEK:([ID] >= (5)) ORDERED FORWARD)
    */

    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Friday, February 5, 2010 2:55 PM
    • Marked as answer by Nai-dong Jin - MSFT Wednesday, February 10, 2010 3:31 AM
    Friday, February 5, 2010 2:47 PM

All replies

  • !<=not less than
    !>=not greater than
    !=not equal to=<>


    Example:[Continuation to your previous thread]

    declare   @Table1 table (ID int)

    insert  @Table1 values(1)
    insert  @Table1 values(3)
    insert  @Table1 values(5)
    insert  @Table1 values(7)
    insert  @Table1 values(9)
    insert  @Table1 values(11)
    insert  @Table1 values(13)
    insert  @Table1 values(14)



    declare @num int
    set @num=1
    ;with cte as
    (select  @num as num
    union all
    select  num+1 from  cte where num+1!>20 --not greater than 20
    )

    select num from cte where num not in (select id from @Table1)

    Friday, February 5, 2010 1:18 PM
  • Hi

    Transact-SQL comparison operators.
    Operator Meaning

    = (Equals)

    Equal to

    > (Greater Than)

    Greater than

    < (Less Than)

    Less than

    >= (Greater Than or Equal To)

    Greater than or equal to

    <= (Less Than or Equal To)

    Less than or equal to

    <> (Not Equal To)

    Not equal to

    != (Not Equal To)

    Not equal to (not ISO standard)

    !< (Not Less Than)

    Not less than (not ISO standard)

    !> (Not Greater Than)

    Not greater than (not ISO standard)



    Example for not equal to

    USE AdventureWorks;
    GO
    DECLARE @MyProduct int;
    SET @MyProduct = 750;
    IF (@MyProduct != 0)
       SELECT ProductID, Name, ProductNumber
       FROM Production.Product
       WHERE ProductID = @MyProduct
    GO

    Rajesh Jonnalagadda http://www.ggktech.com
    • Proposed as answer by Naomi N Friday, February 5, 2010 2:56 PM
    • Marked as answer by Nai-dong Jin - MSFT Wednesday, February 10, 2010 3:31 AM
    Friday, February 5, 2010 1:40 PM
  • Hi its similar to like <=


    Ex : Select * from Table Where ID > 50

    then its shows all ID greater than 50

    Suppose u will give this query means

    Select * from Table Where ID <= 50

    its shows all ID Less than or Equal to 50

    Same Like you using this query will return results

    Select * from Table Where ID !>50




    Friday, February 5, 2010 2:15 PM
  • SQL Server will translate the !< or !> to the appropriate comparison operator (>= or <=), so the answer is it actually does not even use this operator.  I would recommend that you stick to the normal operators like >= and <=.

    Here is a sample and the query plan

    declare   @t table (ID int PRIMARY KEY CLUSTERED)
    
    insert  @t values(1)
    insert  @t values(3)
    insert  @t values(5)
    insert  @t values(7)
    insert  @t values(9)
    insert  @t values(11)
    insert  @t values(13)
    insert  @t values(14)
    
    set statistics profile on;
    
    select *
    from @t
    where id !< 5
    
    set statistics profile off;
    
    /*
    select *  from @t  where id !< 5
      |--Clustered Index Seek(OBJECT:(@t), SEEK:([ID] >= (5)) ORDERED FORWARD)
    */

    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Friday, February 5, 2010 2:55 PM
    • Marked as answer by Nai-dong Jin - MSFT Wednesday, February 10, 2010 3:31 AM
    Friday, February 5, 2010 2:47 PM