CODEWITHSUNDEEP
mysqlcount
JamieITGirl
JamieITGirl

Reputation: 191

MySQL COUNT() and duplicates

I have this query: 

select count(name) as nr  
from team where city='ny' and name=ANY  
(select teamName from contract where playerCode=ANY  
(select code from player where name='X' and surname='Y'));

I don't understand why the count() function doesn't count the duplicates even if there is no distinct clause.

These are the tables: 

Player(code, name, surname)  
Contract(id, playerCode, teamName, year)  
Team(name, city)

With this integrity constraints: 

Contract(playerCode)-->Player(code)  
Contract(teamName)-->Team(name)

The query extracts the number of teams of NY city which have a contract with a player named X,Y. Thanks.

Upvotes: 0

Views: 151

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

Your query will count duplicates. My guess is that you are expecting duplicates from multiple matches in the subquery. However, the subquery is not a JOIN and so will not be duplicating results from the team table, regardless of how many matches there are in the subquery.

Each row in the team table will only have the WHERE conditions evaluated once, and will be included (once) based on whether those conditions are satisfied.

Assuming there are no NULL values in team.name, if you SELECT COUNT(name), COUNT(*) both fields should have the same value.

Upvotes: 1

Related Questions