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?

score 6 · Accepted Answer · answered Apr 16 '09 at 20:41

6

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

answered Apr 16 '09 at 20:41

Joel Coehoorn

399,467
113
570
794

For some odd reason, the left join is now acting like a normal join. – Paul Tomblin Apr 16 '09 at 20:58

score 3 · Answer 2 · answered Apr 16 '09 at 20:42

3

add a where (main.col2 = a.col2)
just do a join instead of a left join.

answered Apr 16 '09 at 20:42

tpdi

34,554
11
80
120

I need to do a LEFT JOIN, because I need to return the rows in main that don't have a match in a. – Paul Tomblin Apr 16 '09 at 20:50
Sure, but can't you join the join of (main, a) to b as a non-left join? – tpdi Apr 16 '09 at 21:38
There isn't a row of b for every row of main. Doing a non-left join would leave out most of main, wouldn't it? – Paul Tomblin Apr 16 '09 at 21:48

score 0 · Answer 3 · answered Apr 16 '09 at 20:42

0

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

answered Apr 16 '09 at 20:42

Nick DeVore

9,748
3
39
41

score 0 · Answer 4 · answered Apr 16 '09 at 20:53

0

    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

answered Apr 16 '09 at 20:53

Jeremy

6,580
2
25
33

score 0 · Answer 5 · edited Sep 27 '11 at 16:15

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

That looks exactly like what I described in the question. – Paul Tomblin Apr 16 '09 at 20:58 — Paul Tomblin, Apr 16 '09 at 20:58

Restricting a LEFT JOIN

5 Answers5

Linked