Exclude using joins without subquery

Question

how to converse following code to get the same results using join (without using subquery)

select a_key from table_a a 
inner join table_b b --in my code I've 5 joins like that
    on a.a_key=b.a_key 
where a_key not in
    (select a_key from table_c     --and conditions within this brackets also
        where var_a beteween  table_c.col1 and table_c.col2
        or var_b beteween  table_c.col1 and table_c.col2
    )

You could always store the subquery results first into a temp table and then join on that table in a second query. — ItalianStallion, Sep 02 '14 at 17:53
Why do you want rewrite the query? Perfomance? You ought to add a motivation I think. — user2672165, Sep 02 '14 at 18:04
Yes performance, optimization, joins are faster than subqueries — user3376246, Sep 02 '14 at 18:07
In this case then there is the `NOT EXISTS` as well. I don't know about the performance of it but my point is that you could ask how to improve performance then. — user2672165, Sep 02 '14 at 18:09
I've found that joins are faster and more pro, it can be enough — user3376246, Sep 02 '14 at 18:14
Your experience contradicts http://stackoverflow.com/questions/6777910/sql-performance-on-left-outer-join-vs-not-exists — user2672165, Sep 02 '14 at 18:20
I ve another select that should be connected to select i posted above, this select gets the same value of column and filters using the same way values from another table, how to connect it to this above ?it looks like select a_key from table_a a where a_key not in (select a_key from table_c --and conditions within this brackets also where var_a beteween table_c.col1 and table_c.col2 or var_b beteween table_c.col1 and table_c.col2 ) — user3376246, Sep 02 '14 at 18:21

score 0 · Answer 1 · answered Sep 02 '14 at 17:47

The following is essentially the same logic:

select a_key
from table_a a inner join
     table_b b
     on a.a_key = b.a_key left join
     table_c c
     on (var_a between table_c.col1 and table_c.col2 or
         var_b between table_c.col1 and table_c.col2
        ) and
        a.a_key = c.a_key
where c.a_key is null;

You should prefix your columns with table aliases. The column a_key is ambiguous in your original, as are the column var_a and var_b.

These are slightly different if any matching table_c.a_key values are NULL. In that case, the join version probably behaves more like you would expect.

check out my edits i've forgotten to write something – user3376246 Sep 02 '14 at 17:55 — user3376246, Sep 02 '14 at 17:55

Exclude using joins without subquery

1 Answers1