SQL Operators

SQL Operators:

Apart from standard relational operators (= and >), SQL has some other operators that can

be used in conditions.

BETWEEN value-1 AND value-2 Checks whether the value is in the given range including the min and max values. This supports DATE type data also.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(,). If the value is matching with any of the values given in the list then condition is taken as true.

LIKE pattern Checks whether the given string is matching with the given pattern.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.

IS NULL and IS NOT NULL Checks whether the value is null or not null.

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

Java Language

Note: BETWEEN.. AND is alternative to using >= and <= operators.

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.

% Zero or more characters can take the place of %. _ (underscore) 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. 

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.

Selecting distinct values:

DISTINCT clause of SELECT command specifies only distinct values of the specified column must be selected. The following SELECT command will display only distinct course fee values from COURSES

table.

select distinct fee from courses;

FEE

---------

3500

4000

4500

5000

5500

Whereas the same query without DISTINCT clause will select the following. 

select fee from courses;

FEE

---------

4500

5500

3500

5000

4500

4000