1Z0-071 Joining Multiple Tables 1 — Questions and Answers
Question 1: Which JOIN type returns all rows from the left table and matching rows from the right table, with NULLs for non-matches?
- INNER JOIN
- FULL OUTER JOIN
- LEFT OUTER JOIN (Correct answer)
- CROSS JOIN
Correct answer: LEFT OUTER JOIN
LEFT OUTER JOIN returns all rows from the left table and matched rows from the right; unmatched right columns are NULL.
Question 2: What is the result of a CROSS JOIN between a table with 4 rows and a table with 3 rows?
- 4 rows
- 3 rows
- 7 rows
- 12 rows (Correct answer)
Correct answer: 12 rows
A CROSS JOIN (Cartesian product) returns every combination, so 4 × 3 = 12 rows.
Question 3: Which SQL syntax represents an ANSI-standard INNER JOIN?
- SELECT * FROM a, b WHERE a.id = b.id
- SELECT * FROM a JOIN b ON a.id = b.id (Correct answer)
- SELECT * FROM a INNER b WHERE a.id = b.id
- SELECT * FROM a, b ON a.id = b.id
Correct answer: SELECT * FROM a JOIN b ON a.id = b.id
The ANSI JOIN syntax uses the JOIN keyword with an ON clause to specify the join condition.
Question 4: What is a NATURAL JOIN in Oracle SQL?
- A join that uses all columns with the same name in both tables (Correct answer)
- A join using a WHERE clause only
- A join that always returns all rows from both tables
- A join specified with a USING clause
Correct answer: A join that uses all columns with the same name in both tables
NATURAL JOIN automatically joins tables on all columns that share the same name and compatible data type.
Question 5: Which clause in a JOIN allows you to specify a single common column name without a table prefix?
- ON
- USING (Correct answer)
- WHERE
- BY
Correct answer: USING
The USING clause specifies a common column by name, and that column cannot be prefixed with a table alias.
Question 6: What type of join returns rows from both tables regardless of whether a match exists?
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN (Correct answer)
Correct answer: FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no matching row.
1Z0-071 Joining Multiple Tables 1
Which JOIN type returns all rows from the left table and matching rows from the right table, with NULLs for non-matches?