if you count with count(‘x):
SQL> select count (‘x’) from departments;
COUNT(‘X’)
———-
27
If you count empty strings or NULLs:
SQL> select count (”) from departments;
COUNT(”)
———-
0
Again try with space then the result
SQL> select count (‘ ‘) from departments;
COUNT(”)
———-
27
But if the count is hidden in a subqry and if the count is disquised with bind var then:
SQL> variable x varchar2(10);
SQL> exec : x := ‘test’;
PL/SQL procedure successfully completed.
SQL> select : x as bindvar, d.*
2 from departments d
3 where ( select count (:x)
4 from employees e
5 where e.department_id = d.department_id
6 ) > 0;
BINDVAR DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
——————————– ————- —————————— ———- ———–
test 10 Administration 200 1700
test 20 Marketing 201 1800
test 30 Purchasing 114 1700
test 40 Human Resources 203 2400
test 50 Shipping 121 1500
test 60 IT 103 1400
test 70 Public Relations 204 2700
test 80 Sales 145 2500
test 90 Executive 100 1700
test 100 Finance 108 1700
test 110 Accounting 205 1700
11 rows selected.
If you count with Null then:
SQL> exec : x := ”;
PL/SQL procedure successfully completed.
SQL> select : x as bindvar, d.*
2 from departments d
3 where ( select count (:x)
4 from employees e
5 where e.department_id = d.department_id
6 ) > 0 ;
no rows selected
SQL> select count(*), count(last_name), count(1) from employees;
COUNT(*) COUNT(LAST_NAME) COUNT(1)
———- —————- ———-
107 107 107
SQL> select count (*), count(‘x’), count(1) from employees;
COUNT(*) COUNT(‘X’) COUNT(1)
———- ———- ———-
107 107 107
Here in the above example:
Count(*) counts all occurrences including nulls. This is a row count for a given table.
Count(last_name) counts all non null occurrences of values in last_name. If ‘last_name’ has null values, count(last_name) will be less than count(*).
Count(1) counts all null values in a “pseudo column” created by the constant. Since the constant 1 will never be null, this would be similar to count(*) that is nothing but row count.
