join versus explicit in condition

are there some valid reasons, in a Oracle db, to preferring in a generic query, a filter condition expressed by a join table , instead of a filter with an IN condition with a large number of elements (some hundreds). I mean if you can write something like

SELECT .... FROM t1 WHERE t1. IN (......) with 100-200 items

or if it is better to change it with

SELECT .... FROM t1 JOIN t2 ON t1. = T2.

where the t2 table contains the values needed for the filter

many thanks

Thanks for the answers

I try to explain the situation and my doubt I have an user interface where the user can choose in a control many items (for example one or mo开发者_运维百科re people a list of professionals). I can use directly this list adding this in a IN condition, that is SELECT .... FROM t1 WHERE t1. IN (p1... p200) but this solution, could raise some problems: - if the selected items are a lot, then the string can exceed a limit of sql string (I remember in Oracle existed a limit of 4000 bytes) - an IN condition with many valuesmay be inefficient

So an alternative solution can be 1. create a temporary table with the selected item 2. using a join between the temparary table and the main table

Usually the filling of a temporary table is fast and my question is if this second solution is more efficient of the first

The two queries are not functionally equivalent, so the question is somewhat odd--I can't imagine this comes up very often (if ever).

That said, if you have a table that contains exactly the rows that need to be filtered, a JOIN would be a more natural/standard way to handle it.

Is the idea in the first example is to query t2 to get all the values, then add them to a collection and generate an IN clause? If so, I would say this would be a very bad practice.

From what I see, there are two different questions.

a) Using a Static List/table.

If the (100-200) item list is a list of static values, for eg.Let's say a list of Countries or currencies, I think it would be better to add this to a static table/parameter table and change the query to use the table instead. If you need to track a new code/country etc. later, all you need to do later is insert a new code in the look up table.

Also, if there are other queries that use the same conditions (and there usually are), this look up table will promote re-use.

select * from t1 where id in (select id from t2);
and 
select * from t1,t2
 where t1.id = t2.id 

are both equivalent and better than

select * from t1 where 
  id in ('USD','EUR'..... ); -- 100 to 200 items to track.

b) The choice of Join vs IN:

It really does not matter a lot. The final query that oracle executes will be the transformed version of your query which might evaluate to the same query in both cases.

You should see which of the two queries are more easier to read and convey the intentions correctly.

Useful Link : http://explainextended.com/2009/09/30/in-vs-join-vs-exists-oracle/

http://explainextended.com/2009/09/30/in-vs-join-vs-exists-oracle/