Database-SQL

Question

sid	sname	rating	age
18	jones	3	30.0
41	jonah	6	56.0
22	ahab	7	44.0
63	moby	null	15.0

Consider a below relation Sailors

We want to find the names of all sailors with a higher rating than all sailors with age<21

The following two SQL queries attempt to obtain the answer to the above question.Do they both Compute the same result?Under what condition, would they compute the same result?

Q1:  Select S.sname

       FROM Sailors S

       WHERE  NOT EXISTS ( SELECT * 

                                               FROM Sailors S2

                                               WHERE S2.age<21

                                               AND S.rating <=S2.rating)

 

Q2:   SELECT *

          FROM Sailors S

          WHERE S.rating>ANY(SELECT S2.rating

                                                FROM Sailors S2

                                                WHERE S2.age<21)

 

 

I think first query returns all the sailors name because the co-related nested query when selects S2.age<21 picks up sailor with sid 63, but as soon as comparison S.rating<=S2.rating is done, it results in Unknown as comparison with Null is Unknown and the whole inner query for each sailor records goes empty and NOT EXISTS(EMPTY SET)=TRUE.

In the Second query, in nest query returns an empty set, and .>ANY(Empty Set)=False for all sailor records and hence no record will be selected.

So, NO, both queries will not give same result.Infact, both give incorrect result but different result sets.

Now suppose I give a rating to the Sailor with sid 63 having age=15.Now if this sailor is given highest rating , then both queries will give same result → the empty set.

 

Is my analysis Correct?

Comments

Now suppose I give a rating to the Sailor with sid 63 having age=15.Now if this sailor is given highest rating , then both queries will give same result → the empty set.

for the given instance, yes both result emptyset

---

yes

here telling same

https://gateoverflow.in/185060/sql-keyword-any-and-all

---

ALL evaluates to TRUE if inner query returns no tuples. 

Is there any logic  behind?

---

Even 2nd query doesnot have join condition

right?

I mean

AND S.rating <=S2.rating

 

---

mam, 2nd query is co-related query !

---

I have a question that is because of comparison with null is UNKNOWN that's why whole query return empty set???....otherwise in first query 30<21 false AND anything will return false and NOT EXIST will make it TRUE and it can print sname JONES...

---

I have a question that is because of comparison with null is UNKNOWN that's why whole query return empty set???

yes...

---

@ Shaik Masthan thanks:)

---

http://sqlfiddle.com/#!9/ae01f9/1/0

 please check here it returns all the names when i feed the sql query can you verify  it?

---

in the question also, Ayush wittten same thing, right ?

I think first query returns all the sailors name

then what is the problem @Nilabja Sarkar

---

i have a hard time to understand the null return type queries that's why try to clear my doubts. here null is compared with other ratings but still it returns the name mobi but it should not return anything

---

inner query returns empty set due to NULL

then the over all query look like as

       Select S.sname

       FROM Sailors S

       WHERE  NOT EXISTS ( emptyset )

Not exist return true on emptyset ===> all tuples from S display in the result !

---

$\forall x \{ \phi \}$ is always true because there is no single x, for which the expression becomes false.

$\exists x \{ \phi \}$ always false because there is not even a single x, for which it can become true.

$\forall xP(x) \equiv(P(x_1)\land P(x_2).....P(x_n))$

$\exists xP(x) \equiv(P(x_1 \lor P(x_2) \lor ....P(x_n))$