Oracle Tutorial: Restricting rows, BETWEEN ,IN,LIKE,NULL Conditions and Sorting in Oracle

WHERE Clause in SQL

WHERE clause is used to restrict rows. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true, the row meeting the condition is returned.

Syntax: SELECT <Columns Name> from <Table Name> WHERE <Conditions>;

In Oracle SQL query is not case sensitive but data values are case sensitive. for example in employee table ename column value 'vimal' is different from value 'VIMAL'.

When you write where clause for string(varchar, char etc) and date data type, values must enclosed data in single quotation('string') otherwise query will generate error message.

Comparison Conditions in Query

Comparison operator like
= equal to
< less than
> greater than
<= less than equal to
>= greater than equal to
can be used in WHERE clause to restrict rows.

BETWEEN .......AND Condition

This is also a comparison condition. comparison based on two values both are inclusive.

Syntax : Select <colums> from <table name> Where <Col Name> BETWEEN <Min inclusive value> AND <Max. inclusive value>;

IN Condition
IN Condition use multiple values to restrict rows. Multiple values are comma separated and inside the bracket .
Ex. shows IN condition for numeric values.

Ex. Shows IN Condition for string value.

LIKE Condition
Like Condition perform character pattern matching operation. This operation can only be used for string.
Two symbol % and _ is used in LIKE Conditions. these character is called wildcard character.

% denotes zero or many characters.
_ denotes one character.

Suppose you want to get all employee names whose names begin with 'S' or suppose a condition where you want to get employee names where second character is 'A'.
It is widely used command in programming for searching string.

You can also search date values by LIKE Condition.

ESCAPE Option in LIKE Condition.

ESCAPE Option is used to include symbol % and _ into search pattern. imagine a condition when you want to search employee ename whose second character is '_', now if you write query like

Select * from employee where ename like '_ _%'.

It won't display desire output because '_' is a special symbol in LIKE Condition. Now you can change the definition of '_' using ESCAPE Option.

Now you can see i have inserted two rows in employee table. here column desig contain '_' and '%' .

Search Employee designation where designation contain '_'.
here '\' is used before '_' and escape '\' removes the definition of '_'. you can use other symbol in place of '\'
for Ex. All these query are similar.
Select * from employee where desig like '%\_%' escape '\';
Select * from employee where desig like '%+_%' escape '+';
Select * from employee where desig like '%#_%' escape '#';
Select * from employee where desig like '%&_%' escape '&';

NULL Condition
Null value means value is

Unavailable
Unassigned
Unknown
Inapplicable

Two keywords for null or not null values are

is null
is not null

Note : Mathematical operation with null value always return null.

Is null return value based on column null values.

is not null display value that doesn't contain null value.

Logical Condition
AND Operator
Syntax: Select <columns> from <table> where <Condition1> AND <Condition2>;

Query returns value if both condition are true.

OR Operator
Syntax: Select <columns> from <table> where <Condition1> OR <Condition2>;

Query returns value if either Condition1 is true or Condition2 is true or both are true.

NOT Operator
Syntax: Select <columns> from <table> where NOT in <Condition>;

Query returns value only if given condition is unmatched.

Operator Precedence

Sorting in Oracle Select Query
Order By Clause
Order By Clause is used in oracle for sorting. By default order by sort record in ascending order.

Syntax: Select <Columns> from <table name> ORDER BY <column1,column2..> Asc/Desc;

Order by Example in string values.