Oracle Database/10g Advanced SQL

This documentation details the usage of the latest query methodology on the Oracle 10g DBMS.

=Joins=

Join queries combine rows from two or more tables, views, or materialized views. If multiple tables are listed in the query's FROM clause the Oracle Database performs a join. Columns from any of the tables may be listed in the select list. Columns that exist in both tables, however, must be qualified, in order to avoid ambiguity.

The following query returns the mortgage information for all payments received from customers during the year 2007.

The other way of writing the same query can be

NATURAL JOIN
The NATURAL JOIN joins two tables which contain a column or multiple columns with the same name and data-type.

The following query joins the customer table to the invoice table with a natural join, the natural join utilizes the customer_id that is present on both the customer table and the invoice table. It returns the customer and invoice data for invoices that have not had any payments made on them.

INNER JOIN
Most of the commonly used joins are actually INNER JOINs. The INNER JOIN joins two or more tables, returning only the rows that satisfy the JOIN condition. Here are some examples of INNER JOINs.

This joins the customer and order table, connecting the customers to their orders. The result contains a combined list of customers and their orders, if a customer does not have an order, they are omitted from the result.

The Other way of writing query is

OUTER JOIN
The OUTER JOIN joins two or more tables, returning all values whether or not the join condition is met. When a value exists in one table but not the other, nulls are used in the place of the columns that are joined to a record without a JOIN companion.

There are three specific types of outer joins: FULL OUTER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN.

FULL OUTER JOIN
With the FULL OUTER JOIN the query will return rows from either of the tables joined, whether or not there is any matching data on the table joined. If no matching data exists, nulls are placed into the fields where data would have otherwise existed.

In the following example, the data in a table is synced with the data that is regularly imported into a data import table via SQL Loader. A stored procedure is then used to see if anything was added, updated or removed and the rows are merged accordingly.

LEFT OUTER JOIN
With the LEFT OUTER JOIN the query will return rows only if the row exists in the table specified on the left side of the join. When no matching data is found from the table on the right side of the join, nulls are placed into the fields where the data would have otherwise existed.

The following example will return all of the customers and their associated cases if they have one. If the customer has no case then it will only return the data for the customer.

RIGHT OUTER JOIN
With the RIGHT OUTER JOIN the query will return rows only if the row exists in the table specified on the right side of the join. When no matching data is found from the table on the left side of the join, nulls are placed into the fields where the data would have otherwise existed.

The following example will return a list of trucks and their cargo. If a truck has no cargo then a null will be put in place of the field specifying the cargo's load_id.

=Subqueries=

UNION [ALL]
The UNION operator outputs the items that exist in both result sets. The UNION ALL operator outputs all of the items in the two sets, whether or not both sets contain the item.

The following query returns all customers from San Francisco whose balance is 100000 and 500000.

MINUS
The query after the MINUS operator is removed from the result set of the queries before the operator.

In the following example, the first part of the query gets all of the customers. In the second part of the inactive customers are taken out. Finally, in the third part of the query, customers with zip codes between 80000 and 90000 removed from the set.

INTERSECT
The INTERSECT operator only returns the results that are present in both of the queries.

The following example returns all of the customers who have a balance due in Los Angeles.

=Case Statements= The following queries are equivalent, they return all of the customers from Switzerland. The CASE statement translates the single character status flags "A" and "I" to "ACTIVE" and "INACTIVE" If a value is NULL then it returns the string "NULL"

Basic Usage
The simplest form of a CASE statement specifies the variable and then the possible values to check for.

Searched Case
The searched CASE expression is the more advanced form of case. Instead of specifying the value to be checked at the beginning, each WHEN statement has a comparison that is checked.