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 ....