CODEWITHSUNDEEP
sql
rwolst
rwolst

Reputation: 13672

Most Efficiently Written Query in SQL

I have two tables, Table1 and Table2 and am trying to select values from Table1 based on values in Table2. I am currently writing my query as follows:

SELECT Value From Table1
WHERE
(Key1 in
    (SELECT KEY1 FROM Table2 WHERE Foo = Bar))
AND
(Key2 in
    (SELECT KEY2 FROM Table2 WHERE Foo = Bar))

This seems a very inefficent way to code the query, is there a better way to write this?

Upvotes: 0

Views: 98

Answers (1)

Nicholas Carey
Nicholas Carey

Reputation: 74287

It depends on how the table(s) are indexed. And it depends on what SQL implementation you're using (SQL Server? MySq1? Oracle? MS Access? something else?). It also depends on table size (if the table(s) are small, a table scan may be faster than something more advanced). It matters, too, whether or not the indices are covering indices (meaning that the test can be satisfied with data in the index itself, rather than requiring an additional look-aside to fetch the corresponding data page.) Unless you look at the execution plan, you can't really say that technique X is "better" than technique Y.

However, in general, for this case, you're better off using correlated subqueries, thus:

select *
from table1 t1
where exists( select *
              from table2 t2
              where t2.key1 = t1.key1
            )
  and exists( select *
              from table2 t2
              where t2.key2 = t1.key2
            )

A join is a possibility, too:

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

though that will give you 1 row for every matching combination, though that can be alleviated by using the distinct keyword. It should be noted that a join is not necessarily more efficient than other techniques. Especially, if you have to use distinct as that requires additional work to ensure distinctness.

Upvotes: 5

Related Questions