A question on LEFT OUTER JOIN

Question

Post reply

A question on LEFT OUTER JOIN

todinhkhoi

Old Hand

Points: 363
More actions
June 27, 2011 at 4:01 pm

#253625

Dear all
I have the following query:
----------------
Query 1:
select shrtckn_pidm, ssbsect_term_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, ssbsect_camp_code
from saturn.ssbsect
left outer join saturn.shrtckn
on ssbsect_crn = shrtckn_crn
where ssbsect_term_code = '201110'
and ssbsect_subj_code = 'SOC'
and ssbsect_crse_numb = '101'
and ssbsect_camp_code = 'OL'
and shrtckn_term_code = '201110'
----------------
The total number of records I got from this query was 538.
Now I link one more table to the above query:
----------------
Query 2:
select shrtckn_pidm, stsex, stclass, ssbsect_term_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, ssbsect_camp_code
from saturn.ssbsect
left outer join saturn.shrtckn
on ssbsect_crn = shrtckn_crn
left outer join oiradm.student
on shrtckn_pidm = stpidm
where ssbsect_term_code = '201110'
and ssbsect_subj_code = 'SOC'
and ssbsect_crse_numb = '101'
and ssbsect_camp_code = 'OL'
and shrtckn_term_code = '201110'
and stcyt = '201110'
----------------
The total number of records I got from this query was 537, which was 1 record fewer than query 1.
What I still do not understand is that in query 2 I used left outer join to link one more table (oiradm.student) to query 1. Therefore, I hoped to get the same number of records (538). However, I did not get what I wanted. Could you help me to explain this? If I want to get the same result as in query 1, what should I do to query 2?
Thank you very much.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 1

Please show the code that you used, it would be much easier.

Edit> didn't see that code at first. Never mind.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I suspect this bit of code in Query 2 is the reason:

and stcyt = '201110'

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

todinhkhoi Old Hand Points: 363 More actions · Answer 3

Hello mister.mangoo

What I do not understand here is that I used left join here, so the total number of records from query 1 (538 records) should have been preserved. (In other words, records that existed in query 1 but did not exist in the last table "oiradm.student" in query 2 must have shown up, too).

Does anyone have any other idea?

Thank you very much.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

The extra code at the end is part of the WHERE clause, not the join!

and stcyt = '201110'

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

You have two problems with your queries. In the first query, you have essentially turned it into an INNER JOIN by including a column from the unpreserved table (outer table) in the where clause.

Then, you added another table in an outer join - and included a column from that table in the where clause which effectively turns the outer join into an inner join.

This comes down to how nulls are evaluated. When you compare a column with a null value - the evaluation is unknown, and therefore the row is excluded.

In an outer join, the outer table will return nulls for those rows that have no matching rows in the preserved table. Then, when you try to compare the value in that column to another value - it evaluates to unknown because of the null - eliminating that row from the results.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs