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('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 |
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( VARCHAR2 CHARACTER SET ANY_CS) RETURN fs; |
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; |