SQL allows duplicate tuples in relations, and correspondingly defines the…
2014
SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:
select * from R where a in (select S.a from S)- A.
select R.* from R, S where R.a=S.a
- B.
select distinct R.* from R,S where R.a=S.a
- C.
select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
- D.
select R.* from R,S where R.a=S.a and is unique R
Attempted by 154 students.
Show answer & explanation
Correct answer: C
Correct query: select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
Explanation: The nested query keeps each row of R if its a value appears at least once in S. The IN condition checks existence of matching a values and does not multiply rows of R based on how many matching rows S has.
A plain join between R and S duplicates each row of R once per matching row in S, so it can increase the multiplicity of R rows.
Using (select distinct a from S) produces one row per distinct a value, so joining R with that result gives at most one match per a value and preserves the multiplicity coming from R itself.
Therefore the join with distinct a reproduces the existence-based semantics of IN and returns exactly the same rows as the nested query.
Illustrative example: R has rows (a=1,id=10) and (a=1,id=11); S has two rows with a=1. The IN query returns the two rows from R. A plain join returns four rows (each R row paired with both S rows). Joining R with (select distinct a from S) returns the two R rows, matching the IN query.
Conclusion: The query that joins R with the distinct a values from S is equivalent to the nested IN query because it implements the same membership test without changing the multiplicity of R's tuples.
A video solution is available for this question — log in and enroll to watch it.