SQL Server : Comparison | Range | List Operator

Previous Articles - SQL Server : Arithmetic Operator

Comparison Operator's :

   The Commmand Syntax is :

 SELECT column_list FROM table_name Where expression1 comparison_operator expression2

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or Equal to
<=	Less than or Equal to
<>, !=	Not Equal to
!>	Not Greater than
!<	Not Less than
()	Controls Procedence

Example :

SELECT title FROM title WHERE type='business' 
SELECT stor_id, ord_num, title_id FROM sales WHERE qty>20
SELECT type, title_id, price FROM titles WHERE price*vtd sales < advance.

Range Operator's :

The range operator is used to retrieve data that can be extracted in ranges. The range operations are:

1. BETWEEN
2. NOT BETWEEN

The Syntax :

  SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression2

Example :
=======
      
    1. SELECT title FROM titles WHERE advance BETWEEN 2000 AND 5000
    2. SELECT title FROM titles WHERE advance NOT BETWEEN 4000 AND 5000

List Operator's :

The Syntax :

 SELECT column_list FROM table_name WHERE expression list_operator('value_list')

Example :
=======

    1. SELECT pub_name, city, state FROM publishers WHERE state IN ('CA', 'NY')
    2. SELECT pub_name, city, state FROM publishers WHERE state NOT IN ('CA', 'NY')

String Operator's :

SQL Server provides a pattern-matching method for string expressions using the LIKE keyword with the wildcard characters. The LIKE keyword is used to select those rows that match the specified portion of character string. The Like keyword allows wildcard characters that can be used as a part of an expression.

Wild Card	Description
%	Represents any string of zero or more characters(s)
-	Represents a single character
[]	Represents any single character within the specified range.
[^]	Represents any single character not within the specified range.

Example's of the LIKE Operator with wildcards.

Example	Description
SELECT title FROM titles WHERE type LIKE 'bus%'	Returns all titles from titles table where first three characters of the column type are 'bus'
SELECT * FROM publishers WHERE country LIKE 'US_'	Returns all rows from publishers table where country name is three characters long and starts with US where the third character can be anything.
SELECT title_id, price FROM titles WHERE title_id LIKE 'P[SC]]%'	Returns all columns from the titles table where title_id starts with the character P and Contains S or C in the second position followed by any number of characters.
SELECT title_id, price FROM titles WHERE title_id LIKE 'P[^C]%'	Returns all title_id and price from the titles table where title_id starts with P and does not contain and S as the second character and the third position onwards can contain any characters.

Logical Operator's :

1. OR - Returns the result when any of the specified search conditions is true.
2. AND - Return the result when all of the specified search condition are true.
3. NOT - Bnegates the expression that follows it.

 
  SELECT column_list FROM table_name WHERE conditional_expression{AND|OR}[NOT] conditional_expression

Example of Logical Operators:

Example	Description
SELECT * FROM publishers WHERE city='Los Vegas' OR city='New York'	Returns all the rows specific to the conditions, even if any one of the conditions is true.
SELECT publishers WHERE city='Los Vegas' AND city='Wasington D.C'	Returns all the rows specific to the conditions, when both the conditions are true.
SELECT * FROM publishers WHERE city='Los Vegas' OR NOT city='New York'	Returns all the rows specific to the conditions, except the rows specified with the condition after NOT operator.