Oracle SQL Oracle SQL Joins and Subqueries 3

Question 1

What is the difference between IN and EXISTS in Oracle SQL for subqueries?

Accepted Answer

EXISTS stops at first match (short-circuit), IN evaluates all subquery rows

Answer

EXISTS short-circuits as soon as a matching row is found, while IN collects all subquery results before comparing, making EXISTS more efficient for large subqueries.

Question 2

In Oracle SQL, which join returns all rows from both tables, with NULLs where there is no match?

Accepted Answer

FULL OUTER JOIN

Answer

A FULL OUTER JOIN returns all rows from both tables, filling NULLs for unmatched columns on either side.

Question 3

What does NOT EXISTS return if the subquery finds no matching rows in Oracle SQL?

Accepted Answer

TRUE

Answer

NOT EXISTS returns TRUE when the subquery finds no matching rows, making it useful for finding rows absent from another table.

Question 4

Which Oracle SQL clause is used to specify the join condition in an ANSI-style join?

Accepted Answer

ON

Answer

The ON clause specifies the explicit join condition when column names differ between tables or when complex conditions are needed.

Question 5

What happens if a scalar subquery returns more than one row in Oracle SQL?

Accepted Answer

Oracle raises ORA-01427 error

Answer

Oracle raises ORA-01427 'single-row subquery returns more than one row' error when a scalar subquery context receives multiple rows.

Oracle SQL Practice Test

Oracle SQL Practice Test

Oracle SQL Oracle SQL Joins and Subqueries 3