I have two tables: Customer and State. These two tables are linked by m:m relationship.

So there is customer_state table.

Customer: 8000 rows
cust_num
f_name
l_name
state_Code

State: 36,000 rows
state_Code
state_Desc

Customer_state:
cust_num
state_code

I have the following query in hbm:

<set
name="states" fetch="join" table="customer_state" lazy="false"
>
<key column="cust_num" />
<element type="string" column="state_code"/>
</set>

When run, it is executing the following query for each customer. So for each 8000 rows, it is substituing
cust_num in the following query.


select
cust.cust_num as customer1_1_0_,
cust.FIRST_NAME as FIRST4_1_0_,
..(all columns of cust)
st.cust_num as customer1_2_,
st.state_code as st2_2_
from
customer cust,
state st
where
cust.cust_num=st.cust_num(+)
and cust.cust_num= '02066165'

So my question is, is there any way to fetch all the rows at once without going through each row of
customer_number and executing the above.

In otherwords, I need the result of the following without cust_number mentioned.
select
cust.cust_num as customer1_1_0_,
cust.FIRST_NAME as FIRST4_1_0_,
..(all columns of cust)
st.cust_num as customer1_2_,
st.state_code as st2_2_
from
customer cust,
state st
where
cust.cust_num=st.cust_num(+)