Question 64

Semantics of the Given Query:

select * from R where a in (select S.a from S)

• The subquery returns the multiset of values of $S.a$.
• For each tuple $r \in R$, the IN predicate evaluates to TRUE if $r.a$ matches at least one value in $S.a$.
• Crucially, even if $r.a$ appears multiple times in $S$, the tuple $r$ is output only once per instance of $r$ in $R$.
• If $R$ contains duplicate tuples, each duplicate tuple in $R$ is evaluated independently and preserved.

Evaluating Options:

• (A) select R.* from R, S where R.a=S.a: If a value of $a$ appears $k$ times in $S$, the tuple $r \in R$ will be duplicated $k$ times in the cross join, changing multiplicity.
• (B) select distinct R.* from R,S where R.a=S.a: The DISTINCT keyword eliminates pre-existing duplicate tuples from $R$, changing multiplicity.
• (C) select R.* from R,(select distinct a from S) as S1 where R.a=S1.a:
  The subquery (select distinct a from S) produces a relation where each matching attribute value appears exactly once.
  Therefore, each tuple in $R$ matches at most one tuple in $S_1$, preserving the exact multiplicity of $R$ without generating spurious duplicate rows or discarding legitimate duplicates in $R$.

Conclusion:
Option (C) produces the identical answer under SQL multiset semantics.