There is lots of operator available in SQL server database such as assignment operator, bitwise operator, logical operator, comparison operator etc. Here we will discuss some important operator which is used widely in SQL server database.

SELECT command with AND operator:

The AND operator displays a record if both the first condition and the second condition is true.

Syntax: 

SELECT [COLUMN NAME] | [ALL] FROM <TABLE NAME> WHERE <FIRST_CONDITION> AND <SECOND_CONDITION>

Example:

 ---- SELECT USERLOGININFO ROW FIELD WHERE BOTH CONDITION TRUE
  
SELECT * FROM USERLOGININFO WHERE id = 103 AND NAME = 'HAPPY'

Select command with OR operator:

The OR operator displays a record if either the first condition or the second condition is true.

Syntax:  SELECT [COLUMN NAME] | [ALL] FROM <TABLE NAME> WHERE <FIRST_CONDITION> AND <SECOND_CONDITION>

Example:

 ---- SELECT USERLOGININFO ROW FIELD WHERET ONE CONDITION TRUE
  
SELECT * FROM USERLOGININFO WHERE id = 103 OR id=102

SELECT command with AND/OR operator:

We can use ‘AND’ and ‘OR’ operator both concurrently.

Syntax:  

   SELECT [SPECIFIED COLUMN NAME] | [ALL] FROM <TABLE NAME> WHERE <CONDITION1> AND/OR <CONDITON WITHIN CONDITON> OR DO IT VICE VERSA.

Example:

---- SELECT USERLOGININFO ROW FIELD WHERET TWO CONDITION TRUE
  
SELECT * FROM USERLOGININFO WHERE id = 103 AND(Name = 'HAPPY' OR Name ='VARUNSINGH')

SELECT command with NOT EQUAL (<>) operator:

Not Equal (<>) operator displays a records when condition not equal to provide condition.

Syntax:

SELECT [COLUMN NAME] | [ALL] FROM <TABLE_NAME> WHERE <CONDITION>

Example:

---- SELECT USERLOGININFO ROW FIELD WHERET CONDITION TRUE
  
SELECT * FROM USERLOGININFO WHERE id <> 103

SELECT command with ‘LIKE’ operator:

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. With ‘Like’ operator we must be use SQL wildcards for search data in specified pattern.

Syntax:

SELECT [SPECIFIED _COLUMN_ NAME]   |   [ALL] FROM <TABLE_NAME> WHERE [COLUMN_NAME] LIKE   [SEARCH_PATTERN]

Example:

---- SELECT USERLOGININFO ROW FIELD WHERE SEARCH PATTERN MATCH
  
SELECT * FROM USERLOGININFO WHERE Name like 'HA%'

SELECT command with IN operator:

The IN operator allows you to specify multiple values in a WHERE clause and retrieve that data field value where condition match.

Syntax:

SELECT [SPECIFIED_COLUMN_NAME]  |     [ALL]  FROM  <TABLE_NAME> WHERE   [COLUMN_NAME] IN <VALUE1, VALUE2, …………….VALUEN>

Example:

---- SELECT USERLOGININFO ROW FIELD WHERE SEARCH PATTERN MATCH
  
SELECT * FROM USERLOGININFO WHERE id in (101,102,103,104,105,106)

SELECT command with UNION operator:

The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Syntax:

                SELECT [SPECIFIED_COLUMN_NAME] | [ALL] FROM <TABLE _NAME 1>

                UNION

                SELECT [SPECIFIED_COLUMN_NAME] | [ALL] FROM <TABLE _NAME 2>

Example:

 SELECT * FROM STUDENT_DETAIL UNION  
SELECT * FROM STUDENT_DETAILS2

There is a little difference between ‘UNION ‘and ‘UNION  ALL’ in case of ‘UNION ALL’ duplicate data are consider where as in case ‘UNION’ does not.