OUTER JOIN with WHERE clause fails

12 Aug 2006 7:04 PM

mavrick_101

Hi,

I am frustrated with this problem.

I am trying to get results from two tables and using left outer join. The
results are ok util I add the WHERE clause. As soon as I add the where clause
it works as INNER JOIN.

What am I doing wrong? :(

12 Aug 2006 7:47 PM

David Portas

mavrick_101 wrote:

> Hi,
>
> I am frustrated with this problem.
>
> I am trying to get results from two tables and using left outer join. The
> results are ok util I add the WHERE clause. As soon as I add the where clause
> it works as INNER JOIN.
>
> What am I doing wrong? :(

Do you normally expect people to play mind reading games or is it just
when you are frustrated? Let's try and debug the query that you didn't
actually let us see... I suppose you are referencing the outer table
(the unpreserved one on the right hand side of the join) using a
comparison such as =, >, < or <> in the WHERE clause. That means the
rows with nulls will get excluded because logically speaking the WHERE
clause is always applied after the JOIN ... ON clause.

Move the comparison expression(s) that reference the outer table into
the ON clause instead.

Please give us a few more clues if you need more help. :-(

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

14 Aug 2006 5:09 PM

mavrick_101

Sorry for being not so clear in my question.

Your answer did help me resolve the problem. I added the part of the where
clause along the join statement before the where clause, and it worked.

Thnx

Show quote

"David Portas" wrote:

> mavrick_101 wrote:
> > Hi,
> >
> > I am frustrated with this problem.
> >
> > I am trying to get results from two tables and using left outer join. The
> > results are ok util I add the WHERE clause. As soon as I add the where clause
> > it works as INNER JOIN.
> >
> > What am I doing wrong? :(
>
> Do you normally expect people to play mind reading games or is it just
> when you are frustrated? Let's try and debug the query that you didn't
> actually let us see... I suppose you are referencing the outer table
> (the unpreserved one on the right hand side of the join) using a
> comparison such as =, >, < or <> in the WHERE clause. That means the
> rows with nulls will get excluded because logically speaking the WHERE
> clause is always applied after the JOIN ... ON clause.
>
> Move the comparison expression(s) that reference the outer table into
> the ON clause instead.
>
> Please give us a few more clues if you need more help. :-(
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>