This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
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
The two statements are the same if and only if the name column is declared UNIQUEandNOT 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.