Information Management: 3.2 Displaying Data from Multiple Tables

American National Standards Institute

ANSI stands for what?

Structured Query Language (SQL)

Is the information-processing industry-standard language of relational database management systems (RDBMS).

JOIN clause

This clause combines fields from two or more tables.

NATURAL JOIN

Is based on all columns in two tables that have the same name and selects rows from the two tables that have equal values in all matched columns.

CROSS JOIN

The ANSI/ISO SQL: 1999 SQL this joins each row in one table to every row in the other table. The result set represents all possible row combinations from the two tables.

USING clause

This clause specifies the columns that should be used for the join. In a natural join, if the tables have columns with the same names but different data types, the join causes an error. To avoid this situation, the join clause can be modified with this clause.

WHERE clause

The USING clause allows us to use what clause to restrict rows from one or both tables?

ON clause

This allows a greater variety of join conditions to be specified. This clause also allows us to use WHERE to restrict rows from one or both tables. This join clause is required when the common columns have different names in the two tables.

OUTER JOIN

When a join returns the unmatched rows as well as the matched rows, what it is called?

LEFT, FULL, RIGHT

Outer join syntax uses what terms?

FULL OUTER JOIN

It is possible to create a join condition to retrieve all matching rows and all unmatched rows from both tables. Using this join solves this problem. The result set of this join includes all rows from a left outer join and all rows from a right outer join combined together without duplication.

SELF-JOIN

Is used to join a table to itself as if it was two tables.

Hierarchical Queries

Is a method of reporting the branches of a tree in a specific order. Closely related to self-joins. Using this query, you can retrieve data based on a natural hierarchical relationship between rows in a table.

START WITH, CONNECT BY PRIOR, LEVEL

Hierarchical queries have their own new keywords, which are?

START WITH

Hierarchical Queries: Identifies which row to use as the Root for the tree it is constructing.

CONNECT BY PRIOR

Hierarchical Queries: Explains how to do the inter-row joins

LEVEL

Hierarchical Queries: Specifies how many branches deep the tree will traverse.

WHERE clause or CONNECT BY PRIOR clause

How can branches be pruned from a tree in SQL? What clause do you use?

Specified row is excluded

What happens when the WHERE clause is used to prune a branch?

Equijoin

Sometimes called a "simple" or "inner" join, this join is a table join that combines rows that have the same values for the specified columns. This join is equavalent to ANSI: NATURAL JOIN, JOIN USING, JOIN ON (when the join condition uses "=")

Equals (=)

An Equijoin uses what operator to specify the join condition.

Cartesian Product

This is a combination of each row of one table with each row of the other. Is equivalent to an ANSI CROSS JOIN. If two tables in a join query have no join condition specified in the WHERE clause or the join condition is invalid, the Oracle Server returns the this of the two tables.

Table Alias

Is similar to a column alias; it renames an object within a statement. It is created by entering the new name for the table just after the table name in the from-clause.

FROM clause

If a table alias is used in what clause, then that table alias must be substituted for the table name throughout the SELECT statement.

AND operator

To join three tables, you need to add another join condition to the WHERE clause using what operator?

Nonequijoin

Is equivalent to an ANSI JOIN ON (where the condition used is something other than equals).

Outer Join

Is used to see rows that have a corresponding value in another table plus those rows in one of the tables that have no matching value in the other table.