Oracle SQL Oracle SQL Joins and Subqueries 1 — Questions and Answers
Question 1: Which type of JOIN returns only rows where there is a match in BOTH tables?
- LEFT OUTER JOIN
- FULL OUTER JOIN
- INNER JOIN (Correct answer)
- CROSS JOIN
Correct answer: INNER JOIN
An INNER JOIN returns only those rows where the join condition is satisfied in both tables, excluding unmatched rows.
Question 2: What does a LEFT OUTER JOIN return in Oracle SQL?
- Only rows matching in both tables
- All rows from the left table and matching rows from the right (Correct answer)
- All rows from the right table and matching rows from the left
- All rows from both tables regardless of match
Correct answer: All rows from the left table and matching rows from the right
A LEFT OUTER JOIN returns all rows from the left table and NULL for unmatched columns from the right table.
Question 3: In Oracle SQL, what is the legacy syntax equivalent of a LEFT OUTER JOIN?
- table1, table2 WHERE table1.id(+) = table2.id
- table1, table2 WHERE table1.id = table2.id(+) (Correct answer)
- table1 LEFT JOIN table2 USING (id)
- table1, table2 WHERE table1.id *= table2.id
Correct answer: table1, table2 WHERE table1.id = table2.id(+)
Oracle's proprietary (+) operator placed on the right side of the condition creates a LEFT OUTER JOIN.
Question 4: What does a CROSS JOIN produce in Oracle SQL?
- Only matching rows from both tables
- A Cartesian product of all rows from both tables (Correct answer)
- Rows that exist in one table but not the other
- NULL rows for non-matching records
Correct answer: A Cartesian product of all rows from both tables
A CROSS JOIN produces a Cartesian product, pairing every row from the first table with every row from the second table.
Question 5: Which Oracle SQL subquery type returns a single value used in a WHERE clause comparison?
- Correlated subquery
- Scalar subquery (Correct answer)
- Inline view
- Exists subquery
Correct answer: Scalar subquery
A scalar subquery returns exactly one row and one column, making it suitable for use with single-value comparison operators like =, >, <.
Question 6: What is a self-join in Oracle SQL?
- Joining a table to itself using aliases (Correct answer)
- Joining two copies of different tables
- A join that uses no WHERE condition
- A join between primary and foreign key columns only
Correct answer: Joining a table to itself using aliases
A self-join joins a table to itself using different aliases, commonly used for hierarchical data like employee-manager relationships.
Oracle SQL Oracle SQL Joins and Subqueries 1
Which type of JOIN returns only rows where there is a match in BOTH tables?