Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

royc75

Programmer
Jun 1, 2006
127
GB
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?
 
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?

 


don't use WHERE ;-)
Code:
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
 
It is working!
Could you please explain me the logic behind this?
 
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
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top