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.