Lesson 1

In your previous experiences or courses you have learned the concept of joining tables and how to create queries that implement inner, outer, left, and right joins. This module looks into the unique way that Oracle handles table joins and sub-queries.

By the end of this module, you will know how to:

1. Identify two proper syntax formats for the IN clause
2. Interpret an outer JOIN using Oracle’s syntax
3. Identify the connection between a query and a sub-query
4. Use EXISTS instead of IN for a sub-query
5. Describe the parsing sequence of a query that use a query in place of a table name

The module looks at joins and sub-queries by focusing on examples of inner and outer joins, subqueries and correlated subqueries.
In the next lesson, you will learn two different ways to code a sub-query that uses the IN clause.

A join combines the output from exactly two row sources, such as tables or views, and returns one row source. The returned row source is the data set. A join is characterized by multiple tables in the

1. WHERE (non-ANSI) or
2. FROM ... JOIN (ANSI) clause

of a SQL statement. Whenever multiple tables exist in the FROM clause, Oracle Database performs a join.
A join condition compares two row sources using an expression. The join condition defines the relationship between the tables. If the statement does not specify a join condition, then the database performs a Cartesian join, matching every row in one table with every row in the other table.

Within the Oracle database, the basic structure is a table. Oracle Database 11g supports many types of tables, including the following:

1. Relational tables: Using the Oracle-supplied datatypes you can create tables to store the rows inserted and manipulated by your applications. Tables have column definitions, and you can add or drop columns as the application requirements change. Tables are created via the create table command.
2. Object-relational tables: To take advantage of features such as type inheritance, you can use Oracle's object-relational capabilities. You can define your own datatypes and then use them as the basis for column definitions, object tables, nested tables, varying arrays, and more.
3. Index-organized tables: You can create a table that stores its data within an index structure, allowing the data to be sorted within the table.
4. External tables: Data stored in flat files may be treated as a table that users can query directly and join to other tables in queries. You can use external tables to access large volumes of data without ever loading them into your database.
   Note that Oracle also supports BFILE datatypes, a pointer to an external binary file. Before creating a BFILE or an external table, you must create a directory alias within Oracle (via the create directory command) pointing to the physical location of the file.
5. Partitioned tables: You can divide a table into multiple partitions, which allows you to separately manage each part of the table. You can add new partitions to a table, split existing partitions, and administer a partition apart from the other partitions of the table. Partitioning may simplify or improve the performance of maintenance activities and user queries. You can partition tables on ranges of values, on lists of values, on hashes of column values, or on combinations of those options.
6. Materialized views: A materialized view is a replica of data retrieved by a query. User queries may be redirected to the materialized views to avoid large tables during execution, the optimizer will rewrite the queries automatically. You can establish and manage refresh schedules to keep the data in the materialized views fresh enough for the business needs.
7. Temporary tables: You can use the create global temporary table command to create a table in which multiple users can insert rows. Each user sees only his or her rows in the table.
8. Clustered tables: If two tables are commonly queried together, you can physically store them together via a structure called a cluster.
9. Dropped tables: You can quickly recover dropped tables via the flashback table to before drop command. You can flash back multiple tables at once or flash back the entire database to a prior point in time. Oracle supports flashback queries, which return earlier versions of rows from an existing table.