SQL Join Semantics: “Right-side rows with no match on the left”
In relational databases, a SQL outer join extends matching rows by also returning rows that have no corresponding key match on the other side. The question asks for the join that returns records from the right table which have no matching key in the left table, and that are included in the result set.
The correct answer is (ii) Right outer join.
A RIGHT OUTER JOIN keeps all rows from the right table; when a right row does not match any left key, the left columns appear as NULL in the output.
By contrast, a FULL OUTER JOIN would include unmatched rows from both sides, and a LEFT OUTER JOIN focuses on unmatched rows from the left side.
Finally, “Half outer join” is not a standard SQL join type (and is generally not recognized as a distinct join operator).
Key terms you should know here:
- key
- match
- NULL padding
- result set
How to choose the correct join from the wording
- 1Step 1
The question says “included in the result set” for rows from the right table that have no match on the left—this points to an outer join that preserves the right side.
- 2Step 2
If only one side’s unmatched rows are included, use LEFT OUTER JOIN or RIGHT OUTER JOIN. If both sides’ unmatched rows are included, use FULL OUTER JOIN.
- 3Step 3
For RIGHT OUTER JOIN, unmatched right rows are kept; the left columns for those rows become NULL.
- 4Step 4
Match the behavior to the options: the correct mapping is (ii) Right outer join.
Quick memory rule
For RIGHT OUTER JOIN, think: “Right rows never disappear.” Left-side fields may become NULL.
Avoid confusing FULL vs RIGHT
If the question said “unmatched rows from both tables,” then the answer would be FULL OUTER JOIN. Since it specifies only right-table unmatched rows, use RIGHT OUTER JOIN.
Join Behavior for Unmatched Rows (Which side is preserved?)
Preserved side determines which table’s unmatched key rows appear in the result.
Answer verification
Choosing the join type from the question’s phrasing
Keyword: “right table”
Step AIndicates the join must preserve rows from the right side."
Keyword: “no matching key in the left table”
Step BRequires an outer join so unmatched keys are still returned."
Final selection
Step CRIGHT OUTER JOIN matches exactly: right rows included; left columns become NULL for non-matches."
Knowledge Check
Which join refers to join records from the right table that have no matching key in the left table and are included in the result set?
Explore Related Topics
Relational Algebra Equivalence: Why $\pi_A(R) - \pi_A((\pi_A(R) \times S) - R)$ Represents Division
The expression
[ \pi_A(R)-\pi_A\big((\pi_A(R)\times S)-R\big) ]
is a derived form of the relational‑algebra division operator, returning all (A) values that pair with every tuple in (S).
- Division is defined as (R\div S={a\mid\forall b\in S,;(a,b)\in R}).
- The formula works by (1) projecting candidate (A) values, (2) forming all required ((A,B)) pairs with (S), (3) subtracting existing pairs to find missing ones, (4) projecting the missing (A) values, and (5) removing them from the candidates.
- In the example, (R(A,B)={(1,x),(1,y),(2,x),(2,y),(3,x)}) and (S(B)={x,y}) yield (R\div S={1,2}).
- This construction captures the universal (“for all”) query pattern, unlike selection, join, or simple projection.
Key Constraints and Nullability: Which One Cannot Be NULL?
Object Query Language (OQL) and Its Relation to SQL