SQL OPERATOR

A part from standard relational operators (= and >), SQL has some other operators that can be used in conditions.

Operator                                               What it does? BETWEEN value-1 AND value-2       Checks whether the value is in the
                                                              given range. The range is                                                                                                                           inclusive of the given values.
IN(list)                                                  Checks whether the value is matching  
                                                              with any one of the values given in the
                                                              list. List contains values separated by comma(,).
 LIKE pattern                                        Checks whether the given string is matching
                                                              with the given pattern. More on this later.
IS NULL and IS NOT NULL               Checks whether the value is null or not null.

Now, let us see how to use these special operators of SQL.

BETWEEN ... AND Operator  Checks whether value is in the given range. The range includes all the values in the range including the min and max values. This supports DATE type data also.

To display the list of course where DURATION is in the range 20 to 25 days, enter:

select name
from courses
 where duration between 20 and 25;
 NAME --------------------
Oracle database
C programming
ASP.NET
Java Language
Note: BETWEEN.. AND is alternative to using >= and <= operators.

IN Operator 

Compares a single value with a list of values. If the value is matching with any of the values given in the list then condition is taken as true.
The following command will retrieve all courses where duration is either 20 or 30 days.
select name
from courses
where duration in (20,30);
NAME --------------------
VB.NET
C programming
The same condition can be formed even without IN operator using logical operator OR as follows: Select name
from courses
where duration = 20 or duration = 30;
However, it will be more convenient to user IN operator compared with multiple conditions compared with OR operator.

LIKE operator 

This operator is used to search for values when the exact value is not known. It selects rows that match the given pattern. The pattern can contain the following special characters.

Symbol                                  Meaning 

% %. _ (underscore)       Zero or more characters can take the place
                                        of  Any single character can take the place
                                        of underscore. But there must be one letter.

To select the courses where the course name contains pattern .NET, enter:

select name,duration, fee from courses
where name like '%.NET%'
NAME          DURATION     FEE
-------------------- --------- ---------
VB.NET         30                   5500
ASP.NET       25                    5000
The following example selects courses where second letter in the course code is “b” and column PREREQUISITE contains word “programming”.

select * from courses where ccode like '_b%' and prerequisite like '%programming%';
CCODE            NAME          DURATION     FEE         PREREQUISITE
----- -------------------- --------- --------- ------------------------
vbnet                VB.NET            30                 5500          Windows and programming

Remember LIKE operator is case sensitive. In the above example, if CCODE contains value in uppercase (VB), then it won’t be a match to the pattern.

IS NULL and IS NOT NULL operators  

These two operators test for null value. If we have to select rows where a column is containing null value or not null value then we have to use these operators. For example the following SELECT command will select all the courses where the column FEE is null.
select * from courses
where fee is null;

Though Oracle provides NULL keyword, it cannot be used to check whether the value of a column is null. For example, the following condition will always be false as Oracle treats two null values as two different values.
select * from courses
where fee = null;

The above command does NOT work as fee though contains null value will not be equal to NULL. SO, we must use IS NULL operator.