query

Question

Consider the following instance $R$.

A	1	2	3	4
B	Null	1	2	2

The number of tuples returned by the following SQL query is 

> select * from $R$ as $R1$

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

Answer 1

The relation is put in landscape mode :)

$R = \left\{(1, NULL), (2,1), (3,2), (4,2) \right\}$

Now, a tuple is output if where condition is TRUE. Here the where condition is NOT EXISTS which returns true iff we give empty set to it. So, the condition for output here is that the inner query should not return any tuple.

Based on query, the inner query returns a tuple if the 'A' value being considered exist in any of the tuple as a 'B' value. This happens for A values, 1 and 2 and hence NOT EXISTS is FALSE for these two as the inner query returns $\{(2,1)\}$ and $\{(3,2), (4,2)\}$ respectively. For the tuples $(3,2)$ and $(4,2)$, the inner query returns $\{\}$ and hence they are output. So, the output is $\{(3,2),(4,2)\}$ and answer is 2.

Answer 2

There us typo in ques..it should be select * from R1

Now for first 2 tuples inner query returns something so they will not be present in op now for A=3 and A=4 inner query returns empty set..so they will be in op..so 2 tuples are returned..

Answer 3

comparison with NULL is neither true noe false . its unknown so i think. the result will be unknown.

http://www-cs-students.stanford.edu/~wlam/compsci/sqlnulls

Comments

where NULL = 1?

This returns NULL and hence the where condition becomes NULL. So, there is no issue with NULL in given question. See the second point below- here the tuple with "NULL" is just ignored. 

• A boolean comparison between two values involving a NULL returns neither true nor false, but unknown in SQL's three-valued logic. [3] For example, neither NULL equals NULL nor NULL not-equals NULL is true. Testing whether a value is NULL requires an expression such as IS NULL or IS NOT NULL.
• An SQL query selects only values whose WHERE expression evaluates to true, and groups whose HAVING clause evaluates to true.
• The aggregate COUNT(*) counts all NULL and non-NULL tuples; COUNT(attribute) counts all tuples whose attribute value is not NULL. Other SQL aggregate functions ignore NULL values in their computation. [4]

---

Arjun sir, Why no issue because  ( where NULL =3 ?) can be true or false, it is unknown behaviour. So if it evaluates to true, then it will impact our answer.

---

I have edited the explanation. Please see.

Answer 4

Only two tuples will be displayed...

The reason behind is that inner query executes for each tuple of outer query so the the tuples selected by inner query is -->

1,NULL

2,1

2,1

so the outer query will display the tuples which are not present in the above..i.e.

3,2

4,2

so the answer is 2 tuples.

click on this link for further clarification --> http://sqlfiddle.com/#!9/e366e/1