The GATE Grind

GATE 2014 CS (CS2) – Question 64

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Multiple choice

SQL allows duplicate tuples and multiplicities in joins. Which query always gives the same result as “select * from R where a in (select S.a from S)”?

  1. select R.* from R,S where R.a=S.a
  2. select distinct R.* from R,S where R.a=S.a
  3. select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
  4. select R.* from R,S where R.a=S.a and is unique R

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (C) select R.* from R,(select distinct a from S) as S1 where R.a=S1.a

Explanation

IN tests whether a matching value exists, so duplicates in S must not multiply an Rrow. Deduplicating only S.a before the join achieves that while retaining duplicate Rrows. Therefore **C**. A plain join multiplies matches and DISTINCT R.*would incorrectly remove original Rduplicates.