I have following simple sql code to select the number of rows that meet a specific criteria:
SELECT COUNT(CASE WHEN /*condition*/ THEN 1 END) FROM table_name;
Verus
SELECT COUNT(*) FROM table_name WHERE /*condition*/;
Both of them seems to work the same when I only want to do some aggregate function to the data.
I have two questions:
Thanks for any responds!
The usual answer to "which way is faster" usually depends on your DB. The way to find out is to use your DB's "explain" tool to see how it's providing the answer to your query.
As to your other question, I've never see COUNT(CASE WHEN /*condition*/ THEN 1 END) in the 30yrs I've been working with DBs.
I had to think about it, to even be sure it'd work. As it relies on an implicit else null and that the count function ignores NULL.
In your case, I'd expect them to be the same for most DB's. However, that's only because I'd expect most DB's to rewrite the query into the same form of
COUNT(*) FROM table_name WHERE /*condition*/
This form of the query can definitely take advantages of indexes, whereas the other form might not.
SELECT COUNT(CASE WHEN /*condition*/ THEN 1 END) FROM table_name; a little bit faster since SELECT COUNT(*) FROM table_name WHERE /*condition*/; will first loop over the whole table and filter the unneeded result and again loop over the table to count the number of them, but SELECT COUNT(CASE WHEN /*condition*/ THEN 1 END) FROM table_name; will need 1 loop and you will get the result. Does it work like this way? I am new to SQL, please correct me if I am wrong!
\$\endgroup\$
WHERE clause. It may not need to read the actual table at all. Without a where clause, the query engine will need to read through the entire table.
\$\endgroup\$