Restricting a LEFT JOIN

Question

I have a table, let's call it "a" that is used in a left join in a view that involves a lot of tables. However, I only want to return rows of "a" if they also join with another table "b". So the existing code looks like

SELECT ....
FROM main ...
...
LEFT JOIN a ON (main.col2 = a.col2)

but it's returning too many rows, specifically ones where a doesn't have a match in b. I tried

SELECT ...
FROM main ...
...
LEFT JOIN (
   SELECT a.col1, a.col2
   FROM a
   JOIN b ON (a.col3 = b.col3)) ON (a.col2 = main.col2)

which gives me the correct results but unfortunately "EXPLAIN PLAN" tells that doing it this way ends up forcing a full table scan of both a and b, which is making things quite slow. One of my co-workers suggested another LEFT JOIN on b, but that doesn't work because it gives me the b row when it's present, but doesn't stop returning the rows from a that don't have a match in b.

Is there any way to put the main.col2 condition in the sub-SELECT, which would get rid of the full table scans? Or some other way to do what I want?

Answer 1

SELECT ...
FROM ....
LEFT JOIN ( a INNER JOIN b ON .... ) ON ....

Answer 2

1. add a where (main.col2 = a.col2)

2. just do a join instead of a left join.

Answer 3

What if you created a view that gets you the "a" to "b" join, then do your left joins to that view?

Answer 4

    Select ... 
    From Main 
     Left Join a on main.col2 = a.col2
    where a.col3 in (select b.col3 from b) or a.col3 is null

you may also need to do some indexing on a.col3 and b.col3

Answer 5

First define your query between table "a" and "b" to make sure it is returning the rows you want:

Select
   a.field1,
   a.field2,
   b.field3
from
   table_a a

   JOIN table_b b
      on (b.someid = a.someid)

then put that in as a sub-query of your main query:

select
   m.field1,
   m.field2,
   m.field3,
   a.field1 as a_field1,
   b.field1 as b_field1
from
   Table_main m

   LEFT OUTER JOIN
      (
      Select
         a.field1,
         a.field2,
         b.field3
      from
         table_a a

         JOIN table_b b
            on (b.someid = a.someid)
      ) sq
   on (sq.field1 = m.field1)

that should do it.

Ahh, missed the performance problem note - what I usually end up doing is putting the query from the view in a stored procedure, so I can generate the sub-queries to temp tables and put indexes on them. Suprisingly faster than you would expect. :-)

Ron