Oracle Conditions
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
 
COMPOUND 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;
 
EXISTS Condition
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@pdbdev

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@pdbdev

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@pdbdev

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

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);
 
JSON Conditions
For JSON conditions use the JSON link at page bottom.
 
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;
/
MEMBER OF Tests whether an element is a member of a nested table
conn oe/oe@pdbdev

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@pdbdev

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;
/
NOT MEMBER OF 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 NOT MEMBER OF (x);

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

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 MEMBER OF conn hr/hr@pdbdev

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;
/
SUBMULTISET

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

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;
NOT SUBMULTISET

Tests whether a specified nested table is a submultiset of another specified nested table		 <nested_table1> NOT SUBMULTISET [OF] <nested_table2>
SELECT customer_id, cust_address_ntab1
FROM customer_demo
WHERE cust_address_ntab1 NOT SUBMULTISET OF cust_address_ntab2;
 
MULTISET (Nested Table) Conditions
IS A SET Tests whether a specified nested table is composed of unique elements
<neseted_table> IS A SET
conn oe/oe@pdbdev

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 NOT A SET Tests whether a specified nested table is composed of unique elements
<neseted_table> IS NOT A SET
conn oe/oe@pdbdev

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 NOT A SET
AND customer_id < 106;
IS EMPTY Tests whether a specified nested table is empty
<neseted_table> IS EMPTY
conn oe/oe@pdbdev

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 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;
IS NOT EMPTY Tests whether a specified nested table is not empty
<neseted_table> IS NOT EMPTY
SELECT COUNT(*)
FROM customer_demo
WHERE cust_address_ntab IS NOT EMPTY;
 
NULL Conditions
IS NULL IS NULL(s VARCHAR2 CHARACTER SET ANY_CS) RETURN BOOLEAN;
conn scott/tiger@pdbdev

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@pdbdev

SELECT ename FROM emp WHERE comm IS NOT NULL;
 
PATH Conditions (also referred to by Oracle as "Functions" and "Ancillary Operators"
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;
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;
 
PATTERN MATCHING Conditions (also follow the link to Wildcards at page bottom)
Condition Variations
Variant Purpose
LIKE defined by the input character set
LIKEC with Unicode
LIKE2 with UCS2 code points
LIKE4 with UCS4 code points
LIKE [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;
LIKE with ending wildcard Find any string that begins with the letter 'S'
conn scott/tiger@pdbdev

SELECT ename FROM emp WHERE ename LIKE 'S%';
LIKE with leading wildcard Find any string that ends with the letter 'S'
SELECT ename FROM emp WHERE ename LIKE '%S';
LIKE with multiple wildcards Find any string that contains, anywhere, the letter 'S'
SELECT ename FROM emp WHERE ename LIKE '%S%';
LIKE with 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 [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 [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 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;
 
REGULAR EXPRESSION Conditions
REGEXP_LIKE See Regular Expressions Link under Related Topics, at page bottom
 
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
WHEREqu 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;
 
XML Conditions
EQUALS_PATH 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;
UNDER_PATH 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;

Related Topics
Built-in Functions
Built-in Packages
Delete Statements
Explain Plan
GROUP BY and HAVING CLauses
Insert Statements
JSON Functionality
Merge Statements
Model Clause
Nested Tables
NULL
Object Tables
Operators (Built-in)
Regular Expressions
Select Statements
Update Statements
WHERE Clause
WildCards
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx