Register

GO Classes DPP | GATE CS, DA | DBMS | IN Subquery Equivalence

ago
25 views
0 0 votes

Consider,

SELECT DISTINCT TBL1.COL1
FROM TBL1
WHERE COL1 IN
      (SELECT COL1 FROM TBL2);

Which SQL statement produces the same result?

  1. SELECT DISTINCT TBL1.COL1
FROM TBL1

UNION

SELECT TBL2.COL1
FROM TBL2;

  2. SELECT DISTINCT TBL1.COL1
FROM TBL1
WHERE EXISTS
(SELECT *
FROM TBL2
WHERE TBL1.COL1 = TBL2.COL1);

  3. SELECT DISTINCT TBL1.COL1
FROM TBL1, TBL2
WHERE TBL1.COL1 = TBL2.COL1
AND TBL1.COL2 = TBL2.COL2;

  4. SELECT DISTINCT TBL1.COL1
FROM TBL1
LEFT OUTER JOIN TBL2
ON TBL1.COL1 = TBL2.COL1;

1 Answer

0 0 votes

The original condition:

$\mathrm{TBL1.COL1\ IN\ (SELECT\ COL1\ FROM\ TBL2)}$

means:

Keep a $\mathrm{TBL1}$ row if some row in $\mathrm{TBL2}$ has the same $\mathrm{COL1}$.

Option B expresses exactly that:

$\mathrm{WHERE\ EXISTS\ (SELECT\ *\ FROM\ TBL2\ WHERE\ TBL1.COL1=TBL2.COL1)}$

Option A returns the union of values from both tables, including values that occur only in $\mathrm{TBL2}$.

Option C adds another condition:

$\mathrm{TBL1.COL2=TBL2.COL2}$

which was not required in the original query.

Option D is a left outer join, so it also retains $\mathrm{TBL1}$ rows having no match.

Hence:

Answer : $\boxed{\mathrm{B}}$

Note : The primary concept here is still the meaning of $\mathrm{IN}$: membership of an outer value in the result relation produced by a subquery.

ago
Voting & Accepting an answer helps improve the community
← Previous · GO Classes DPP | GAT...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

0 0 votes
1 1 answer
30
30 views
GO Classes asked 18 hours ago
30 views
GO Classes DPP | GATE CS, DA | DBMS | NOT IN Query Equivalence
Consider relations:$\mathrm{Product}$$\mathrm{Inventory}$where the underlined attributes in the original examination are primary keys.The following query returns product ...
0 0 votes
1 1 answer
53
53 views
GO Classes asked 18 hours ago
53 views
GO Classes DPP | GATE CS, DA | DBMS | Nested Query
Relevant relations are:$\mathrm{BUILDING(BID,\ BName,\ Address)}$$\mathrm{IN\_BUILDING(EID,\ BID)}$Which of the following queries correctly find the names of buildings wh...
1 1 vote
2 2 answers
38
38 views
GO Classes asked 19 hours ago
38 views
GO Classes DPP | GATE CS, DA | DBMS | Duplicate Tuples
Consider the relation $R(a,b)$. The relation may contain duplicate tuples.Which of the following queries are guaranteed not to contain duplicate tuples, regardless of the...
0 0 votes
1 1 answer
103
103 views
GO Classes asked 3 days ago
103 views
GO Classes DPP | GATE CS, DA | DBMS | Nested Query
Three relations with the same schema are maintained for three regions:$\mathrm{TokyoProduct}$, $\mathrm{NagoyaProduct}$, and $\mathrm{OsakaProduct}$$\mathrm{ProductNo}$ i...