Oracle Certification Chapter 4

A join that replicates each row from the first table with every row from the second table. It creates a join between tables by displaying every record combination

Can be created through two methods:

1) not including a joining condition in a WHERE clause
2) using the JOIN method with the CROSS JOIN keywords

Creates a join through a commonly names and defined column

Can be created by two methods:

1) using the WHERE clause
2) using the join method with the NATURAL JOIN or JOIN...ON or JOIN...USING keywords

Joins tables where there are no equivalent rows in the tables to be joined, e.g., to match values in one column of a table with a range of values in another table. Can be created in two ways:

1) using a WHERE clause
2) using the JOIN method with the JOIN...ON keywords

Joins a table to itself.
Can be created by two methods:

1) Using a WHERE clause
2) using the JOIN method with the JOIN...ON keywords

Includes records of a table in output when there is no matching record in the other table.
Can be created by two methods:

1) Using the WHERE clause
2) using the JOIN method with the OUTER JOIN keywords, and also the keywords LEFT, RIGHT, or FULL

It is used to indicate the table containing the deficient rows. The operator is placed next to the table that should have null rows added to create a match.

Used to combine results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT and MINUS

SELECT title, name
FROM books, publisher
WHERE books.pubid = publisher.pubid;

SELECT title, p.pubid, name
FROM publisher p, books b
WHERE p.pubid = b.pubid;

SELECT title, pubid, name
FROM publisher NATURAL JOIN books;

SELECT title, pubid, name
FROM books JOIN publisher
USING(pubid);

SELECT title, name
FROM books b JOIN publisher p
ON b.pubid = p.pubid;

SELECT title, gift
FROM books, promotion
WHERE retail
BETWEEN minretail AND maxretail;

SELECT title, gift
FROM books JOIN promotion
ON retail BETWEEN minretail AND maxretail;