SQL1

Question

Consider the following R

A	1	2	3	4
B	NULL	1	2	2

select * from R as R1 where not exists (select * from R where B = R1.A)

The number of tuple returned by SQL query is ____________

Comments

2 ??

---

yes , tell me approach

---

a[] = { 1, 2, 3, 4}

b[]= {null, 1, 2, 2}

count=0;

for (i=0; i< 4; i++)

{

flag=1;

for ( j=0;j<4 ; j++)

{

if (a[i]==b[j])                       //null== (any not null)  -> false..
{
flag=0;
break;
}

}

if(flag)
count++;

}

printf(count)

---

here it is asking for "select * from R  as R1"

but I think u r doing "select A from R  as R1"

---

no @srestha ... I am counting number of tuples ..

see this ...

where B = R1.A

---

In B , here existing NULL

In A , here exists 3,4

rt?

---

yes .....so ..??  didn't get you ..

didn't you get what i want to say by my code ??

---

ans = 2 is coming just because of elements 3 and 4 of attribute A.. which have no matching with any element of attribute B.

---

yes u r taking j inside i

and then takes i as output, which is 2 rt?

but in this query

R and R1 both have A and B  separately

Now inner query will take R.B=R1.A //which gives only 1 tuple as output, because 3 values of B column is matching with A column

And then outer query (not exists) keyword gives output 4-1=3 tuples as output

---

@vijay that I told in my 1st command , i.e. u r taking A tuple only in consideration

---

Is there any fault in my logic?

---

Now inner query will take R.B=R1.A //which gives only 1 tuple as output, because 3 values of B column is matching with A column

I think in this line you are making mistake ...

see ...

outer loop - R1     // select * from R as R1 where not exists (select * from R where B = R1.A)

 inner loop  - R

both R and R1 are same but 

 R.B=R1.A

 is doing everything... 

---

yes then inner query return from R

And outer query will return from R1

A	1
B	NULL

rt?

because in other 3 columns R.B=R1.A

---

@vijay I am not getting how "select * from R where B = R1.A" is taking 2 tuples?

See according to below table it is taking 3 rows. Plz tell me where am I wrong? :(

---

@srestha ...  

select * from R as R1 where not exists (select * from R where B = R1.A)

This query results - All the tuples where the element of attribute A does not have any match with any elements of attribute B of the same relation R.

And here element 3 and 4 of attribute A do not have any matching with the all elements of attribute B( NULL, 1, 2, 2). So ans = 2.

And  if you want to know, how the scanning is being done here, then you can see the above pseudo code written above - 

And still if you have doubt then take a deep sleep for half an hour and then come back and  read all this again  .. you will get everything...

---

Hm,

Answer 1

R1.A	R1.B	R.A	R.B
1	null	1	null
2	1	2	1
3	2	3	2
4	2	4	2

For each row of R1 , (select * from R where B = R1.A) executes...Row from R1 printed when null return from from inner query...

For R1.A = 3 and R1.A = 4 there is no matching R.B in any row so for these two row it will return null ...and condition given" doesn't exist" ... So these two row will be printed...

Comments

cannot understand @Gabbar

According to ur table

inner query will "select * from R where B = R1.A"

R.A	R.B
2	1
3	2
4	2

As it have to return whole tuple, not R.A individually it can take. rt?

---

I think I have made it complex...I'll update in simple way...

---

No I think the table is all right.

Answer 2

@Srestha. When you compare a number with null, its value may or may not be true. But since, you are not sure if they are equal, you take the comparison to be false. Now, evaluate.