SQL standard enables portability of SQL applications across conforming software products. Oracle has always been an active contributor to the SQL standard. In Oracle9i release, several new features are compliant with ANSI/ISO SQL99 standard. One of the most notable among these would be the ANSI compliant joins.

A join is a query that combines rows from two or more tables, views, or materialized views. 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.

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.

Natural Joins

A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns.

Inner Joins

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition. Inner join is the default join type for a join operation.

Outer Joins

An outer join extends the result of an inner join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
• To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.

More Info

	Oracle 9i SQL Reference Release 1: Chapter 7 - SQL Queries and Other SQL Statements
	Oracle 9i SQL Reference Release 1: Chapter 17 - SQL Statements: Select

Oracle9i Database Daily Features

Archives