So far in earlier oracle sql tutorial, we have learned about select from the table where all the rows are being returned.
Now in this Oracle sql tutorial we learn about restricting the date set (number of rows selected) from the table.
This is achieved through where clause in the sql statement
Basic sql statment with where clause is of the form
select [Distinct] { *,col alias…..} from table [where conditions ] ;
We have already explained the other parts in the earlier chapter ,the things which need to be explained is the where conditions
1) Where signifies to restrict the number of rows returned from the table based on the conditions given
2) Conditions can be column names, comparison operator, constant
3) column names can be compared to literal,column name using comparison operator
Lets us understand by some examples
select ename ,salary from dept where salary =5000
Here in this example
Column salary is compared to constant 5000 with the comparison operator =
select ename ,salary from dept where salary > 2000
Here in this example
Column salary is compared to constant 2000 with the comparison operator >
select ename ,salary from dept where salary = salary1
Here in this example
Column salary is compared to column name salary1 with the comparison operator =
So in general we can say about this where clause
It consists of three parts
1) Column name
2) Comparison operator
3) column name ,constant or list of values
More things on where clause conditions
| Character and dates:We used single quotes for specifying
character and dates. It is case sensitive. The default display for date is DD-MON-YY |
select ename ,salary from dept where ename=’NATHAN’ select ename ,salary from dept where ename=’john’ select ename ,salary from dept where hire_date=’01-OCT-14′; |
| Common Comparison Operator: They are >(greater than) ,
<(less than), <=(less than or equal to >,>=(not equal to) |
select ename ,salary from dept where salary =5000; |
| Between .. and ( Between the value) | select ename ,salary from dept where salary between 2000 and 3000; |
| IN Operator: This is use to find the value of column
in a list of values |
select ename ,salary from dept where ename in (‘JOHN’,’NATHAN’); |
| Like Operator: This is use to find the value of column
like a particular value. % is used specially in that ,it denotes zero to any character. We can do wildcard searches of any string using like |
select ename ,salary from dept where ename like ‘%ATH%’; |
| Is NULL Operator: Null means no values or unassigned values. We cannot test that with = operator. | select ename ,salary from dept where surname is null ; |