Oracle SQL - Select WHERE Clause

  1. HOME
  2. Oracle SQL
  3. Select
  4. WHERE Clause

Introduction

With the WHERE clause, you can specify a condition to filter the rows for the result.

There are simple and compound conditions.

Simple conditions typically contain one of the SQL comparison operators listed in the following table.

Operator Description
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
= Equal to
<> Not equal to (alternative syntax: !=)

Expressions containing comparison operators constitute statements that can evaluate to TRUE or FALSE.

The following code shows an example of a WHERE clause with a simple condition. 

select ename, init, msal 
from   emp 
where  msal >= 3000;

WHERE clause with a simple condition using the <> (not equal to) operator. 

select dname, location 
from   departments 
where  location <> 'CHICAGO';

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w  w  .j a va 2  s  .c o m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> select ename, init, msal
  2  from   emp
  3  where  msal >= 3000;

ENAME    | INIT  |      MSAL
-------- | ----- | ---------
SCOTT    | DEF   |  03000.00
KING     | CC    |  05000.00
JONES    | R     |  08000.00
FORD     | MG    |  03000.00

SQL>
SQL> select dname, location
  2  from   departments
  3  where  location <> 'CHICAGO';

DNAME      | LOCATION
---------- | --------
ACCOUNTING | NEW YORK
TRAINING   | DALLAS
HR         | BOSTON

SQL>

Quiz