seeing no nulls from self outer join@@

–drop table students
–go
–create table students(
—    student varchar(44),
—    score decimal(4,1),
—    ident int identity
–)
–insert students select ‘Andrew’, 15.6
–insert students select ‘Becky’, 13
–insert students select ‘Chuck’, 12.2
–insert students select ‘Dan’, 25.6
–insert students select ‘Eric’, 15.6
–insert students select ‘Fred’, 5.6
–select * from students
select * from students h left join students l
  on h.score >= l.score
order by h.score, l.score
–- won’t show the nulls expected of an outer join, so I remove the “=”. Now Fred (and Greg) the poor student doesn’t score higher than anyone and therefore shows nulls.
Another way to get the nulls is adding the not-equal join condition
select * from students h left join students l
  on h.score >= l.score
and h.student != l.student –- this condition is essential to self outer joins
Now, this topic is relevant to me because whenever I construct an outer join, I expect to see nulls. An outer join without nulls is a emergency exit seat without legroom.
Q: if an outer join returns no nulls, is it equivalent to an inner join?
A: Usually yes. I’m yet to find an exception.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s