GATE CS 2014 Set 2 — Question 64

The original query selects all tuples from $R$ where the attribute $a$ exists in the relation $S$. The IN clause does not cause duplication of rows from $R$ even if $S$ contains duplicates of $a$. It simply checks for existence.(A) select R.* from R, S where R.a=S.a
This is a standard join. If $S$ has multiple rows with the same $a$ value, the result will contain multiple copies of the corresponding row from $R$. This changes the multiplicity of the result compared to the original query.(B) select distinct R.* from R,S where R.a=S.a
This removes duplicates from the result. However, the original relation $R$ might contain duplicate rows that satisfy the condition. The original query preserves duplicates present in $R$, whereas DISTINCT would remove them. Thus, this is not equivalent.(C) select R.* from R, (select distinct a from S) as S1 where R.a=S1.a
Here, the subquery S1 contains only unique values of $a$ from $S$. Joining $R$ with S1 ensures that each row in $R$ matches at most one row in S1. Therefore, rows in $R$ are not duplicated by the join, but existing duplicates in $R$ are preserved (since they each match the single corresponding entry in S1). This is equivalent to the IN clause behavior.(D) Syntax is invalid/non-standard.