No if the count returns values (suppose emp_class = 2) the result set will contain 4 values!
I want (emp_class = 1) returns 0 if it returns null
Sorry, I don't understand you.
Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
Always say which version of Oracle you're using (for example, 22.214.171.124.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Are you interested in emp_class=1 or emp_class=2? If you're interested in emp_class=1, then include "emp_class=1" in the WHERE clause, like you originally did. It won't matter how many rows (if any) have emp_class=2.
If you're interested in emp_class=2, then say "emp_class = 2" in the WHERE clause.
Also, COUNT never returns NULL. It always returns a non-negative integer.