Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

count SQL

 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic