Oracle Query: Composite WHERE clause.

It's been a long night here at the office, me and my partner tryin to fix some trouble and do some testing, there are condition where I need to query records from transaction table, based on combination of fields from other table.

There are several ways to achive this kind of results:

1. Using a subquery
select * from table_trx a where a.subscriber_no in (select subscriber_no from table_reference b where b.offers = a.offers);

2. Using join
select * from table_trx a join table_reference b on a. subscriber_no = b.subscriber_no and a.offer = b.offer;

3. Using composite where clause
Select * from table_trx a where (a.subscriber_no, a.offer) in (select b.subscriber_no, b.offer from table_reference);

And #3 I just figured out last night (yes, shame on me), I'm using the third query just to verify that the results I'm getting from first or second query is correct, because I'm not quite sure whether the second query would have been accurate. While the first and second query will returns the same results, the second one might a little bit different, because if we using different kind of join, this will mostly returns different set of results.


Keywords: composite where clause query

Regards,
- Zeft.