Most Efficiently Written Query in SQL -

i have 2 tables, table1 , table2 , trying select values table1 based on values in table2. writing query follows:

select value table1 (key1 in     (select key1 table2 foo = bar)) , (key2 in     (select key2 table2 foo = bar)) 

this seems inefficent way code query, there better way write this?

it depends on how table(s) indexed. , depends on sql implementation you're using (sql server? mysq1? oracle? ms access? else?). depends on table size (if table(s) small, table scan may faster more advanced). matters, too, whether or not indices covering indices (meaning test can satisfied data in index itself, rather requiring additional look-aside fetch corresponding data page.) unless @ execution plan, can't technique x "better" technique y.

however, in general, case, you're better off using correlated subqueries, thus:

select * table1 t1 exists( select *               table2 t2               t2.key1 = t1.key1             )   , exists( select *               table2 t2               t2.key2 = t1.key2             ) 

a join possibility, too:

select t1.* table1 t1 join table2 t2a = t2a.key1 = t1.key1 ... join table2 t2b = t2b.key2 = t1.key2 ... 

though give 1 row every matching combination, though can alleviated using distinct keyword. should noted join not more efficient other techniques. especially, if have use distinct requires additional work ensure distinctness.