Difference Between count() and size

[Jarrod Felino]

One Student entity can have many Presentation entity, which is held as a Collection. What I need to know if there is any difference between:

select count(s.presentations) from Student s

select size(s.presentations) from Student s

[Amandeep Singh]

SIZE- Arithmetic Functions
The SIZE function returns an integer value, the number of elements of the collection. If the collection is
empty, the SIZE function evaluates to zero.

Syntax for Size-
SIZE(collection_valued_path_expression)

COUNT- Aggregate function
The following aggregate functions can be used in the SELECT clause of a query: AVG, COUNT, MAX,
MIN, SUM. the path expression that is the argument to the aggregate
function must terminate in a state-field. The path expression argument to COUNT may terminate in
either a state-field or a association-field, or the argument to COUNT may be an identification variable.

Syntax for Count-

COUNT ([DISTINCT] identification_variable | state_field_path_expression |
single_valued_association_path_expression)

[Rahul Babbar]

Personally, i would suggest the use of count() instead of size.
Because count() will get the count while querying the database,
on the other hand, size() will load the entities and then return their size, because there is no such thing as size in the db.
since you only need the number of rows, better get them from the db instead of getting from java.

[Christophe Verré]

Rahul Babbar wrote:better get them from the db instead of getting from java.

The SIZE function is not used to get anything from java. The persistence provider will turn the JPQL in a native query, whether you use COUNT or SIZE. SIZE is a function used in conditions, so it's different from COUNT.

[Rahul Babbar]

Thanks Chris,

What i meant was if we use size(), then the persistence provider 'may'(not sure) load all the records and then return their size. which will not be good, since it'll affect performance.
If the persistence provider converts it into appropriate count query and executes on the db(as you said), it should be fine.

Thanks for the clarification..

[Christophe Verré]

I think the misunderstanding came from the first post. There's no such JPQL like "select size(s.presentations) from Student s". The size function is used in conditions. It's a handy function which avoids to type in subqueries. For example the following :

SELECT c FROM Company c WHERE SIZE(c.employees) > 100

could be written like :

SELECT c FROM Company c WHERE ( SELECT COUNT(e) FROM c.employees e ) > 100

[Amandeep Singh]

the same thing in my explanation but without example. :idea: