CODEWITHSUNDEEP
t-sqlsql-server-2008-r2left-join
paparazzo
paparazzo

Reputation: 45096

Left Join Is Not Doing What I expect

Totally confused and I have been working at this for 2 hours

I thought restriction on the left side of the join are honored

On this query I am getting [docSVsys].[visibility] 1 and <> 1
I thought this would restrict [docSVsys].[visibility] to 1 

select top 1000 
       [docSVsys].[sID], [docSVsys].[visibility] 
      ,[Table].[sID],[Table].[enumID],[Table].[valueID] 
  from [docSVsys]  with (nolock) 
  left Join [DocMVenum1] as [Table] with (nolock) 
    on [docSVsys].[visibility] in (1)
   and [Table].[sID] = [docSVsys].[sID]
   and [Table].[enumID] = '140' 
   and [Table].[valueID] in (1,7)

This works 

select top 1000 
       [docSVsys].[sID], [docSVsys].[visibility] 
      ,[Table].[sID],[Table].[enumID],[Table].[valueID] 
  from [docSVsys]  with (nolock) 
  left Join [DocMVenum1] as [Table] with (nolock) 
    on [Table].[sID] = [docSVsys].[sID]
   and [Table].[enumID] = '140' 
   and [Table].[valueID] in (1,7)
 where [docSVsys].[visibility] in (1)

I am just having a really off day as I had it in my mind the left side honored the join

Upvotes: 2

Views: 71

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

LEFT JOINs preserve all rows from the left (first) table, no matter what. The condition in the ON clause is only for matching which rows from the right/second table should be paired with rows from the left/first table.

If you want to exclude some rows from the firs table, use the WHERE clause:

select top 1000 
   [docSVsys].[sID], [docSVsys].[visibility] 
  ,[Table].[sID],[Table].[enumID],[Table].[valueID] 
from [docSVsys]  with (nolock) 
left Join [DocMVenum1] as [Table] with (nolock) 
  on [Table].[sID] = [docSVsys].[sID]
  and [Table].[enumID] = '140' 
  and [Table].[valueID] in (1,7) 
where [docSVsys].[visibility] in (1)

Upvotes: 1

usr
usr

Reputation: 171178

SELECT *
FROM A
LEFT JOIN B ON Condition

is equivalent to

 SELECT *
 FROM A
 CROSS JOIN B
 WHERE Condition
UNION ALL
 SELECT A.*, NULL AS B
 FROM A
 WHERE NOT EXISTS (SELECT * FROM B WHERE Condition)

Some rough pseudo-code...

Note, that all rows from A get through. It's just that the columns from B can be NULL if the join fails for some particular row of A.

Put the filter on docSVsys into the WHERE clause.

Upvotes: 1

Related Questions