edited by
478 views
1 votes
1 votes

Consider the following instances $R.$

A 1 2 3 4
B Null 1 2 2

The number of tuples returned by the following $\text{SQL query}$ is$?$

$>$select $*$ from $R$ as $R_{1}$ 

where not exists$($select $*$ from $R$               

                              where $B = R_{1}.A)$

edited by

2 Answers

2 votes
2 votes
A B

1

2

3

4

-

1

2

2

select ∗ from R as R1

                        where NOT EXISTS (select ∗ from R where B=R1.A)

 

this is co-related sub-query. for every row of the outer query the sub-query will be evaluated

first row for R1-> R1(1, NULL) here R1.A=1, R1.B=NULL

we check which tuples of R satisfies the condition in the sub-query => B=R1.A

so it will returns all those tuples where B=1. there is only one such tuple <2,1>

 

now NOT EXISTS clause will return true if the sub-query evaluates no rows

here for R1(1, NULL) the sub-query returns one such. Thus, the NOT EXISTS clause evaluates to false

 

for the row R1(2,1) the sub-query returns <1, NULL>, here B=1 and R1.A=1

again the NOT EXISTS clause evaluates to false as the sub-query doesn't returns empty set

 

R1(3,2) here R1.A=3, R1.B=3
there is not such row in R such that R1.A=B=3 so NOT EXISTS condition evaluates to true

similarly when you check for R1(4,2) NOT EXIST will return true

so the number of tuples=2

0 votes
0 votes

In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query. Because the subquery may be evaluated once for each row processed by the outer query.

Link :https://en.wikipedia.org/wiki/Correlated_subquery

Here , the answer is two.

For the tuple (3,2) and (4,2) inner correlated subquery returns empty set and WHERE NOT EXISTS {empty set} is true.

So two rows are selected.

Related questions

1 votes
1 votes
1 answer
1
Naveen K Verma asked Jan 10, 2018
680 views
Consider the following instance R.A1234BNull122The number of tuples returned by the following SQL query is _____________. select * from R as R1 where not exists (select...
0 votes
0 votes
1 answer
2