Can some one please explain to me why do I get different result for two following queries.

Query 1:
Select c from Category c join c.items i where i.name = 'Item1'


The generated SQL (show_sql=true):
select category0_.categoryID as categoryID1_, category0_.name as name1_ from externdb.dbo.Category category0_ inner join externdb.dbo.Item items1_ on category0_.categoryID=items1_.categoryID where items1_.name='Item1'
select items0_.categoryID as categoryID1_, items0_.itemID as itemID1_, items0_.itemID as itemID0_0_, items0_.categoryID as categoryID0_0_, items0_.name as name0_0_ from externdb.dbo.Item items0_ where items0_.categoryID=?


This one returns categery that contains atleast one item with name 'Item1' and all items in that category.

Query 2:
Select c from Category c join fetch c.items i where i.name = 'Item1'

The generated SQL (show_sql=true):
select category0_.categoryID as categoryID1_0_, items1_.itemID as itemID0_1_, category0_.name as name1_0_, items1_.categoryID as categoryID0_1_, items1_.name as name0_1_, items1_.categoryID as categoryID0__, items1_.itemID as itemID0__ from externdb.dbo.Category category0_ inner join externdb.dbo.Item items1_ on category0_.categoryID=items1_.categoryID where items1_.name='Item1'



This one returns categery that contains item with name 'Item1' and only items in with name 'Item1'

Mapping documents:

<hibernate-mapping package="com" default-access="field" >

<class name="Category" table="externdb.dbo.Category">
<id name="categoryID" type="long" column="categoryID"/>
<property name="name" type="string" column="name"/>
<set name="items" access="property" lazy="false">
<key column="categoryID"/>
<one-to-many class="Item"/>
</set>

</class>

</hibernate-mapping>

<hibernate-mapping package="com">

<class name="Item" table="externdb.dbo.Item">

<id name="itemID" type="long" column="itemID"/>
<property name="categoryID" type="long" column="categoryID"/>
<property name="name" type="string" column="name"/>

</class>

</hibernate-mapping>

The first one queries the Items for the Category with a second query and therefore the WHERE clause isn't applied. The 2nd query simply returns all Items for each Category returned by the first query.

The second one, using join fetch, retrieves all of the data in one big query so only those items which match the where clause are retrieved.

Does this mean that fetch strategy not only changes the way data is retrieved but also effects what data is retrieved?


How can I retrieve Category with specific items and not use join fetch?

Thank you in advance for your answear?

You can use a filter on the collection.

Thank you for your answer, I was able to solve my problem using filter. However I am still puzzled about roll of fetch on the returned result. I understand what is the purpose or fetch strategy but I can' understand why would it effect returned result.