aspose file tools*
The moose likes JDBC and the fly likes count SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "count SQL" Watch "count SQL" New topic
Author

count SQL

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
I have a table Cutomer(name, id, phone) and Student(name, department). Assuming "name" is a unique valued column, do you think both of the following two queries generate the "number of customers whose name are in Student table" ?

1. select count(distinct customer.name) from customer, student where customer.name = student.name

2. select count(*) from customer, student where customer.name = student.name
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The two statements are the same if and only if the name column is declared UNIQUE and NOT NULL. Remember, null values will not be counted by count() function, and if the name column is not NOT NULL, you cannot know in advance whether there are some null values.

Sorry, I've completely forgot about the Student table.

If the name column is unique in both tables, then yes, the two queries are the same. As opposed to what I've stated above, null values are already eliminated by the where clause.

Also, count(distinct name) might be a lot more expensive in databases that are not "smart" enough to note that the name column is unique. Do not use distinct if you really do not need it.

However, I'd suggest to always use count(*) to count rows. This is the standard in most databases, and it is immediately obvious to anyone reading your code that you want to count the rows.
 
jQuery in Action, 2nd edition
 
subject: count SQL