1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Different SQL Server Operators

Discussion in 'SQL Server' started by MinalS, Apr 4, 2016.

  1. The various SQL Server operators are as listed below:
    1. Comparison operators
    2. Arithmetic operators
    3. Logical operators
    4. Negation operators

    Arithmetic operators



    1. Addition

    It is used for adding values on any side of the operator. The operator can add days, date or number.
    Example:
    Code:
    	DECLARE @a int;
    	SET @a = 10;
    	SELECT 12 + @a;
    
    
    Output:

    [​IMG]


    2. Subtraction

    It is used for subtracting two numbers, days, from given numbers.

    Example:

    Code:
    
    SELECT MAX(marks) – MIN(marks) as difference from academics
    
    
    Output:

    [​IMG]

    3. Multiply

    It multiplies two expressions using the multiplication operator.

    Example:

    Code:
    
    DECLARE @x int;
    SET @x = 30;
    SELECT 2 * @x;
    
    
    Output:

    [​IMG]

    4. Division

    Used for dividing one number by another

    Example:

    Code:
    
    DECLARE @y int;
    SET @y = 30;
    SELECT @y / 5;
    
    
    Output:

    [​IMG]

    5. Modulo

    The reminder of one number divided by other is returned.

    Example:

    Code:
    
    SELECT 46%3 As Integer;
    
    
    Output:

    [​IMG]

    Logical operators



    1. ALL

    It is used for comparing value with a single column set of values.
    Syntax:
    Code:
    scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery ) 
    
    Example:
    Code:
    select srno, marks, name from academics where marks > ALL 
    ( SELECT marks from academics where marks = 70 );
    
    Output:
    [​IMG]

    2. AND

    It contains two Boolean expression and the value as TRUE is returned if both the expressions are TRUE.
    Syntax:
    Code:
    Boolean_expression AND Boolean expression
    
    Example:
    Code:
    select srno, name, location from academics where marks > 80 and location = ‘Glasgow’
    
    Output:
    [​IMG]

    3. BETWEEN

    It is used to define a range to be tested. The values present in the range are displayed.
    Syntax:
    Code:
    test_expression [ NOT ] BETWEEN expression1 AND expression 2
    
    where,
    test_expression: it is used to define the range using the expression 1 and expression 2
    NOT: the result of the predicate is negated
    expression1, expression 2: both the expressions must have similar data types
    AND: It defines that test expression is in the range defined by both the expressions
    Example:
    Code:
    select name, marks from academics where marks between 65 and 80
    
    Output:
    [​IMG]

    4. IN

    It is used to define whether the values are matching with the one present in the list or subquery.
    Syntax:
    Code:
    Expression [ NOT ] IN ( subquery | expression [ ,….n ] )
    
    Where,
    Expression: Defines the valid expression
    subquery: The result set of one column is defined
    expression [ ,….n ] : The expressions which match the list are defined
    Example:
    Code:
    select srno from academics where name in ( select name from academics where location = ‘Germany’ )
    
    Output:
    [​IMG]

    5. LIKE

    It is used to check if the character string matches the pattern. The pattern contains wildcard and regular characters.
    Syntax:
    Code:
    Expression [ NOT ] LIKE pattern [ escape character ]
    
    Where,
    Expression: It is a valid expression of character data type
    pattern: The string of characters to be search in expression.
    The type of wildcard characters that can be added to the pattern are:
    1. _ ( underscore) : It is used for matching any single character.
    2. %: The string consisting of zero or more characters
    3. [ ] : A single character in the specified range
    4. [ ^ ] : A single character not in the specified range
    Example:
    Code:
    select srno, name, location from academics where location LIKE ‘G%’
    
    Output:
    [​IMG]

    Negation condition operators



    NOT operator

    The operator is used for altering the meaning of the operator that is used. It is also known as negate operator.
    Syntax:
    Code:
    [ NOT ] Boolean_expression
    
    Where,
    Boolean expression is a valid expression
    Example:
    Code:
    select srno, name, location from academics where location LIKE ‘G%’ AND NOT srno > 103
    
    Output:
    [​IMG]

    Comparison operators



    Some of the comparison operators in SQL are as explained below:

    1. Equals ( = )

    The equality of two expressions is compared.
    Syntax:
    Code:
    expression = expression
    
    where,
    expression is any valid one. The data type of the expressions must be easily converted.
    Example:
    Code:
    select * from academics where srno = 104
    
    Output:
    [​IMG]

    2. Greater than ( > )

    The operator verifies if the value of the left operand is greater than the right operand value.
    Syntax:
    Code:
    Expression > Expression
    
    Example:
    Code:
    select * from academics where marks > 60
    
    Output:
    [​IMG]

    3. Less than ( < )

    It is used to check if the value of left operand is less than the value of the right operand.
    Syntax:
    Code:
    Expression < Expression
    
    Example:
    Code:
    select * from academics where marks < 80
    
    Output:
    [​IMG]

    4. Not Equal To ( < > )

    It is used for comparing two expressions. If the values are not equal, the condition is true.
    Syntax:
    Code:
    Expression < > Expression
    
    Example:
    Code:
    select * from academics where marks < > 70
    
    Output:
    [​IMG]

    5. Not Less Than ( ! < )

    The operator checks if the value of left operand is not less than the value of right operand. If it is satisfies, the condition is true.
    Syntax:
    Code:
    Expression ! <  Expression
    
    Example:
    Code:
    select * from academics where marks ! < 70
    
    Output:
    [​IMG]

    6. Not Greater Than ( ! > )

    The operator is used for checking whether the left operand is not greater than the right operand value. If satisfied, the condition is true.
    Syntax:
    Code:
    Expression ! >  Expression
    
    Example:
    Code:
    select * from academics where marks ! > 70
    
    Output:
    [​IMG]
     

Share This Page