Oracle Conditions

Oracle Conditions Version 11.2.0.3

General Information

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.

<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

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

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;