Oracle Conditions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Condition Precedence

SQL Operators are evaluated before conditions
Order Operator
1 =, !=, <, >, <=, >=, !=, <>, ^=, ~=
2 IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF TYPE
3 NOT
4 AND
5 OR
 
EXISTS
Note: the subquery SELECT can return NULL, 0, *, or a table column ... the optimizer does not care
EXISTS Semijoin EXISTS (<subquery>)
SELECT table_name
FROM user_tables t
WHERE EXISTS (
  SELECT table_name
  FROM user_indexes i
  WHERE i.table_name = t.table_name);

SELECT COUNT(*)
FROM serv_inst
WHERE srvr_id = 503;

SELECT COUNT(*)
FROM dual
WHERE EXISTS (
  SELECT NULL
  FROM serv_inst
  WHERE srvr_id = 503
  AND srvr_id IS NOT NULL);
NOT EXISTS NOT EXISTS (<subquery>)
SELECT table_name
FROM user_tables t
WHERE NOT EXISTS (
  SELECT table_name
  FROM user_indexes i
  WHERE i.table_name = t.table_name);
 
FLOATING POINT Conditions
INFINITE standard.'IS INFINITE' (N NUMBER) RETURN BOOLEAN;
standard.'IS INFINITE' (F BINARY_FLOAT) RETURN BOOLEAN;
standard.'IS INFINITE' (D BINARY_DOUBLE) RETURN BOOLEAN;

standard.'IS NOT INFINITE' (N NUMBER) RETURN BOOLEAN;
standard.'IS NOT INFINITE' (F BINARY_FLOAT) RETURN BOOLEAN;
standard.'IS NOT INFINITE' (D BINARY_DOUBLE) RETURN BOOLEAN;

Determines whether an expression is infinite r is the undefined result of an operation (that is, is not a number)
SELECT COUNT(*)
FROM all_objects
WHERE data_object_id IS NOT INFINITE;
NAN standard.'IS NAN' (N NUMBER) RETURN BOOLEAN;
standard.'IS NAN' (F BINARY_FLOAT) RETURN BOOLEAN;
standard.'IS NAN' (D BINARY_DOUBLE) RETURN BOOLEAN;

standard.'IS NOT NAN' (N NUMBER) RETURN BOOLEAN;
standard.'IS NOT NAN' (F BINARY_FLOAT) RETURN BOOLEAN;
standard.'IS NOT NAN' (D BINARY_DOUBLE) RETURN BOOLEAN;

Determines whether an expression is infinite r is the undefined result of an operation (that is, is not a number)
SELECT COUNT(*)
FROM all_objects
WHERE data_object_id IS NOT NAN;
 
GROUP COMPARISON Conditions
ALL Test

Evaluates to TRUE if the query returns no rows
ALL(expression_list | sub_query)
conn scott/tiger

SELECT ename, sal, deptno
FROM emp;

SELECT sal
FROM emp WHERE deptno = 30;

SELECT ename, sal, deptno FROM emp WHERE sal >= ALL (
SELECT sal FROM emp WHERE deptno = 30);

conn uwclass/uwclass

SELECT COUNT(*)
FROM servers
WHERE srvr_id = ALL(1,2,3,4,5);

SELECT COUNT(*)
FROM servers
WHERE srvr_id >= ALL(1,2,3,4,5);

SELECT COUNT(*)
FROM servers
WHERE srvr_id <= ALL(1,2,3,4,5);

SELECT COUNT(*)
FROM servers
WHERE srvr_id <> ALL(1,2,3,4,5);
ANY Test

Evaluates to FALSE if the query returns no rows
ANY(expression_list | sub_query)
conn scott/tiger

SELECT ename, sal, deptno FROM emp WHERE sal >= ANY
(SELECT sal FROM emp WHERE deptno = 30);

Note: <col> = ANY(list) is equivalent to <col> IN (list)
SOME Test

Evaluates to FALSE if the query returns no rows
SOME(expression_list | sub_query)
conn scott/tiger

SELECT ename, sal, deptno FROM emp WHERE sal >= SOME (
SELECT sal FROM emp WHERE deptno = 30);
 
INTERROW Conditions
IS ANY See the Model Clause link under Related Topics
IS PRESENT See the Model Clause link under Related Topics
 
IS OF TYPE
Basic syntax Tests object instances based on specific type information
conn uwclass/uwclass

CREATE TYPE person_t AS OBJECT
(name VARCHAR2(30), ssn NUMBER) NOT FINAL;
/

CREATE TYPE employee_t UNDER person_t
(dept_id NUMBER, salary NUMBER) NOT FINAL;
/

CREATE TYPE part_time_emp_t UNDER employee_t
(num_hrs NUMBER);
/

CREATE TABLE persons OF person_t;

INSERT INTO persons VALUES (person_t('Morgan',1234));
INSERT INTO persons VALUES (employee_t('Morle',32456,12,10000));
INSERT INTO persons VALUES (part_time_emp_t('Kyte',5678,13,1000,20));

SELECT *
FROM persons;

SELECT *
FROM persons p
WHERE VALUE(p) IS OF TYPE (employee_t);
With NOT Operator Tests object instances based on specific type information
SELECT *
FROM persons p
WHERE VALUE(p) IS NOT OF (ONLY part_time_emp_t);
With ONLY Clause Tests object instances based on specific type information
SELECT *
FROM persons p
WHERE VALUE(p) IS OF (ONLY part_time_emp_t);
 
LIKE (Be sure to check the link on Wildcards at the bottom of the page)
Syntax [NOT] LIKE(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

[NOT] LIKE(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET,
esc IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

[NOT] LIKE(
str IN CLOB CHARACTER SET ANY_CS,
pat IN CLOB CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

[NOT] LIKE(
str IN CLOB CHARACTER SET ANY_CS,
pat IN CLOB CHARACTER SET str%CHARSET,
esc IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;
Condition Variations
Variant Purpose
LIKE defined by the input character set
LIKEC with Unicode
LIKE2 with UCS2 code points
LIKE4 with UCS4 code points
Ending Wildcard Find any string that begins with the letter 'S'
conn scott/tiger

SELECT ename FROM emp WHERE ename LIKE 'S%';
Leading Wildcard Find any string that ends with the letter 'S'
SELECT ename FROM emp WHERE ename LIKE '%S';
Multiple Wildcards Find any string that contains, anywhere, the letter 'S'
SELECT ename FROM emp WHERE ename LIKE '%S%';
Single Character Wildcard Find any string that contains the letter 'A' followed by any single character which followed by the letter 'E'
SELECT ename FROM emp WHERE ename LIKE '%A_E%';
 
LIKEC
Syntax [NOT] LIKEC(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

[NOT] LIKEC(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET,
esc IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;
 
LIKE2
Syntax [NOT] LIKE2(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

[NOT] LIKE2(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET,
esc IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;
 
LIKE4
Syntax LIKE4(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

LIKE4(
str IN VARCHAR2 CHARACTER SET ANY_CS,
pat IN VARCHAR2 CHARACTER SET str%CHARSET,
esc IN VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;
 
LOGICAL Conditions
AND     <value_or_expression> >= <value_or_expression>
AND <value_or_expression> >=<value_or_expression>
SELECT table_name
FROM all_tables
WHERE initial_extent IS NOT NULL
AND next_extent IS NULL;
NOT WHERE <value_or_expression> NOT <condition> <comparison_condition> <value_or_expression>
See demos for EMPTY, EXISTS, IN, INFINITE, and NULL.
OR    <value_or_expression> >= <value_or_expression>
OR <value_or_expression> >= <value_or_expression>
SELECT owner, table_name
FROM all_tables
WHERE (table_name LIKE 'D%' OR owner = 'SYSTEM');
 
MEMBERSHIP Conditions
IN (list) <value_or_expression> IN (<expression_list>)
SELECT owner, table_name
FROM all_tables
WHERE owner IN ('SYS', 'SYSTEM');
IN (subquery) <value_or_expression> IN (<subquery_result>)
SELECT owner, table_name
FROM all_tables
WHERE initial_extent IN (
  SELECT MIN(initial_extent)
  FROM all_tables);
NOT IN Antijoin <value_or_expression> NOT IN (<expression_list>)
SELECT owner, table_name
FROM all_tables
WHERE initial_extent NOT IN (
  SELECT MIN(initial_extent)
  FROM all_tables);
Complex IN Demo Using CAST -- based on emp table in scott/tiger

set serveroutput on

DECLARE
 i     PLS_INTEGER;
 InStr VARCHAR2(20) := '10';
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno IN (InStr);

  dbms_output.put_line(i);
END;
/

--==============================

DECLARE
 i     PLS_INTEGER;
 InStr VARCHAR2(20) := '10,30';
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno IN (InStr);

  dbms_output.put_line(i);
END;
/

--==============================

CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(20);
/

DECLARE
 i PLS_INTEGER;
 x InStrTab := InStrTab('10','30');
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno IN (
    SELECT column_value
    FROM TABLE(CAST(x AS InStrTab))
);

  dbms_output.put_line(i);
END;
/

-- or

DECLARE
 i PLS_INTEGER;
 x InStrTab := InStrTab('10','30');
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno IN (
    SELECT column_value
    FROM TABLE(x)
);

  dbms_output.put_line(i);
END;
/
Complex IN Demo Using MEMBERSHIP OF conn scott/tiger@pdborcl

CREATE OR REPLACE TYPE InStrTab IS TABLE OF NUMBER(2);
/

set serveroutput on

DECLARE
 i PLS_INTEGER;
 x InStrTab := InStrTab(10,30);
BEGIN
  SELECT deptno BULK COLLECT INTO x
  FROM dept WHERE deptno IN (10,30);

  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno MEMBER OF (x);

  dbms_output.put_line(i);
END;
/
Another Demo Using MEMBERSHIP OF conn hr/hr@pdborcl

CREATE OR REPLACE TYPE t_deptno IS TABLE OF NUMBER(10);
/

DECLARE
 depts t_deptno;
BEGIN
  SELECT department_id BULK COLLECT INTO depts
  FROM departments
  WHERE department_id IN (10,20);

  FOR i in 1 .. 10000 LOOP
    FOR rec IN (SELECT * FROM employees e WHERE e.department_id MEMBER OF (depts)) LOOP
      NULL;
    END LOOP;
  END LOOP;
END;
/
 
NESTED TABLE Conditions
Is A Set Tests whether a specified nested table is composed of unique element
conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab cust_address_tab_typ)
NESTED TABLE cust_address_ntab STORE AS cust_add_ntab_store;

desc customer_demo

UPDATE customer_demo cd
SET cust_address_ntab = CAST(MULTISET(SELECT cust_address
  FROM customers c
  WHERE c.customer_id = cd.customer_id)
  AS cust_address_tab_typ);

set linesize 121

SELECT customer_id, cust_address_ntab
FROM customer_demo
WHERE cust_address_ntab IS A SET
AND customer_id < 106;
Is Empty Tests whether a specified nested table is empty
conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab cust_address_tab_typ)
NESTED TABLE cust_address_ntab STORE AS cust_add_ntab_store;

desc customer_demo

SELECT COUNT(*)
FROM customer_demo
WHERE cust_address_ntab IS NOT EMPTY;

UPDATE customer_demo cd
SET cust_address_ntab = CAST(MULTISET(SELECT cust_address
  FROM customers c
  WHERE c.customer_id = cd.customer_id)
  AS cust_address_tab_typ)
WHERE ROWNUM <11;

SELECT COUNT(*)
FROM customer_demo
WHERE cust_address_ntab IS NOT EMPTY;
Member Tests whether an element is a member of a nested table
conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab cust_address_tab_typ)
NESTED TABLE cust_address_ntab STORE AS cust_add_ntab_store;

desc customer_demo

UPDATE customer_demo cd
SET cust_address_ntab = CAST(MULTISET(SELECT cust_address
  FROM customers c
  WHERE c.customer_id = cd.customer_id)
  AS cust_address_tab_typ);

SELECT customer_id, cust_address_ntab
FROM customer_demo
WHERE cust_address_typ(';8768 N State Rd 37';, 47404, ';Bloomington';, ';IN';, ';US';)
MEMBER OF cust_address_ntab;
conn scott/tiger

CREATE OR REPLACE TYPE deptno_t IS TABLE OF NUMBER(2);
/

set serveroutput on

DECLARE
 dept_array deptno_t;

 CURSOR empcur IS
 SELECT e.ename
 FROM emp e
 WHERE e.deptno MEMBER OF (dept_array);
BEGIN
   SELECT deptno BULK COLLECT INTO dept_array
   FROM dept WHERE deptno IN (10,30);

   FOR emprec IN empcur LOOP
     dbms_output.put_line(emprec.ename);
   END LOOP;
END;
/
Submultiset

Tests whether a specified nested table is a submultiset of another specified nested table
<nested_table1> [NOT] SUBMULTISET [OF] <nested_table2>
conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab1 cust_address_tab_typ,
     cust_address_ntab2 cust_address_tab_typ)
NESTED TABLE cust_address_ntab1 STORE AS cust_add_ntab1_store
NESTED TABLE cust_address_ntab2 STORE AS cust_add_ntab2_store;

desc customer_demo

UPDATE customer_demo cd
SET cust_address_ntab1 = CAST(MULTISET(SELECT cust_address
  FROM customers c
  WHERE c.customer_id = cd.customer_id)
  AS cust_address_tab_typ),
    cust_address_ntab2 = CAST(MULTISET(SELECT cust_address
  FROM customers c
  WHERE c.customer_id = cd.customer_id)
  AS cust_address_tab_typ);

SELECT customer_id, cust_address_ntab1
FROM customer_demo
WHERE cust_address_ntab1 SUBMULTISET OF cust_address_ntab2;
 
NULL Conditions
IS NULL IS NULL(s VARCHAR2 CHARACTER SET ANY_CS) RETURN BOOLEAN;
conn scott/tiger

SELECT ename FROM emp WHERE comm IS NULL;
IS NOT NULL IS NOT NULL(s VARCHAR2 CHARACTER SET ANY_CS) RETURN BOOLEAN;
conn scott/tiger

SELECT ename FROM emp WHERE comm IS NOT NULL;
 
PATH Conditions
DEPTH DEPTH is an ancillary function used only with the UNDER_PATH and EQUALS_PATH conditions. It returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable.
conn / as sysdba

desc resource_view

SELECT path(1), depth(2)
FROM resource_view
WHERE UNDER_PATH(res, '/sys/schemas', 1)=1
AND UNDER_PATH(res, '/sys/schemas', 2)=1;
EQUALS_PATH The EQUALS_PATH condition determines whether a resource in the Oracle XML database can be found in the database at a specified path.
SELECT any_path
FROM resource_view
WHERE EQUALS_PATH(res, '/sys/schemas/PUBLIC/www.w3.org')=1;
PATH Returns the relative path that leads to the resource specified in the parent condition. Only used with EQUALS_PATH and UNDER_PATH.
SELECT PATH(1), DEPTH(2)
FROM resource_view
WHERE UNDER_PATH(res, '/sys/schemas/PUBLIC', 1)=1
AND UNDER_PATH(res, '/sys/schemas/PUBLIC', 2)=1;
UNDER_PATH The UNDER_PATH condition determines whether resources specified in a column can be found under a particular path specified by path_string in the Oracle XML database repository. The path information is computed by the RESOURCE_VIEW view, which you query to use this condition.
SELECT any_path
FROM resource_view
WHERE UNDER_PATH(res, '/sys/schemas/PUBLIC/www.w3.org')=1;
 
RANGE Conditions
BETWEEN <value_or_expression> BETWEEN <value> AND <value>
SELECT table_name, num_rows
FROM all_tables
WHERE num_rows BETWEEN 100 AND 500;
NOT BETWEEN <value_or_expression> NOT BETWEEN <value> AND <value>
SELECT table_name, num_rows
FROM all_tables
WHERE num_rows NOT BETWEEN 100 AND 500;
 
REGULAR EXPRESSION Conditions
REGEXP_LIKE See Regular Expressions Link under Related Topics
 
SINGLE COMPARISON Conditions
Equal <value_or_expression> = <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent = next_extent;
Not Equal <value_or_expression> <> <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent <> next_extent;
<value_or_expression> != <value_or_expression> 
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent != next_extent;
<value_or_expression> ^= <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent ^= next_extent;
Not Equal (PL/SQL only) <value_or_expression> ~= <value_or_expression>
set serveroutput on

BEGIN
  IF 1 ~= 0 THEN
    dbms_output.put_line('1 is not equal to zero.');
  END IF;
END;
/
Less Than <value_or_expression> < <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent < next_extent;
Less Than Or Equal To <value_or_expression> <= <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent <= next_extent;
Greater Than <value_or_expression> > <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent > next_extent;
Greater Than Or Equal To <value_or_expression> >= <value_or_expression>
SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent >= next_extent;

Related Topics
Delete Statements
Explain Plan
GROUP BY and HAVING CLauses
Insert Statements
Merge Statements
Model Clause
Nested Tables
NULL
Object Tables
Operators (Built-in)
Regular Expressions
Select Statements
Update Statements
WHERE Clause
WildCards

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved