Left Join and Where Clause

Question

Query -

Select * FROM tbl1 
LEFT OUTER JOIN tbl2 ON  tbl1.id   = tbl2.id 
                     AND tbl2.col2 = 'zyx' 
                     AND tbl2.col3  = 'abc' 
WHERE tbl1.col1 = 'pqr'

Here I'm doing left outer join and using 'and' along with join. (First I was using joins 'and' part in 'where' but my results were not correct)

My query, which is very similar to this, runs for a long time; it takes at least 10 seconds to run. Is there a way to optimize these types of query?

Answer 1

You could add indices on those columns on which you compare values.

Answer 2

Create the following indexes:

CREATE INDEX ix_tbl1_1_id ON tbl1 (col1, id)
CREATE INDEX ix_tbl2_2_3_id ON tbl2 (col2, col3, id)

If id is a CLUSTERED PRIMARY KEY in the corresponding tables, you can omit it from the index, since it will be implicitly included there anyway:

CREATE INDEX ix_tbl1_1 ON tbl1 (col1)
CREATE INDEX ix_tbl2_2_3 ON tbl2 (col2, col3)

Answer 3

Maybe

Select * FROM tbl1, tbl2
WHERE tbl1.id = tbl2.id 
AND tbl1.col1 = 'pqr' 
AND tbl2.col2 = 'zyx' 
AND tb2.col3 = 'abc'

Answer 4

At first, it seems like you should put

AND tbl2.col2 = 'zyx' and tbl2.col3 = 'abc'  

into the WHERE clause, however that would conflict with the OUTER JOIN. WHERE restricts the results so adding that effectively makes it an inner join. A couple of different ways to write this might be:

Add nulls to the where

Select * FROM tbl1      
LEFT OUTER JOIN tbl2 ON tbl1.id = tbl2.id      
WHERE tbl1.col1 = 'pqr' 
  AND ((tbl2.col2 = 'zyx' AND tbl2.col3 = 'abc') 
    OR (tbl2.col2 = NULL AND tbl2.col3 = NULL))

or use a subquery

SELECT * FROM tbl1          
LEFT OUTER JOIN 
(
  SELECT *
  FROM tbl2
  WHERE tbl2.col2 = 'zyx' and tbl2.col3 = 'abc'
) AS temp2 ON tbl1.id = temp2.id          
WHERE tbl1.col1 = 'pqr'         

I would probably opt for the subquery approach as it's just clearer what you're intent is. As far as performance, any column in a WHERE should typically be covered by an index. Beyond that, the optimizer should be able to find the best approach no matter which way that you write the query.