Oracle Certification Chapter 4
Terms
undefined, object
copy deck
- What is a Cartesian Join?
-
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 - What is a Equality join?
-
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 - What is a Non Equality Join
-
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 - What is a Self Join
-
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 - What is an Outer Join
-
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 - What is the Outer Join Operator
- 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.
- Set operators
- Used to combine results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT and MINUS
- Example of an equality join using traditional method.
-
SELECT title, name
FROM books, publisher
WHERE books.pubid = publisher.pubid; - Example of an equality join using traditional method with table alias:
-
SELECT title, p.pubid, name
FROM publisher p, books b
WHERE p.pubid = b.pubid; - Example of an equality join using NATURAL JOIN
-
SELECT title, pubid, name
FROM publisher NATURAL JOIN books; - Example of an equality join using JOIN...USING
-
SELECT title, pubid, name
FROM books JOIN publisher
USING(pubid); - Example of an equality join using JOIN...ON
-
SELECT title, name
FROM books b JOIN publisher p
ON b.pubid = p.pubid; - Non-Equality Join Traditionl method
-
SELECT title, gift
FROM books, promotion
WHERE retail
BETWEEN minretail AND maxretail; - Non-Equality Join - JOIN method
-
SELECT title, gift
FROM books JOIN promotion
ON retail BETWEEN minretail AND maxretail;