I have two independent tables TRANSACTION and LOCK in my database. All the unprocessed records(processed='N') are selected by the query for processing from TRANSACTION table..
Before processing, the record entry is stored in LOCK table.

The main reason for storing the entry in LOCK table is to avoid processing of more than one transaction record with the same account and cusip number at a given time(this is handled in my java code).

The record is deleted from the LOCK table after completion of processing and the processed flag is changed to 'Y' in TRANSACTION table for that record.

Here are the two hibernate files

<hibernate-mapping>
<class name="Transaction" table="TRANSACTION" lazy="false">
<id name-"TransactionId" type ="java.lang.Long">
<column name="TRAN_ID" precision="8" scale="0" />
<generator class="sequence">
<param name="sequence">SEQ_TRANSACTION</param>
</generator>
</id>

<property name="accountNumber" type="string">
<column name="ACCOUNT_NUMBER" length="8" />
</property>

<property name="cusipNumber" type="string">
<column name="CUSIP_NUMBER" length="12" />
</property>

<property name="netAmount" type="big_decimal">
<column name="NET_AMOUNT" precision="18" scale="6" />
</property>

......
......

</class>

<query name="Transaction.getUnProcessedRecords">
select new list(tran.accountNumber,tran.cusipNumber) from
Transaction as tran where tran.processed = 'N' order by tran.insertedDate
</query>

<hibernate-mapping>



<hibernate-mapping>
<class name="Lock" table="LOCK" lazy="false">

<composite-id>
<key-property name="accountNumber" type="string">
<column name="ACCOUNT_NUMBER" length="8" not-null="true"/>
</key-property>

<key-property name="cusipNumber" type="string">
<column name="CUSIP_NUMBER" length="12" not-null="true"/>
</key-property>
</composite-id>

</class>

<hibernate-mapping>


Now the requiredment is to modify the query to select unprocessed(processed='N') and unlocked (No entry in LOCK table) records from the TRANSACTION table.
I modified the query as shown below. Can someone please let me know whether the below change is a valid one or not.

<query name="Transaction.getUnProcessedRecords">
select new list(tran.accountNumber,tran.cusipNumber) from
Transaction as tran
(not exists
( select lk.accountNumber, lk.cusipNumber
where tran.accountNumber = lk.accountNumber
and tran.cusipNumber = lk.cusipNumber )
)
and tran.processed = 'N' order by tran.insertedDate
</query>

If you are asking whether one can join tables with no mapped association, and no PK->FK relationship, the answer is yes. Using a theta-join, as you did in your last example.

Your externalized query is in HQL, which supports theta-joins.

An alternative (more efficient?) way of finding records that are in one table but not in another, is making a LEFT JOIN between Transaction and Lock, and the returning those records with nulls on the Lock side.