CODEWITHSUNDEEP
mysqlsql
Polish
Polish

Reputation: 466

How does this SQL query work?

Table name - 'tab'

Schema - (a int,b int)

|  a  |  b  |    
|  1  |  5  |    
|  2  |  10 |    
|  3  |  15 |

select count(*) from tab where exists 

(select count(*) from tab where b=2);

It gave 3 as output. here My doubt is that, we have no value = 2 in column 'b'(sub-query) so it returns 0, which i think is equivalent to 'not-exists', so why I get 3 as output ?

Thanks.

Upvotes: 0

Views: 69

Answers (1)

siride
siride

Reputation: 209455

It's not the equivalent of NOT EXISTS. A SELECT COUNT(*) always returns something: a single row with the count of values given by the rest of the query. If the rest of the query would return 0 rows (no results), then the result of SELECT COUNT(*) is zero, which is not NULL and is not an empty result set. A single row with a single column with value 0 still exists, so the result of EXISTS(...) will be true.

I think what you want is:

select count(*) from tab where exists
(select 1 from tab where b=2);

In this case, if there is no row in tab where b is 2, then no results will return from the subquery and the EXISTS will be false.

Of course, you can easily rewrite this query as:

SELECT COUNT(*)
  FROM tab
 WHERE b <> 2;

But I assume this is actually part of something more complex.

Upvotes: 2

Related Questions