Replace IN operator with or operator : AND OR « Query Select « Oracle PL/SQL Tutorial

SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;

Table created.

SQL>
SQL>
SQL> insert into employees values(1,'Jason',  'N',  'TRAINER', 2,   date '1965-12-18',  800 , NULL,  10);

1 row created.

SQL> insert into employees values(2,'Jerry',  'J',  'SALESREP',3,   date '1966-11-19',  1600, 300,   10);

1 row created.

SQL> insert into employees values(3,'Jord',   'T' , 'SALESREP',4,   date '1967-10-21',  1700, 500,   20);

1 row created.

SQL> insert into employees values(4,'Mary',   'J',  'MANAGER', 5,   date '1968-09-22',  1800, NULL,  20);

1 row created.

SQL> insert into employees values(5,'Joe',    'P',  'SALESREP',6,   date '1969-08-23',  1900, 1400,  30);

1 row created.

SQL> insert into employees values(6,'Black',  'R',  'MANAGER', 7,   date '1970-07-24',  2000, NULL,  30);

1 row created.

SQL> insert into employees values(7,'Red',    'A',  'MANAGER', 8,   date '1971-06-25',  2100, NULL,  40);

1 row created.

SQL> insert into employees values(8,'White',  'S',  'TRAINER', 9,   date '1972-05-26',  2200, NULL,  40);

1 row created.

SQL> insert into employees values(9,'Yellow', 'C',  'DIRECTOR',10,  date '1973-04-27',  2300, NULL,  20);

1 row created.

SQL> insert into employees values(10,'Pink',  'J',  'SALESREP',null,date '1974-03-28',  2400, 0,     30);

1 row created.

SQL>
SQL> select empno, job, bdate
  2  from   employees
  3  where  bdate < date '1960-01-01'
  4  and    job in ('TRAINER','SALESREP');

no rows selected

SQL>
SQL>
SQL> select empno, job, bdate
  2  from   employees
  3  where  bdate < date '1960-01-01'
  4  and   (job = 'TRAINER' or job = 'SALESREP');

no rows selected

SQL>
SQL> drop table employees;

Table dropped.

2.11.AND OR
	2.11.1.	Use AND to link two conditions
	2.11.2.	Use 'OR' to link two conditions
	2.11.3.	Combine three conditions with OR
	2.11.4.	(state = 'CA') OR (state <> 'CA')
	2.11.5.	1=1 or 1=0 and 0=1 (case 1)
	2.11.6.	(1=1 or 1=0) and 0=1 (case 2)
	2.11.7.	1=1 or (1=0 and 0=1) (case 3)
	2.11.8.	CONDITION
	2.11.9.	Combine NOT and AND
	2.11.10.	Not Equal and OR
	2.11.11.	Replace IN operator with or operator
	2.11.12.	Combine conditions with AND and OR
	2.11.13.	Use parenthesis to change the order of AND and OR