This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes count SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "count SQL" Watch "count SQL" New topic

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 from customer, student where =

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

Joined: Aug 22, 2010
Posts: 3733

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.
I agree. Here's the link:
subject: count SQL
It's not a secret anymore!