By and large these HQL clauses are synonymous with their SQL equivalent (NB: these are ANSI SQL, not Transact-SQL, they will work on all complient databases).

The difference here is the word "fetch". This is used to support eager fetching, whereby you know you are going to use the data in the associated entity so you tell Hibernate to get it at the same time, rather than relying on lazy fetching. In this way you avoid multiple extra select queries when you access an associated property in your result set. If you turn on sql logging in Hibernate and try the two you'll see the difference.

Be aware that there are restrictions on when you can use eager fetching. Have a read of the documentation to see what these are.

I feel the document is for expert user to see!! it write "the associated objects should not be used in the where clause ..."

then can I write something like 1)"from Supplier s inner join fetch s.products as p where s.products.price > 100";

also, the document write ""Nor should fetch be used together with setMaxResults() or setFirstResult() as these operations are based on the result rows, which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you'd expect."

I cannot understand what it said!! for example, I want to retrieve 1000 rows, even with duplicate record, then can I use .setMaxResults(1000); ??

then can I write something like 1)"from Supplier s inner join fetch s.products as p where s.products.price > 100";

It doesn't make sense to write what you've written (and it wouldn't make much sense if this were a SQL query either). You've defined a join on products and assigned this association an alias of p, then you've refered to products without using the alias. The only situation where an eager fetch (remember, as I said earlier this is a different thing than a simple join) might need an alias is if you are fetching accross two associated collections. i.e. if products had a collection of items for example.

also, the document write ""Nor should fetch be used together with setMaxResults() or setFirstResult() as these operations are based on the result rows, which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you'd expect."

I cannot understand what it said!! for example, I want to retrieve 1000 rows, even with duplicate record, then can I use .setMaxResults(1000); ??

No. But you can use them if you use a normal join, rather than one that uses eager fetching.