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

@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.