LEFT JOIN doesn't behave as expected at this query... why? 1

[royc75]

Hello,

Consider this simple query:

SELECT number1, number2
FROM NUMBERS LEFT JOIN ITEMS
ON NUMBERS.number1 = ITEMS.number2


Now, suppose NUMBERS Contain numbers from 1 till 10 for each year it has and ITEMS contains numbers from 1 till 9 for each year it has. I would expect the result to bring me 10 with NULL for each year, yet I receive only 9 results, like I did an INNER JOIN and not LEFT JOIN.
Both fields are the exact type and container size.

What could be the problem here?

 

[r937]

there's probably a WHERE clause that you did not reveal

r937.com | rudy.ca

 

[cmmrfrds]

See this thread183-1261299

 

[royc75]

I don't have WHERE clause but I did some more research regarding the data. Consider it looks like thias at the table:

tankNumber caseNumber
---------- ----------
1 1
1 3
2 1
2 2
2 3

Now, consider I would like to LEFT JOIN caseNumber with Numbers table the contains numbers from 1 to 3.
I would like to get for tankNumber 1:

caseNumber number
---------- ------
1 1
NULL 2
3 3

Yet I receive:
caseNumber number
---------- ------
1 1
3 3

since caseNumber 2 exist at tankNumber 1.
even if I add WHERE tankNumber = 1 I still receive the same result.
How can I overcome this?

 

[r937]

don't use WHERE ;-)

select ITEMS.caseNumber
     , NUMBERS.Number
  from bar 
left outer
  join ITEMS
    on ITEMS.caseNumber = NUMBERS.Number
   [b]and[/b] ITEMS.tankNumber = 1

r937.com | rudy.ca

 

[royc75]

It is working!
Could you please explain me the logic behind this?

 

[r937]

the ON clause defines which rows to match

when a matching row is not found, all columns from the right table in the result row are set to NULL

if you use a WHERE condition instead, then for unmatching rows, the right column, which is NULL, will never meet the condition =1, consequently that unmatched row is dropped from the result set



r937.com | rudy.ca

 

[royc75]

I understand. Ans one more question please: It is working with LEFT JOIN as well, so why do I need the LEFT OUTER JOIN for and what is the difference between it and the LEFT JOIN?

 

[r937]

LEFT JOIN = LEFT OUTER JOIN

the keyword OUTER is optional

r937.com | rudy.ca