Consider the following relation instances: Supplier: S

Consider the following relation instances:

Supplier:

Sid     Name     Age
1        Partha     25
2        Neha       30
3        Vinit        29

Catalog:

Sid     Partid     Qty
1         101         5
1         102         6
2         101         8
3         102         9

Parts:

Partid     Partname
101             P1
102             P2

What is the number of rows returned by the following query?
> select S.name from Supplier S
   where not exists (select * from Catalog C
                               where S.sid = C.sid and exists (select count(*) from Parts where Partname = 'P5')

11Comments
Shreyans Dhankhar shreyans 22 Oct 2014 08:53 pm

ANSWER IS 0 coz inner query must return false in order to get the output

Parimal Andhalkar parimal_andhalkar 25 Oct 2014 12:20 am

if x AND null  = null

ans is

Sid     Name     Age
1        Partha     25
2        Neha       30
3        Vinit        29

Parimal Andhalkar parimal_andhalkar 25 Oct 2014 12:44 am

its ans is zero

Navdeep swaminavdeep 8 Nov 2014 11:35 am

I guess the return type of  EXISTS is Boolean .. true or false

So

select S.name from Supplier S
   where NOT EXISTS (select * from Catalog C
                               where S.sid = C.sid and EXISTS (select count(*) ..... )  )

-- So "EXISTS (select count(*) ..... )"always returns false and where condition always stands false... so inner query is returns nothing ;

i.e. [ select * from Catalog C  where S.sid = C.sid and EXISTS (select count(*) ..... )] is empty hence...   NOT EXISTS  return true ..hence it must return all three tuples of Supplier.

please tell if I am wrong!!

kalpish kalpishsingh 15 Jan 2015 01:42 am

 @swaminavdeep yes this is the ryt concept 
I don't know how they are changing this underlined concept :/

Himadri himz94 12 Oct 2015 11:45 am

Ya i m agree with you .now doubt is how they can give 0 tuple 

Ranita Biswas ranita 9 Sep 2016 05:58 pm

It's a tricky question. And, answer is indeed 0.

The given query is:

select S.name from Supplier S
   where not exists (select * from Catalog C
                               where S.sid = C.sid and exists (select count(*) from Parts where Partname = 'P5')
 

As there is no part with Partname = 'P5', "select count(*) from Parts where Partname = 'P5'" gives you 0, not 0 row, a single row, containing value 0. Now, as there is a row returned by it, "exists (select count(*) from Parts where Partname = 'P5')" gives you TRUE. So, our whole query basically turns out to be:

select S.name from Supplier S
   where not exists (select * from Catalog C
                               where S.sid = C.sid)

As you can see "select * from Catalog C where S.sid = C.sid" selects all the rows from Catalog table, "not exists(select * from Catalog C where S.sid = C.sid)" gives you FALSE.
Therefore, the final query returns 0 number of rows.

Meet Shah smartmeet 19 Jan 2017 12:26 pm

So what if there is Part-105 existed, then also there will be some count value-1 or 2 or N and it is in one ROW, so answer of final query won't be changed, right?

Ranita Biswas ranita 19 Jan 2017 06:22 pm

Yes, you are right. The final result won't be changed won't be changed.

Suman suman08513 20 Jan 2017 07:47 pm

@ranita mam,

Can u please explain possible scenarios for handling NULL values. Like when EXISTS/NOT EXISTS, ALL/ANY is used in outer query and if the inner query retuns NULL value. What could be the output for each of the cases above..

ravindra indra5 7 Dec 2016 10:30 am

select * from  emp_acc_bal where  not   exists(    select * from  emp_acc_bal where null=null ) ;...this is giving me all records of  table