SPECIAL OPERATORS IN SQL SERVER

SPECIAL OPERATORS IN SQL SERVER

IN

NOT IN

BETWEEN

NOTBETWEEN

LIKE

NOT LIKE

IS NULL

1.  IN operator: It is used for comparing multiple values.

             SELECT * FROM EMP WHERE ENAME IN (‘RAM’,’RAJ’)

              SELECT * FROM EMP WHERE EMPNO IN (11, 22, 33)

2. NOT IN Operator: It is just opposite to in. it display all the values in table, other than values specified in list

            SELECT * FROM EMP WHERE JOB NOT IN (‘RAM’,’RAJ’)

Other than ‘RAM’, ‘RAJ’ details will be displayed

3. BETWEEN Operator: it will display the values specified in the range including the given range. It is also known as INCLUSIVE OPERATOR.

          SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000

It will display all the employees details who are getting salary more than or equal to 2000 and less than or equal to 4000.

It includes the range specified, so it is called inclusive operator.

 4. NOT BETWEEN Operator: It is opposite to ‘between’ operator it will display the values other than range specified.

  SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 4000.

Note: NOT BETWEEN operator will not include the range specified, so it is called exclusive operator.

5. LIKE Operator: It is used to search for pattern in characters. it associated with two characters

_ (UNDERSCORE): For single character

 %(PERCENTILE): For Multiple characters

[] – Any single character within the specified range.

[^] – Any single character not within the range [^a_f] [^abcdef]

E.g.: WAQ TO DISPLAY THE NAMES BEGIN WITH ‘S’

 SELECT ENAME FROM EMP WHERE ENAME LIKE ‘S%’

                        ENAME

                        SMITH

                        SCOTT

E.g.: WAQ TO DISPLAY NAMES END WITH ‘S’

 SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%S’

                        ENAME

                        JAMES

                        JONES

E.g.: WAQ TO DISPLAY NAMES CONTAINING ‘A’ AS SECOND CHARACTER

 SELECT ENAME LIKE ‘_A%’

E.g.: WAQ TO DISPLAY NAME CONTAINING 5 CHARACTERS

 SELECT ENAME FROM EMP WHERE ENAME LIKE ’-----‘

                                    OR

SELECT ENAME FROM EMP WHERE LEN (ENAME)=5

6. NOT LIKE Operator: Opposite to like operator.

7. IS NULL Operator: It is used for comparing NULL values

E.g.: WAQ TO DISPLAY ALL EMPLOYEES WHO ARE WORKING IN NULL DEPARTMENT

SELECT * FROM EMP WHERE DEPTNO. IS NULL

8. IS NOT NULL: Opposite to IS NULL operator

E.g.: WAQ TO DISPLAY ALL EMPLOYEES WHO ARE NOT WORKING UNDER NULL DEPARTMENT

SELECT * FROM EMP WHERE DEPTNO. IS NOT NULL