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 |
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 |
INFINITE |
Determines whether an expression is infinite r is the undefined result of an operation (that is, is not a number) |
-- to create all_objs: click here
SELECT COUNT(*) FROM all_objs; WHERE data_object_id IS NOT INFINITE; |
NAN |
Determines whether an expression is infinite r is the undefined result of an operation (that is, is not a number) |
-- to create all_objs: click here
SELECT COUNT(*) FROM all_objs WHERE data_object_id IS NOT NAN; |
|
GROUP COMPARISON |
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); |
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 |
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('Cline',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 |
LIKE( str VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET str%CHARSET) RETURN BOOLEAN;
LIKE( str VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET str%CHARSET, esc VARCHAR2 CHARACTER SET str%CHARSET) RETURN BOOLEAN; |
Syntax |
NOT_LIKE( str VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET str%CHARSET) RETURN BOOLEAN;
NOT_LIKE( str VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET str%CHARSET, esc 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%'; |
|
LOGICAL |
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 |
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 (<expression_list>) |
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; / |
|
NESTED TABLE |
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; |
Submultiset |
Tests whether a specified nested table is a submultiset of another specified 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_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 |
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 |
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 |
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 EXPRESSIONS |
REGEXP_LIKE |
See Regular Expressions Link under Related Topics |
|
SINGLE COMPARISON |
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; |