Oracle SQL: The Essential Reference By David Kreines October 2000 1-56592-697-8, Order Number: 6978 418 pages, $39.95

Chapter 1
Elements of SQL

SQL was developed to provide easy access to relational databases, so it is able to perform the following kinds of actions:

• Querying data from a database
• Inserting data into a database
• Deleting data from a database
• Creating and manipulating database objects
• Controlling access to the database

Strictly speaking, SQL is not a language at all, but rather a means of conveying instructions to the Oracle database. It differs from traditional programming languages in several important ways:

• SQL provides automatic navigation to data.
• SQL operates on sets of data, rather than on individual data elements.
• SQL is declarative, not procedural, and does not provide procedural control.
• SQL programming is done at the logical level; there is little need to deal with the details of implementation.

Simply put, when programming in SQL you tell Oracle what you want to do, but not how it should be done. However, this approach can be both a blessing and a curse. Consider the following SQL statement:

SELECT ename, deptno, sal, comm
FROM scott.emp
WHERE hiredate > '01-JAN-00';

This simple SQL statement tells the database to display a list consisting of name (ename), department number (deptno), salary (sal), and commission (comm) for each employee hired after January 1, 2000. Such a program might have taken hundreds of lines of code in an "old style" procedural language, but takes only three lines in SQL. At the same time, however, Oracle is not always too smart about how it retrieves data. Although Oracle's internal "query optimizer" has steadily improved, there are still many ways to improve SQL performance, and Chapter 8, SQL Statement Tuning, is dedicated to this subject.

TIP:  

The lack of procedural control was viewed by some as a disadvantage of SQL, so Oracle Corporation developed PL/SQL (Procedural Language/SQL), which is discussed in Chapter 7, PL/SQL.

SQL statements, also known as SQL commands, are combinations of the following:

Keywords

Reserved words with specific operational meaning to Oracle.

Variables

Data elements, which may be dynamically replaced with text or numeric values. In SQL these are the names of objects such as columns, tables, or views.

Literals

Constant data, including text strings and numbers.

Operators

Symbols or words that operate on one or more variables or literals.

Lexical Conventions

SQL statements are composed of commands, variables, and operators, which are described in detail in this and subsequent chapters. A SQL statement is constructed from:

• Characters A through Z (or the equivalent from your database character set)
• Numbers 0 through 9
• Spaces
• The following special characters: + - * = ? ! @ ( ) _ . , < > | $ #

TIP:  

Oracle strongly discourages the use of # and $.

Other characters, such as &, are also used in SQL statements, but may be intercepted and interpreted by SQL*Plus if you are using that tool. See Chapter 6, SQL*Plus, for more information.

A SQL statement can contain one or more of the following items anywhere a single space can occur:

• Tab
• Carriage return
• Multiple spaces
• Comments

TIP: 

Certain components of SQL statements (such as variable names and strings) may contain other characters, as long as they are enclosed in double quotes.

The following two SELECT statements, for example, are evaluated in exactly the same way by Oracle and both return the same result set:

SELECT ename,empno,sal FROM scott.emp WHERE sal>500;
 
SELECT ename, 
       empno, sal
FROM   scott.emp
WHERE  sal > 500;

SQL is generally not case-sensitive, so case is not significant except in literals, which are enclosed in quotes.

WARNING: 

Be aware that a variable name enclosed in double quotes will be case-sensitive. This fact is especially important if you access your Oracle database using Microsoft Access, which creates objects using lowercase names.

Naming in SQL

Most naming requirements in SQL are actually requirements of the Oracle database; names that are acceptable for schema objects (defined in the next section) in the Oracle database are acceptable in SQL, and vice versa. The following rules apply to the names of schema objects in Oracle:

• They may comprise 1 to 30 alphanumeric characters.
• They must begin with a letter.
• They may include an underscore ( _ ).
• They may include a dollar ($) or pound sign (#), although Oracle discourages the use of these characters.
• They may not be a reserved word.
• They may not be the name of a SQL command.

TIP:  

A name may begin with and/or contain any characters if it is enclosed in double quotes.

Schema Objects

A schema object is a logical collection of data or other objects that are owned by a user and stored in the database. The following types of objects are considered schema objects:

• Clusters
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Index-organized tables
• Indexes
• Index types
• Materialized views/snapshots
• Materialized view logs/snapshot logs
• Nested table types
• Object types
• Operators
• Packages
• Sequences
• Stored functions
• Stored procedures
• Synonyms
• Tables
• Varying array types
• Views
• Database links

General Syntax

Generally, you reference schema objects in SQL statements using the following syntax:

schema.object_name.object_ part@dblink

These syntax elements have the following meaning:

schema

The name of the schema that owns the object. In Oracle, a schema corresponds one-to-one with a username; if the schema is omitted from a reference to a schema object, then the username that is currently logged in is used by default.

object_name

The name of the object being referenced, such as a table.

object_part

The name of a part of an object, for those schema objects that have a part, such as a column of a table.

dblink

The name of a database link referencing a remote database.

The syntax shown here, with a schema name followed by a period, then followed by an object name (for example, scott.emp) is commonly referred to as dot notation. Generally, if the schema. portion of a name is omitted; the schema of the user currently connected to the database will be used by default.

For example, the following SQL statement queries data from a table, which is a schema object named emp in the schema scott. This schema is located in a remote database and is referenced by the database link test:

SELECT ename, empno, sal
FROM scott.emp@test 
WHERE sal > 500;

Partition Syntax

When referencing a specific partition or subpartition of a partitioned table, use the following syntax:

schema.table_name {PARTITION (partition) |

          SUBPARTITION (subpartition)

                 )

These syntax elements have the following meaning:

schema

The name of the schema that owns the object. In Oracle, a schema corresponds one-to-one with a username, and if the schema is omitted from a reference to a schema object, then the username that is currently logged in is used by default.

table_name

The name of the table being referenced.

partition

The name of a partition of the table.

subpartition

The name of a subpartition of the table.

This construct is known as a partition-extended table name. A partition-extended table name may not have a database link associated with it. Therefore, if you want to access this object on a remote database, you must create a view that can be accessed using the general schema object syntax described previously.

Datatypes

Oracle stores data in the database in any of three basic families of datatypes: character, numeric, and date. Both the character and numeric families have several distinct datatypes associated with them, which are described in the following sections.

Character Data

Character data is any string of one or more bytes of data that will not be the direct target of an arithmetic operation. Oracle (and SQL) supports several types of character data, which are listed below with their usage syntax:

CHAR [(length)]

Fixed-length character data, with a maximum length of 2000 bytes. length specifies the maximum length of the character string to be stored.

VARCHAR2 [(length)]

Variable-length character data, with a maximum length of 4000 bytes. length specifies the maximum length of the character string to be stored.

NCHAR [(length)]

Fixed-length character data consisting of characters from a National Character Language (NLS) supported character set. Since a character may require more than one byte, the maximum length is 2000 bytes (which may allow fewer than 2000 characters). length specifies the maximum length of the character string to be stored.

NVARCHAR2 [(length)]

Variable-length character data consisting of characters from a National Language Support (NLS) character set. Since a character may require more than one byte, the maximum length is 4000 bytes (which may allow fewer than 4000 characters).length specifies the maximum length of the character string to be stored.

LONG

Variable-length character data with a maximum length of 2 gigabytes.

RAW

Raw binary data with a maximum length of 2000 bytes. RAW data will not be converted by Oracle when moving between systems with different character sets.

LONG RAW

Raw binary data with a maximum length of 2 gigabytes. LONG RAW data will not be converted by Oracle when moving between systems with different character sets.

The following character datatypes are also recognized for compatibility with ANSI SQL:

CHARACTER
CHARACTER VARYING
CHAR VARYING 
NATIONAL CHARACTER 
NATIONAL CHAR 
NATIONAL CHARACTER VARYING 
NATIONAL CHAR VARYING 
NCHAR VARYING 

Large Objects

Oracle provides several datatypes that support storage of large amounts of data in a single column. These datatypes are often used to store images, sound, and other large objects:

BLOB

Large, raw binary data with a maximum length of 4 gigabytes. BLOB data will not be converted by Oracle when moving between systems with different character sets. When a BLOB column is referenced, a LOB locator is returned.

CLOB

Large character data with a maximum length of 4 gigabytes.

NCLOB

Large character data consisting of characters from a National Language Support (NLS) character set with a maximum length of 4 gigabytes. NCLOB data will not be converted by Oracle when moving between systems with different character sets.

BFILE

Provides access to a binary file stored in an operating system file external to the Oracle database. The file can have a maximum size of 4 gigabytes.

Numeric Data

Numeric data is data that can participate in an arithmetic operation directly without data conversion. Oracle has only a single type of numeric data: the NUMBER type.

TIP: 

NUMBER data can hold values between 10-130 and 9.99999...x 10125, but the number will be accurate only to 38 positions.

A NUMBER data element can be expressed as:

NUMBER [(precision[,scale])]

precision

The number of digits in the number, which can range from 1 to 38.

scale

The number of digits to the right of the decimal point, which can range from -84 to 127.

If scale is omitted, the number is treated as an integer number, and no decimal portion is stored. If both scale and precision are omitted, the number is treated as a floating-point number.

WARNING:  

It is a good idea to always specify a precision for a number datatype. If you do not, some ODBC drivers will assume a precision of 0, as opposed to the Oracle default of 38. In such cases, the ODBC-compliant client tool will not be able to insert or update rows where the column value is not 0.

Although all numeric data is stored by Oracle as a NUMBER, the following datatypes are also recognized for compatibility with ANSI SQL:

FLOAT
NUMERIC
DECIMAL
NUMBER
INTEGER 
INT 
SMALLINT 
DOUBLE PRECISION
REAL

Dates

The DATE datatype is used by Oracle to store date and time information. Oracle DATE data is stored in a proprietary format that contains the following information:

• Century
• Year
• Month
• Day
• Hour
• Minute
• Second

To store data in a DATE datatype, the date and/or time information must be converted into Oracle's internal format. If the character representation of a date matches the default format (as specified by the NLS_DATE_FORMAT parameter in the initialization file, INIT.ORA), then Oracle can perform this conversion automatically. For example, if the default NLS_DATE_FORMAT of DD-MON-YY is in effect, then the following SQL statement will insert the date July 4, 2076 into a table column called T_DATE:

INSERT INTO sample_table (t_date)
VALUES ('07-JUL-76');

If the date and/or time information is available in another format, you can use one of Oracle's built-in TO_DATE functions to perform the conversion. For example, if the same date is presented as 07/04/76, then the SQL statement shown here, which uses Oracle's TO_DATE function, inserts the following row of data:

INSERT INTO sample_table (t_date)
VALUES (TO_DATE('07-JUL-76','MM/DD/YY'));

See Chapter 5, SQL Functions, for more information on TO_DATE and other conversion functions.

NULL

One sometimes confusing aspect of SQL is the concept of a NULL. NULL is the absence of data; it is neither character nor numeric. Both character and numeric data elements can be set to NULL, which indicates that the element contains no value whatsoever.

NULL is not the same as zero. In fact, any arithmetic operation involving a data element containing NULL will evaluate to NULL. For example, if the current value of a is NULL, then the following expression will evaluate to NULL:

(a+10)*20

TIP:  

This feature of Oracle can make life difficult for a programmer if arithmetic will be performed involving a column that could contain a NULL. Luckily, Oracle provides the NVL function to take care of this problem. See Chapter 5 for more information.

Similarly, when using comparisons, a NULL will never match a condition, even though it may logically seem that it should. If col1 contains the value NULL, then the following clause:

WHERE col1 <> 1

will not resolve to TRUE.

You can test for the presence or absence of NULL in a data element. The condition IS NULL evaluates to TRUE if the expression evaluates to NULL, and to FALSE if it does not.

Locators

Oracle provides several datatypes whose primary purpose is to provide efficient reference to objects stored both within and outside the database.

ROWID

ROWID returns a string that represents the physical location of a row of data and contains all the information Oracle needs to locate a row of data, including:

• The data file containing the row
• The block of the file containing the row
• The position of the row in the data block
• The object number of the object (Oracle8 only)

Except in the case of tables stored in clusters, a ROWID will uniquely identify any single row of data.

ROWID is always returned as an 18-character string and may be used in a Data Manipulation Language (DML) statement just like any other Oracle column.

TIP: 

While ROWID can be stored in a table column, you should never rely on a row retaining its ROWID value. Since the ROWID represents a physical location, the value can change if the row is stored differently. For example, an export followed by an import will almost certainly invalidate any stored ROWID.

UROWID

Because some types of Oracle objects may not have physical locations that are fixed or generated by Oracle (such as objects accessed via a Transparent Gateway), Oracle has developed the Universal ROWID (UROWID) datatype, which can contain the physical ROWID when it is available; otherwise, it contains the logical ROWID. Oracle strongly recommends that you use the UROWID datatype in place of ROWID so either type of location information can be accommodated.

Pseudo-Columns

While not actual datatypes, Oracle supports several special-purpose data elements. These elements are not actually contained in a table, but are available for use in SQL statements as though they were part of the table.

ROWNUM

For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the emp table, the following SQL statement makes use of the ROWNUM pseudo-column:

SELECT *
FROM emp
WHERE ROWNUM < 11;

WARNING:  

ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation.

CURRVAL

When using Oracle SEQUENCE values, (see "CREATE/ALTER/DROP SEQUENCE" in Chapter 2, Data Definition Statements), the pseudo-column CURRVAL returns the current value of the sequence. To reference CURRVAL, it must be associated with a sequence:

[schema.]sequence_name.CURRVAL

schema

The owner of the sequence. If schema is omitted, Oracle assumes the username under which you are currently connected to the database.

sequence_name

The name of an Oracle sequence.

CURRVAL

The current value of the sequence.

NEXTVAL

When using Oracle SEQUENCE values, the pseudo-column NEXTVAL returns the next value of the sequence and causes the sequence to increment by one. You can only reference NEXTVAL if it is associated with a sequence:

[schema.]sequence_name.NEXTVAL

schema

The owner of the sequence. If omitted, the userid under which you are currently logged in is used.

sequence_name

The name of an Oracle sequence.

NEXTVAL

The next value of the sequence.

TIP: 

Oracle will only increment the sequence once in a given SQL statement, so if a statement contains multiple references to NEXTVAL, the second and subsequent reference will return the same value as CURRVAL.

LEVEL

For each row returned by a hierarchical query (using the CONNECT BY clause), LEVEL returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any nonroot node, a parent node is any node that has children, and a leaf node is any node without children.

USER

This pseudo-column will always contain the Oracle username under which you are connected to the database.

SYSDATE

This pseudo-column will contain the current date and time. This column is a standard Oracle DATE datatype.

The date and time contained in SYSDATE comes from the server that processes the query, not the client from which the query is run. So if you connect to a server in Tokyo from a client workstation in London, the date and time will be that of the server in Tokyo (and the date will probably be a day ahead!).

WARNING: 

If you return a SYSDATE column via a database link (for example, SELECT SYSDATE FROM dual@london), the date and time will be returned from the server you are connected to, not the remote server referenced by the database link.

Data Conversion

There are multiple ways to represent data in a database. For example, a salary, which is normally considered a numeric value such as 25,000 can be represented easily as a character string such as "25000". Likewise, an employee ID can be represented as a number (500) or a string ("500"). If you attempt to perform an arithmetic operation on a character value in most computer languages, an error will occur. Not so with SQL. Oracle automatically performs a data conversion when it is necessary (and possible) to complete a requested operation. In the following SQL statement, assume that sal is defined in the database as CHAR(6)--a character string with a fixed length of six bytes:

SELECT ename, SAL * 1.1
FROM scott.emp;

The character string (sal) is multiplied by a numeric constant (1.1). To perform this operation, Oracle first converts the string into a number, and then performs the multiplication. This type of automatic conversion is an implicit data conversion.

WARNING:  

While convenient, implicit data conversion also has a cost in CPU utilization, so be careful when deciding how to store data elements in the database.

Implicit data conversion can have an unexpected negative impact on performance, since it can dramatically affect the way the Oracle query optimizer generates an execution plan. For example, use of an index may be suppressed due to implicit data conversion, resulting in a full table scan.

SQL also provides several functions that perform explicit data conversion:

TO_CHAR

Performs numeric-to-character and date-to-character conversions

TO_NUMBER

Performs character-to-numeric conversion

TO_DATE

Performs character-to-date conversion

See Chapter 5 for more information.

Relational Operators

An operator manipulates data elements and returns a result. The data elements that are operated upon are called operands or arguments, and a special character or keyword represents the actual operator. Oracle SQL supports several types of operators, listed in this section.

Arithmetic Operators

Arithmetic operators perform arithmetical calculations on a pair of data elements and/or constants. Table 1-1 lists the arithmetic operators available in SQL.

Table 1-1: SQL's Arithmetic Operators

Operator	Description	Example
+	Addition	sal + comm
-	Subtraction	sal - comm
*	Multiplication	sal * 1.1
/	Division	sal / 12
-	Negation	-sal
+	Identity	+sal

Concatenation Operator

The concatenation operator (||) combines two character strings. Consider the following SQL statement:

SELECT fname || ' ' || lname
FROM employee_master;

This statement returns (for each row) a single string consisting of the first name, a space, and the last name.

WARNING:  

Although most Oracle platforms use solid vertical bars (||) as the concatenation operator, some platforms, most notably IBM platforms using the EBCDIC character set, use the broken vertical bars ( ). When converting between ASCII and EBCDIC character sets, the conversion of these characters may not be correct.

If one of two concatenated strings is NULL, the result is a non-NULL string. The NULL string is treated as an empty string. If both strings being concatenated are NULL, then the resulting string is NULL. If either of the operands is a VARCHAR2 datatype, the resulting string is a VARCHAR2 datatype as well.

WARNING:  

A concatenated string may not be longer than 2000 characters if the operands are CHAR datatypes, or 4000 characters otherwise. Other character types, like LONG and CLOB, cannot be concatenated.

Comparison Operators

Comparison operators are used to compare two data elements (or a data element and a constant) and return a result that is TRUE, FALSE, or NULL, depending on how the values in the two elements relate to each other. Table 1-2 lists the comparison operators available in SQL.

Table 1-2: SQL's Comparison Operators

Operator	Use	Description	Example
=	a = b	Tests for equality of two operands.	SELECT * FROM emp WHERE sal =500
!=	a != b	Tests for inequality of two operands.	SELECT * FROM emp WHERE sal !=500
^=	a ^= b	Tests for inequality of two operands.	SELECT * FROM emp WHERE sal ^=500
<>	a <> b	Tests for inequality of two operands.	SELECT * FROM emp WHERE sal <>500
<	a < b	Tests that operand a is less than operand b.	SELECT * FROM emp WHERE sal <500
!<	a !< b	Tests that operand a is not less than operand b. This is the same as >=.	SELECT * FROM emp WHERE sal !<500
>	a > b	Tests that operand a is greater than operand b.	SELECT * FROM emp WHERE sal >500
!>	a !> b	Tests that operand a is not greater than operand b. This is the same as <=.	SELECT * FROM emp WHERE sal !>500
<=	a <= b	Tests that operand a is less than or equal to operand b. This is the same as !>.	SELECT * FROM emp WHERE sal <=500
>=	a >= b	Tests that operand a is greater than or equal to operand b. This is the same as !<.	SELECT * FROM emp WHERE sal >=500
IN	a IN (b,c...)	Tests that operand a matches at least one element of the list provided (operand b, operand c, etc.).	SELECT * FROM emp WHERE sal IN (500,600,700)
NOT IN	a NOT IN (b,c...)	Tests that operand a does not match any element of the list provided (operand b, operand c, etc.).	SELECT * FROM emp WHERE sal NOT IN (500,600,700)
ANY	a = ANY (b,c...) a < ANY (b,c...) a > ANY (b,c...), etc.	Tests that the relationship specified (e.g., =, <>, <, >, etc.) is true for at least one element of the list provided (operand b, operand c, etc.). When testing for equality, this is equivalent to IN.	SELECT * FROM emp WHERE sal = ANY (500,600,700)
SOME	a = SOME (b,c...) a < SOME (b,c...) a > SOME (b,c...), etc.	Tests that the relationship specified (e.g., =, <>, <, >, etc.) is true for at least one element of the list provided (operand b, operand c, etc.). When testing for equality, this is equivalent to IN.	SELECT * FROM emp WHERE sal = SOME (500,600,700)
ALL	a = ALL (b,c...) a < ALL (b,c...) a < ALL (b,c...), etc.	Tests that the relationship specified (e.g., =, <>, <, >, etc.) is true for every element of the list provided (operand b, operand c, etc.).	SELECT * FROM emp WHERE sal > ALL (500,600,700)
BETWEEN	a BETWEEN b and c	Tests that operand a is greater than or equal to operand b and less than or equal to operand c.	SELECT * FROM emp WHERE sal BETWEEN 400 AND 600
NOT BETWEEN	a NOT BETWEEN b and c	Tests that operand a is less than operand b or greater than operand c.	SELECT * FROM emp WHERE sal NOT BETWEEN 400 and 600
EXISTS	EXISTS (query)	Tests that the query returns at least one row.	SELECT * FROM emp e WHERE EXISTS (SELECT deptno FROM dept d WHERE deptno= e.deptno)
NOT EXISTS	NOT EXISTS (query)	Tests that the query does not return a row.	SELECT * FROM emp e WHERE NOT EXISTS (SELECT deptno FROM dept d WHERE deptno= e.deptno)
LIKE	a LIKE b	Tests that operand a matches pattern operand b. The pattern may contain _, which matches a single character in that position, or %, which matches all characters.	SELECT * FROM emp WHERE ename LIKE `SMI%'
NOT LIKE	a NOT LIKE b	Tests that operand a does not match pattern operand b. The pattern may contain _, which matches a single character in that position, or %, which matches all characters.	SELECT * FROM emp WHERE ename NOT LIKE `SMI%'
IS NULL	a IS NULL	Tests that operand a is NULL.	SELECT * FROM emp WHERE comm IS NULL
IS NOT NULL	a IS NOT NULL	Tests that operand a is not NULL.	SELECT * FROM emp WHERE comm IS NOT NULL

Logical Operators

SQL provides logical operators that are similar to those available in most other programming languages. The logical operators AND and OR combine the results of two Boolean values to produce a single result based on them, while the logical operator NOT inverts a result. The Boolean values may be any expression that can be evaluated to TRUE or FALSE. Usually the values come from comparison expressions. Table 1-3 presents the logical operators available in SQL, along with the possible results from each.

Table 1-3: SQL's Logical Operators

Operator	Operand 1	Operand 2	Result
AND	TRUE	TRUE	TRUE
	FALSE	FALSE	FALSE
	TRUE	FALSE	FALSE
	FALSE	TRUE	FALSE
	TRUE	NULL	NULL
	FALSE	NULL	FALSE
	NULL	TRUE	NULL
	NULL	FALSE	FALSE
	NULL	NULL	NULL
OR	TRUE	TRUE	TRUE
	FALSE	FALSE	FALSE
	TRUE	FALSE	TRUE
	FALSE	TRUE	TRUE
	TRUE	NULL	TRUE
	FALSE	NULL	NULL
	NULL	TRUE	TRUE
	NULL	FALSE	NULL
	NULL	NULL	NULL
NOT	TRUE		FALSE
	FALSE		TRUE
	NULL		NULL

Set Operators

Unlike other relational operators that operate on one or more individual data elements, set operators work on the entire set of data returned by two queries. Table 1-4 describes the set operators available in SQL.

Table 1-4: SQL's Set Operators

Operator	Description	Example
UNION	Combines all rows returned by both queries and eliminates duplicate rows.	SELECT * FROM emp WHERE deptno=10 UNION SELECT * FROM emp WHERE sal > 500
UNION ALL	Combines all rows returned by both queries and includes duplicate rows.	SELECT * FROM emp WHERE deptno=10 UNION ALL SELECT * FROM emp WHERE sal > 500
MINUS	Takes the rows returned by the first query, removes rows that are also returned by the second query, and returns the rows that remain.	SELECT * FROM emp MINUS SELECT * FROM emp WHERE sal > 500
INTERSECT	Returns only the rows returned by both queries.	SELECT * FROM emp WHERE deptno = 10 INTERSECT SELECT * FROM emp WHERE SAL>500

Structure of a SQL Statement

A SQL statement can be broken into three major components:

• The SQL operation
• The target
• The condition

Only the first two components are required; the condition is optional or may not apply, depending on the SQL operation being performed.

The SQL Operation

There are four basic operations performed by a SQL DML statement. Each of these is discussed in this section. Each operation is also the name of a SQL statement, and the detailed syntax for each statement can be found in Chapter 3, Data Manipulation and Control Statements.

SELECT

The SELECT statement is probably the most common and widely used of all SQL statements. The purpose of a SELECT statement is to retrieve data from the database. The statement may return data elements from one or more database tables or views, from expressions involving data elements from at least one database table or view and/or constant, or from constants. A SELECT statement always has a target component, and often has a condition component. The target of a SELECT statement is the set of tables and views listed in the FROM clause (the tables and views from which data is retrieved). The condition is the expression in the WHERE clause, and possibly in the HAVING clause as well, that restricts the rows that will be returned. If no condition is specified, all rows of the target table(s) and/or view(s) are returned.

INSERT

The INSERT statement creates new rows of data in a target database table or view. The statement provides a list of columns that will receive the data provided (all columns of the table or view are implied if no list is provided) and a corresponding list containing the data elements to be placed in each column. The condition component does not apply to an INSERT.

UPDATE

The UPDATE statement modifies data already in a database table or view. The UPDATE statement always has an associated target, and usually has a condition as well. If no condition is specified, all rows of the target table are updated.

DELETE

The DELETE statement removes rows from a database table or view. This statement will always have an associated target, and usually has a condition as well. If no condition is specified, all rows of the target table or view are removed.

The Target

All SQL DML statements operate on one or more database tables or views. The purpose of the target component is to identify those tables or views. This component takes a different form depending on the statement with which it's being used. For example, the SELECT and DELETE statements have similar target structures:

SELECT *
FROM emp          --This is the target component
WHERE depno = 10
 
DELETE
FROM emp          --This is the target component 
WHERE deptno = 10

The INSERT and UPDATE statements, however, use the target differently:

INSERT INTO emp   --This is the target component
   (empno, ename, sal, hiredate)  
VALUES ('1234','Dave Kreines',500,'06-01-00')
 
UPDATE emp        --This is the target component
SET sal = 600
WHERE empno = '1234'

Joins

When two or more tables or views are referenced as the target of a SELECT statement, this is called a join. One of the fundamental concepts of a relational database is the ability to combine two or more tables into a single result set by specifying how the tables are related (thus the term relational ). Two or more tables or views are typically related to each other by one or more columns that share common data. Such a column is called a key column. An example of a key column might be a department number. Figure 1-1 illustrates such a relationship.

Figure 1-1. Two tables related through a common key

The target component specifies the tables or views to be included in a join, and the condition component tells Oracle how to relate the tables or views to each other.

The following example joins the emp and dept tables:

SELECT ename, location
FROM emp, dept
WHERE emp.deptno = dept.deptno

This statement instructs Oracle to return the name and location for each employee from the database by first forming all possible combinations of data rows from the two tables, and then returning all rows where the two department numbers match. Note that the number of rows in each table can be different; it is the data value that is used for the match. One row from the dept table can, and probably will, match multiple rows of the emp table.

TIP:  

The idea of forming all possible combinations of all rows by joining two tables is conceptual. Oracle almost always finds a more optimal way to generate the join results.

If no condition is specified for a join, all possible combinations of rows from the two tables are returned as the result. In other words, every row of the first table is matched with every row of the second table (assuming that two tables are joined). This result is known as a Cartesian product, and it is usually something you want to avoid. The number of rows returned is equivalent to the number of rows in the first table multiplied by the number of rows in the second. If your tables are large to begin with, the number of rows in the Cartesian product can become extremely large. Performance will suffer greatly by having to generate those rows, and they are not likely be of much use anyway. Cartesian products usually represent a mistake in writing a query.

WARNING:  

The existence of two or more tables or views in the FROM clause, with no corresponding set of join conditions in the WHERE clause, always results in a Cartesian product.

Outer join

In the standard join, rows are only returned when there are corresponding rows in each of the joined tables or views. An outer join allows data to be returned even if no matching row exists in one of the tables. The outer join is specified by adding (+) to the end of the column names for the table that you want to make optional. In other words, (+) means "add a phantom row to this table that contains NULL values for all columns if a matching row does not exist." Here is an example of an outer join that displays a NULL location if there is no matching department number in the dept table:

SELECT ename, location
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)

Normally, a query joining the emp and dept tables would return rows for only the employees who had been assigned to a valid department. By adding (+) to the end of the dept.deptno column name, we make the join into an outer join. Rows are now returned for all employees, whether or not they have a valid department assignment.

The Condition

The condition component, which is specified using the WHERE clause, identifies the specific rows to be operated on by a SELECT, UPDATE, or DELETE statement. While a WHERE clause may be very complex, it ultimately evaluates to either TRUE or FALSE for each row of data, and that action controls whether or not the operation takes place for each row. Consider the following query:

SELECT * 
FROM emp
WHERE sal > 500

Oracle will look at each row of data in the emp table and evaluate the condition sal > 500. Rows for which this expression evaluates to TRUE will be returned, while those for which this condition evaluates to FALSE or unknown (those with values of sal that are less than or equal to 500 or that contain NULL) will not be returned.

Another important use for the WHERE clause is to identify the columns that relate one table to another to perform a join. Here is a query that includes a simple join:

SELECT ename, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno

Both the emp and dept tables have a column called deptno, which contains the department number. The columns do not need to have the same name, but they do have to contain data with the same meaning. In this example, each emp row contains a department number in a column named deptno. The department number also exists in the deptno column of the dept table. Since we know that a department number in the emp table has the same meaning as one in the dept table, these columns can be used to specify a join condition.

WARNING:  

In the example, the column name (deptno) was prefixed by the name of the table (emp or dept). This prefix occurs because both tables in the join have identically named columns. As a result, you need to qualify the column names with the table names so Oracle knows which column you are referring to. Oracle would return an error if you failed to do this because the column names would then be ambiguous. What is intended may be obvious to you, but not to Oracle!

SQL Statements

There are a large number of SQL statements supported for Oracle and described in subsequent chapters of this book. As an aid to identifying the particular SQL statement you might need and helping you find it in the book, Table 1-5 presents a list of SQL commands in alphabetical order, along with a short description, and the chapter number and heading where you can find the full syntax and description of that statement.

Table 1-5: SQL Statements Covered in This Book

SQL Statement	Description	Found in Chapter	Under This Heading
ALTER CLUSTER	Redefines future storage allocations or allocates an extent for a cluster	2	"CREATE/ALTER/DROP CLUSTER"
ALTER DATABASE	Changes one or more characteristics of an existing database	2	"CREATE/ALTER DATABASE"
ALTER FUNCTION	Recompiles a stored PL/SQL function	7	"ALTER FUNCTION"
ALTER INDEX	Changes the characteristics of an index	2	"CREATE/ALTER/DROP INDEX"
ALTER MATERIALIZED VIEW	Changes the storage characteristics or automatic refresh characteristics of a materialized view or snapshot	2	"CREATE/ALTER/DROP MATERIALIZED VIEW"
ALTER MATERIALIZED VIEW LOG	Changes the storage characteristics of a materialized view log	2	"CREATE/ALTER/DROP MATERIALIZED VIEW LOG"
ALTER PACKAGE	Recompiles a PL/SQL package	7	"ALTER PACKAGE"
ALTER PROCEDURE	Recompiles a PL/SQL stored procedure	7	"ALTER PROCEDURE"
ALTER PROFILE	Adds, changes, or removes a resource limit from an existing profile	2	"CREATE/ALTER/DROP PROFILE"
ALTER RESOURCE COST	Modifies the formula calculating the total resource cost used in a session	2	"ALTER RESOURCE COST"
ALTER ROLE	Changes the authorization level required to enable a role	2	"CREATE/ALTER/DROP ROLE"
ALTER ROLLBACK SEGMENT	Changes the online status of a rollback segment or modifies its storage characteristics	2	"CREATE/ALTER/DROP ROLLBACK SEGMENT"
ALTER SEQUENCE	Changes the characteristics of an Oracle sequence	2	"CREATE/ALTER/DROP SEQUENCE"
ALTER SESSION	Changes the functional characteristics of the current database session	2	"ALTER SESSION"
ALTER SNAPSHOT	Changes the storage characteristics or automatic refresh characteristics of a snapshot	2	"CREATE/ALTER/DROP SNAPSHOT"
ALTER SNAPSHOT LOG	Changes the storage characteristics of a snapshot log	2	"CREATE/ALTER/DROP SNAPSHOT LOG"
ALTER SYSTEM	Makes dynamic changes to the database instance	2	"ALTER SYSTEM"
ALTER TABLE	Modifies the characteristics of a table	2	"CREATE/ALTER/DROP TABLE"
ALTER TABLESPACE	Changes the characteristics of an existing tablespace	2	"CREATE/ALTER/DROP TABLESPACE"
ALTER TRIGGER	Recompiles a PL/SQL trigger	7	"ALTER TRIGGER"
ALTER USER	Changes the security and storage characteristics of a user	2	"CREATE/ALTER/DROP USER"
ALTER VIEW	Recompiles a view	2	"CREATE/ALTER/DROP VIEW"
ANALYZE	Collects or deletes statistics about an object in the database	2	"ANALYZE"
ASSOCIATE STATISTICS	Associates a method of statistics computation with database objects	2	"ASSOCIATE STATISTICS"
AUDIT	Sets up auditing for specific SQL statements in subsequent user sessions	2	"AUDIT (SQL Statements)"
AUDIT	Sets up auditing for a specific schema object	2	"AUDIT (Schema Objects)"
CALL	Executes a stored PL/SQL procedure	2	"CALL"
COMMENT	Adds a comment about a table, view, snapshot, or column	2	"COMMENT"
CREATE CLUSTER	Creates a cluster that contains at least one table with one or more columns in common	2	"CREATE/ALTER/DROP CLUSTER"
CREATE CONTROLFILE	Recreates a control file, allowing changes to some parameters	2	"CREATE CONTROLFILE"
CREATE DATABASE	Creates a database and specifies parameters associated with it	2	"CREATE/ALTER DATABASE"
CREATE DATABASE LINK	Creates a database link to provide access to objects on a remote database	2	"CREATE/DROP DATABASE LINK"
CREATE DIMENSION	Creates a dimension that defines a parent-child relationship between pairs of column sets	2	"CREATE/DROP DIMENSION"
CREATE DIRECTORY	Creates a directory object that specifies an operating system directory for storing BFILE objects	2	"CREATE/DROP DIRECTORY"
CREATE FUNCTION	Creates a stored PL/SQL function	7	"CREATE FUNCTION"
CREATE INDEX	Creates an index on at least one column of a table or cluster	2	"CREATE/ALTER/DROP INDEX"
CREATE MATERIALIZED VIEW	Creates a materialized view, also called a snapshot	2	"CREATE/ALTER/DROP MATERIALIZED VIEW"
CREATE MATERIALIZED VIEW LOG	Creates a materialized view log	2	"CREATE/ALTER/DROP MATERIALIZED VIEW LOG"
CREATE PACKAGE	Creates a PL/SQL package	7	"CREATE PACKAGE"
CREATE PROCEDURE	Creates a PL/SQL stored procedure	7	"CREATE PROCEDURE"
CREATE PROFILE	Creates a profile to set limits on database resources	2	"CREATE/ALTER/DROP PROFILE"
CREATE ROLE	Creates a role, which is a set of privileges that can be granted to users	2	"CREATE/ALTER/DROP ROLE"
CREATE ROLLBACK SEGMENT	Creates a rollback segment, which is used by Oracle to store data necessary to roll back changes made by transactions	2	"CREATE/ALTER/DROP ROLLBACK SEGMENT"
CREATE SCHEMA	Creates multiple tables and/or views, and issues grants in a single statement	2	"CREATE SCHEMA"
CREATE SEQUENCE	Creates an Oracle sequence used to automatically generate sequential numbers	2	"CREATE/ALTER/DROP SEQUENCE"
CREATE SNAPSHOT	Creates a snapshot (or materialized view)	2	"CREATE/ALTER/DROP SNAPSHOT"
CREATE SNAPSHOT LOG	Creates a snapshot log	2	"CREATE/ALTER/DROP SNAPSHOT LOG"
CREATE TABLE	Creates a table by specifying the structure or referencing an existing table	2	"CREATE/ALTER/DROP TABLE"
CREATE TABLESPACE	Creates a new tablespace, optionally specifying default storage characteristics for objects subsequently created in the tablespace	2	"CREATE/ALTER/DROP TABLESPACE"
CREATE TRIGGER	Creates a PL/SQL trigger	7	"CREATE TRIGGER"
CREATE TEMPORARY TABLESPACE	Creates a temporary tablespace	2	"CREATE TEMPORARY TABLESPACE"
CREATE USER	Creates a new database user and assigns security and storage properties	2	"CREATE/ALTER/DROP USER"
CREATE VIEW	Create a view	2	"CREATE/ALTER/DROP VIEW"
CREATE SYNONYM	Creates a public or private synonym for a database object	2	"CREATE/DROP SYNONYM"
DROP SYNONYM	Removes a public or private synonym from the database	2	"CREATE/DROP SYNONYM
DELETE	Deletes one or more rows from a table, view, or snapshot	3	"DELETE"
DISASSOCIATE STATISTICS	Disassociates a method of statistics computation from database objects	2	"DISASSOCIATE STATISTICS"
DROP CLUSTER	Removes a cluster from the database	2	"CREATE/ALTER/DROP CLUSTER"
DROP DATABASE LINK	Removes a database link from the database	2	"CREATE/DROP DATABASE LINK"
DROP DIMENSION	Removes a dimension from the database	2	"CREATE/DROP DIMENSION"
DROP DIRECTORY	Removes a directory object from the database	2	"CREATE/DROP DIRECTORY"
DROP FUNCTION	Removes a stored PL/SQL function	7	"DROP FUNCTION"
DROP INDEX	Removes an index from the database	2	"CREATE/ALTER/DROP INDEX"
DROP MATERIALIZED VIEW	Removes a materialized view (or snapshot) from the database	2	"CREATE/ALTER/DROP MATERIALIZED VIEW"
DROP MATERIALIZED VIEW LOG	Removes a materialized view log from the database	2	"CREATE/ALTER/DROP MATERIALIZED VIEW LOG"
DROP PACKAGE	Removes a PL/SQL package from the database	7	"DROP PACKAGE"
DROP PROCEDURE	Removes a PL/SQL stored procedure from the database	7	"DROP PROCEDURE"
DROP PROFILE	Removes a profile from the database	2	"CREATE/ALTER/DROP PROFILE"
DROP ROLE	Removes a role from the database	2	"CREATE/ALTER/DROP ROLE"
DROP ROLLBACK SEGMENT	Removes a rollback segment from the database	2	"CREATE/ALTER/DROP ROLLBACK SEGMENT"
DROP SEQUENCE	Removes a sequence from the database	2	"CREATE/ALTER/DROP SEQUENCE"
DROP SNAPSHOT	Removes a snapshot from the database	2	"CREATE/ALTER/DROP SNAPSHOT"
DROP SNAPSHOT LOG	Removes a snapshot log from the database	2	"CREATE/ALTER/DROP SNAPSHOT LOG"
DROP TABLE	Removes a table from the database	2	"CREATE/ALTER/DROP TABLE"
DROP TABLESPACE	Removes a tablespace from the database	2	"CREATE/ALTER/DROP TABLESPACE"
DROP TRIGGER	Removes a PL/SQL trigger from the database	7	"DROP TRIGGER"
DROP USER	Removes a user from the database	2	"CREATE/ALTER/DROP USER"
DROP VIEW	Remove a view from the database	2	"CREATE/ALTER/DROP VIEW"
EXPLAIN PLAN	Creates an explanation of the execution plan for a SQL statement	2	"EXPLAIN PLAN"
GRANT	Grants a system privilege or role to one or more users and/or roles	2	"GRANT (System Privilege or Role)"
GRANT	Grants privileges on a database object to one or more users or roles	2	"GRANT (Object Privileges)"
INSERT	Inserts a row of data into a table or view	3	"INSERT"
NOAUDIT	Stops auditing defined by a prior AUDIT statement for schema objects	2	"NOAUDIT (Schema Objects)"
NOAUDIT	Stops auditing defined by a prior AUDIT statement for SQL statements	2	"NOAUDIT (SQL Statements)"
RENAME	Changes the name of an existing table, view, sequence, or private synonym	2	"RENAME"
REVOKE	Removes a system privilege or role from one or more users and/or roles	2	"REVOKE (System Privilege or Role)"
REVOKE	Revokes privileges on a database object from one or more users or roles	2	"REVOKE (Object Privileges)"
SAVEPOINT	Identifies a point in a transaction to which you can roll back using the ROLLBACK command	3	"SAVEPOINT"
SELECT	Retrieves data from a table, view, or snapshot	3	"SELECT"
SET CONSTRAINT	Specifies at the transaction level how constraints are checked	3	"SET CONSTRAINT"
SET ROLE	Enables or disables roles for the current session	3	"SET ROLE"
SET TRANSACTION	Establishes the current transaction as read-only or read-write, or specifies the rollback segment to be used by the transaction	3	"SET TRANSACTION"
TRUNCATE	Removes all rows from a table or cluster	3	"TRUNCATE"
UPDATE	Changes the value stored in column of data in one or more tables, views, or snapshots	3	"UPDATE"

Back to: Oracle SQL: The Essential Reference