Chapter 4: Retrieve data from more than one table

implicit syntax

Pre SQL92 join Syntax. Not adding INNER

cross join

Result set that includes each row from the first able joined with each row from the second table the result set is known as the Cartesian product

minus operator

A query that excludes rows from the first query if they also occur in the second query

intersect operator

A query that only includes rows that occur in both queries

table alias

Alternate name for a table used to easily identify a table and shorten the typing. Helpful when creating large queries with 1 or more joins.

set operators

Combine the results of two (or more) SELECT statements. Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS.

Explicit Syntax

The join syntax introduced in the SQL-92 standard. Ex. adding INNER to JOIN

When can you use the USING keyword?

To simplify syntax for joining tables the join can be an inner or an outer join the table must be joined by a column that has the same name in both tables must be equijoin equals operator is used to compare the two columns

How many join conditions can there be?

Two or more connected by AND or OR operators.

How can you combine inner and outer joins?

You can combine inner and outer joins with a SELECT statement

What must you do when coding self joins?

You must use aliases

What is a multi-table join?

a series of two-table joins proceeding from left to right

Union

combines the result sets of two or more SELECT statements into one result set each result set must return the same number of columns and corresponding columns must have the same data type

When can you use the NATURAL keyword?

creates a natural join that simplifies the syntax for joining tables. Can be used to join two tables based on all columns in the two tables that have the same name.

right outer join

includes all rows from second, or right, table

full outer join

includes all the rows from both tables

self join

is a join where a table is joined with itself

join

is used to combine columns from two or more tables into a result set based on the join conditions you specify

Why isn't common place to use a right join?

it's easier to read statements that join two or more tables

ad hoc relationships

join tables based on relationships not defined in the database

outer joins

retrieves all rows that satisfy the join condition, plus unmatched rows in one or both tables

inner join

rows that satisfy that satisfy the join condition are included in the result

left outer join

the result set of includes all the rows from the first or left table

qualified column name

used when two columns in a join condition have the same name.