Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01





Go to previous page Go to next page

Expressions, Conditions, and Queries

This chapter describes how to combine the values, operators, and functions described in earlier chapters evaluate to a value.

This chapter includes these sections:


An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. An expression generally assumes the datatype of its components.

This simple expression evaluates to 4 and has datatype NUMBER (the same datatype as its components):


The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:


You can use expressions in:

For example, you could use an expression in place of the quoted string 'smith' in this UPDATE statement SET clause:

SET ename = 'smith'; 

This SET clause has the expression LOWER(ename) instead of the quoted string 'smith':

SET ename = LOWER(ename);

Expressions have several forms, as shown in the following syntax:


Oracle does not accept all forms of expressions in all parts of all SQL statements. You must use appropriate expression notation whenever expr appears in conditions, SQL functions, or SQL statements in other parts of this reference. The sections that follow describe and provide examples of the various forms of expressions.

See Also: The individual SQL statements in Chapter 7 through Chapter 11 for information on restrictions on the expressions in that statement 

Simple Expressions

A simple expression specifies column, pseudocolumn, constant, sequence number, or null.


In addition to the schema of a user, schema can also be "PUBLIC" (double quotation marks required), in which case it must qualify a public synonym for a table, view, or materialized view. Qualifying a public synonym with "PUBLIC" is supported only in data manipulation language (DML) statements, not data definition language (DDL) statements.

The pseudocolumn can be either LEVEL, ROWID, or ROWNUM. You can use a pseudocolumn only with a table, not with a view or materialized view. NCHAR and NVARCHAR2 are not valid pseudocolumn datatypes.

See Also: "Pseudocolumns" for more information on pseudocolumns 

Some valid simple expressions are:

'this is a text string'
N'this is an NCHAR string'

Compound Expressions

A compound expression specifies a combination of other expressions.


Note that some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within an aggregate function.

Some valid compound expressions are:

('CLARK' || 'SMITH') 
SQRT(144) + 72 

Variable Expressions

A variable expression specifies a host variable with an optional indicator variable. Note that this form of expression can appear only in embedded SQL statements or SQL statements processed in an Oracle Call Interface (OCI) program.


Some valid variable expressions are:

:employee_name INDICATOR :employee_name_indicator_var

Built-In Function Expressions

A built-in function expression specifies a call to a single-row SQL function.


Some valid built-in function expressions are:


See Also: "SQL Functions" and "Aggregate Functions" for information on built-in functions 

Function Expressions

A function expression specifies a call to

The optional expression/subquery list must match attributes of the function, package, or operator. Only scalar subqueries are supported.


Some valid user-defined function expressions are:

scott.payrol.tax_rate(dependents, empno)@ny

Type Constructor Expressions

A type constructor expression specifies a call to a type constructor. The argument to the type constructor is any expression or subquery. Only scalar subqueries are supported.


If type_name is an object type, then the expression/subquery list must be an ordered list, where the first argument is a value whose type matches the first attribute of the object type, the second argument is a value whose type matches the second attribute of the object type, and so on. The total number of arguments to the constructor must match the total number of attributes of the object type.

If type_name is a varray or nested table type, then the expression/subquery list can contain zero or more arguments. Zero arguments implies construction of an empty collection. Otherwise, each argument corresponds to an element value whose type is the element type of the collection type.

If type_name is an object type, a varray, or a nested table type, the maximum number of arguments it can contain is 1000 minus some overhead.

Expression Example

This example shows the use of an expression in the call to a type constructor.

  (no NUMBER, street CHAR(31), city CHAR(21), state CHAR(3), zip NUMBER); 
CREATE TYPE address_book_t AS TABLE OF address_t;
  /* Object Type variable initialized via Object Type Constructor */ 
  myaddr address_t = address_t(500, 'Oracle Parkway', 'Redwood Shores', 'CA',  94065);
  /* nested table variable initialized to an empty table via a constructor*/ 
  alladdr address_book_t = address_book_t(); 
  /* below is an example of a nested table constructor with two elements
     specified, where each element is specified as an object type constructor. */ 
  insert into employee values (666999, address_book_t(address_t(500,
     'Oracle  Parkway', 'Redwood Shores', 'CA', 94065), address_t(400,
     'Mission Street', 'Fremont', 'CA', 94555))); 

Subquery Example

This example illustrates the use of a subquery in the call to the type constructor.

   empno NUMBER, 
   ename VARCHAR2(20)); 
INSERT INTO emptbl VALUES(7377, 'JOHN'); 
   pname VARCHAR2(25), 
   empref REF employee); 
CREATE TABLE depttbl (dno number, proj project); 
INSERT INTO depttbl values(10, project('SQL Extensions',  
                                       (SELECT REF(p) FROM emptbl p 
                                        WHERE ename='JOHN')));

CAST Expressions

A CAST expression converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.


CAST allows you to convert built-in datatypes or collection-typed values of one type into another built-in datatype or collection type. You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

For the operand, expr can be either a built-in datatype or a collection type, and subquery must return a single value of collection type or built-in type. MULTISET informs Oracle to take the result set of the subquery and return a collection value. Table 5-1 shows which built-in datatypes can be cast into which other built-in datatypes. (CAST does not support LONG, LONG RAW, or any of the LOB datatypes.)

Table 5-1 Casting Built-In Datatypes











































a You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.

To cast a named collection type into another named collection type, the elements of both collections must be of the same type.

If the result set of subquery can evaluate to multiple rows, you must specify the MULTISET keyword. The rows resulting from the subquery form the elements of the collection value into which they are cast. Without the MULTISET keyword, the subquery is treated as a scalar subquery, which is not supported in the CAST expression. In other words, scalar subqueries as arguments of the CAST operator are not valid in Oracle8i.

Built-In Datatype Examples


Collection Examples

The CAST examples that follow use the following user-defined types and tables:

      (no NUMBER, street CHAR(31), city CHAR(21), state CHAR(2)); 
CREATE TYPE address_book_t AS TABLE OF address_t; 
CREATE TYPE address_array_t AS VARRAY(3) OF address_t; 
CREATE TABLE emp_address (empno NUMBER, no NUMBER, street CHAR(31), 
                             city  CHAR(21), state CHAR(2)); 
CREATE TABLE employees (empno NUMBER, name CHAR(31)); 
CREATE TABLE dept (dno NUMBER, addresses address_array_t); 

This example casts a subquery:

SELECT e.empno,, CAST(MULTISET(SELECT, ea.street,
                                     , ea.state 
                                        FROM emp_address ea
                                        WHERE ea.empno = e.empno)
                        AS address_book_t)
  FROM employees e; 

CAST converts a varray type column into a nested table:

SELECT CAST(d.addresses AS address_book_t)
   FROM dept d 
   WHERE d.dno = 111; 

The following example casts a MULTISET expression with an ORDER BY clause:

CREATE TABLE projects (empid NUMBER, projname VARCHAR2(10));
CREATE TABLE employees (empid NUMBER, ename VARCHAR2(10));
CREATE TYPE projname_table_type AS TABLE OF VARCHAR2(10);

An example of a MULTISET expression with the above schema is:

                             FROM projects p
                             WHERE p.empid=e.empid 
                             ORDER BY p.projname) 
  AS projname_table_type)
   FROM employees e;

CURSOR Expressions

A CURSOR expression returns a nested cursor. This form of expression is similar to the PL/SQL REF cursor.


A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:

Restrictions: The following restrictions apply to the CURSOR expression:


SELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum,
                                         FROM   projects p 
                                         WHERE  p.empno = e.empno)
                        FROM TABLE(d.employees) e)
  FROM dept d
  WHERE d.dno = 605; 

Object Access Expressions

An object access expression specifies attribute reference and method invocation.


The column parameter can be an object or REF column.

When a type's member function is invoked in the context of a SQL statement, if the SELF argument is null, Oracle returns null and the function is not invoked.

Examples in this section use the following user-defined types and tables:

   (empid NUMBER,
    name VARCHAR2(31),
    birthdate DATE,

       var NUMBER;
          var := TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate) /12);

CREATE TABLE department (dno NUMBER, manager EMPLOYEE_T);


The following examples update and select from the object columns and method defined above.

UPDATE department d
  SET d.manager.empid = 100;
SELECT, d.manager.age()
  FROM department d;

DECODE Expressions

A DECODE expression uses the special DECODE syntax:


To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle compares them using nonpadded comparison semantics.

The search, result, and default values can be derived from expressions. Oracle evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

In a DECODE expression, Oracle considers two nulls to be equivalent. If expr is null, Oracle returns the result of the first search that is also null.

The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

See Also:



This expression decodes the value deptno. If deptno is 10, the expression evaluates to 'ACCOUNTING'; if deptno is 20, it evaluates to 'RESEARCH'; etc. If deptno is not 10, 20, 30, or 40, the expression returns 'NONE'.

DECODE (deptno,10, 'ACCOUNTING', 
               20, 'RESEARCH', 
               30, 'SALES', 
               40, 'OPERATION',                     

CASE Expressions

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. The syntax is:


Oracle searches for the first WHEN ... THEN pair for which condition is true.

At least one occurrence of expr1 or expr2 must be non-null.


The maximum number of arguments in a CASE expression is 255, and each WHEN ... THEN pair counts as two arguments. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is expr1 can itself be a CASE expression. 


The following statement finds the average salary of all employees in the EMP table. If an employee's salary is less than $2000, the CASE expression uses $2000 instead.

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END) from emp e;

Expression List

An expression list is a series of expressions separated by a comma. The entire series is enclosed in parentheses.


An expression list can contain up to 1000 expressions. Some valid expression lists are:

(10, 20, 40) 
(LENGTH('MOOSE') * 57, -SQRT(144) + 72, 69) 


A condition specifies a combination of one or more expressions and logical operators that evaluates to either TRUE, FALSE, or unknown. You must use this syntax whenever condition appears in SQL statements.

You can use a condition in the WHERE clause of these statements:

You can use a condition in any of these clauses of the SELECT statement:

A condition could be said to be of the "logical" datatype, although Oracle does not formally support such a datatype.

The following simple condition always evaluates to TRUE:

1 = 1 

The following more complex condition adds the sal value to the comm value (substituting the value 0 for null) and determines whether the sum is greater than the number constant 2500:

NVL(sal, 0) + NVL(comm, 0) > 2500 

Logical operators can combine multiple conditions into a single condition. For example, you can use the AND operator to combine two conditions:

(1 = 1) AND (5 < 7) 

Here are some valid conditions:

name = 'SMITH' 
emp.deptno = dept.deptno 
hiredate > '01-JAN-88' 
sal BETWEEN 500 AND 1000 
comm IS NULL AND sal = 2000 

Conditions can have several forms, as shown in the following syntax.


The sections that follow describe the various forms of conditions.

See Also: The description of each statement in Chapter 7 through Chapter 11 for the restrictions on the conditions in that statement 

Simple Comparison Conditions

A simple comparison condition specifies a comparison with expressions or subquery results.


See Also:

"Comparison Operators" for information on comparison operators 

Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery.


See Also: SELECT and subquery 

Membership Conditions

A membership condition tests for membership in a list or subquery.


Range Conditions

A range condition tests for inclusion in a range.


NULL Conditions

A NULL condition tests for nulls.


EXISTS Conditions

An EXISTS condition tests for existence of rows in a subquery.


LIKE Conditions

A LIKE condition specifies a test involving pattern matching.


Compound Conditions

A compound condition specifies a combination of other conditions.


Queries and Subqueries

A query is an operation that retrieves data from one or more tables or views. In this reference, a top-level SELECT statement is called a query, and a query nested within another SQL statement is called a subquery.

This section describes some types of queries and subqueries and how to use them.

See Also: SELECT and subquery for the full syntax of all the clauses and the semantics of the keywords and parameters 

Creating Simple Queries

The list of expressions that appears after the SELECT keyword and before the FROM clause is called the select list. Each expression expr becomes the name of one column in the set of returned rows, and each table.* becomes a set of columns, one for each column in the table in the order they were defined when the table was created. The datatype and length of each expression is determined by the elements of the expression.

If two or more tables have some column names in common, you must qualify column names with names of tables. Otherwise, fully qualified column names are optional. However, it is always a good idea to qualify table and column references explicitly. Oracle often does less work with fully qualified table and column names.

You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query.

You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement.

See Also: "Hints" and Oracle8i Performance Guide and Reference for more information on hints 

Hierarchical Queries

If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause:

Oracle uses the information from the hierarchical query clause clause to form the hierarchy using the following steps:

  1. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.

  2. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

  3. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

  4. If the query contains a WHERE clause, Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

  5. Oracle returns the rows in the order shown in Figure 5-1. In the diagram children appear below their parents.

Figure 5-1 Hierarchical Queries

To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery.

If the CONNECT BY condition results in a loop in the hierarchy, Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.

See Also:

"LEVEL" for a discussion of how the LEVEL pseudocolumn operates in a hierarchical query 

Sorting Query Results

You can use the ORDER BY clause to order the rows selected by a query. Sorting by position is useful in the following cases:

The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. For information on these parameters, see Oracle8i National Language Support Guide. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause.


A join is a query that combines rows from two or more tables, views, or materialized views ("snapshots"). Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables.

In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.


An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE.

See Also: "Equijoin Examples" 

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

See Also: "Self Join Example" 

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, the optimizer may choose a join order that avoids producing an intermediate Cartesian product.

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.

Outer join queries are subject to the following rules and restrictions:

If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle will return only the results of a simple join.

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C.

See Also: SELECT and subquery for the syntax for an outer join 

Using Subqueries

A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.

A subquery can contain another subquery. Oracle imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause.

If tables in a subquery have the same name as tables in the containing statement, you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.

Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.

A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.

See Also: "Correlated Subquery Examples" 

A scalar subquery returns exactly one column value from one row. You can use a scalar subquery in place of an expression to specify a value in the VALUES clause of an INSERT statement or to provide an argument of a type constructor expression or user-defined function expression.

Use subqueries for the following purposes:

Unnesting of Nested Subqueries

Subqueries are "nested" when they appear in the WHERE clause of the parent statement. When Oracle evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include subqueries that contain a CONNECT BY or START WITH clause, a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the subquery's immediate outer query block.

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:

Selecting from the DUAL Table

DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table.

See Also: "SQL Functions" for many examples of selecting a constant value from DUAL 

Distributed Queries

Oracle's distributed database management system architecture allows you to access data in remote databases using Net8 and an Oracle server. You can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table, view, or materialized view.

See Also: "Referring to Objects in Remote Databases" for more information on referring to database links 

Restrictions on Distributed Queries

Distributed queries are currently subject to the restriction that all tables locked by a FOR UPDATE clause and all tables with LONG columns selected by the query must be located on the same database. For example, the following statement will raise an error:

SELECT emp_ny.* 
    FROM emp_ny@ny, dept 
    WHERE emp_ny.deptno = dept.deptno 
    AND dept.dname = 'ACCOUNTING' 
    FOR UPDATE OF emp_ny.sal; 

The following statement fails because it selects long_column, a LONG value, from the emp_review table on the ny database and locks the emp table on the local database:

SELECT emp.empno, review.long_column, emp.sal 
    FROM emp, emp_review@ny review 
    WHERE emp.empno = emp_review.empno 
    FOR UPDATE OF emp.sal;

In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.