THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Teaser: Left Join..the SQL ego crusher

One of the more difficult things to master for people coming to SQL seems to be the Outer Join. The common mistake people make is of course putting the condition in the WHERE clause from the outer joined table which should have been in the JOIN itself. There was a question about this on Stackoverflow and a person claimedhe did have an Left Join because...well....he had Left Join in his SQL statement...

 

Let's take a look at some code, create these 2 tables

 

CREATE TABLE Home( HomeID INT not null,

City VARCHAR(200) not null,

Taxes DECIMAL(16,2)not null)

go

 

INSERT Home VALUES(1,'New York City',12000.00)

INSERT Home VALUES(2,'Sacramento',11000.00)

INSERT Home VALUES(3,'Wichita',6000.00)

INSERT Home VALUES(4,'Tampa',9000.00)

go

 

CREATE TABLE HomePhotos(HomePhotosID INT not null,

HomeID INT not null,

HasColorPhoto BIT not null)

go

 

INSERT HomePhotos VALUES(1,1,1)

INSERT HomePhotos VALUES(2,1,1)

INSERT HomePhotos VALUES(3,3,0)

INSERT HomePhotos VALUES(4,3,0)

INSERT HomePhotos VALUES(5,4,1)

go

If we do a regular join

 

SELECT h.*,hp.HomePhotosID FROM Home h

join HomePhotos hp ON h.HomeID = hp.HomeID

Our output is this 

HomeID	City		Taxes	HomePhotosID
1 New York City 12000.00 1
1 New York City 12000.00 2
3 Wichita 6000.00 3
3 Wichita 6000.00 4
4	Tampa		9000.00		5 

 

A left join

SELECT h.*,hp.HomePhotosID FROM Home h

LEFT join HomePhotos hp ON h.HomeID = hp.HomeID

Gives us this 

 HomeID City Taxes HomePhotosID

1	New York City	12000.00	1
1 New York City 12000.00 2
2 Sacramento 11000.00 NULL
3 Wichita 6000.00 3
3 Wichita 6000.00 4
4 Tampa 9000.00 5

Now what will these 2 queries bring back?

 

SELECT h.*,hp.HomePhotosID,hp.HasColorPhoto

FROM Home h

LEFT join HomePhotos hp

ON h.HomeID = hp.HomeID

AND h.Taxes <= 11000

 

 

 

 

SELECT h.*,hp.HomePhotosID,hp.HasColorPhoto

FROM Home h

LEFT join HomePhotos hp

ON h.HomeID = hp.HomeID

AND h.Taxes = -555

 

Did you guess correctly?

 

It is helpful to know the order of logical query processing, here is what happens in general

FROM (incl joins)
ON
OUTER
WHERE
GROUP BY
HAVING
SELECT
ORDER BY 
TOP

 

Published Tuesday, August 04, 2009 10:42 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Nice one!

August 4, 2009 10:07 AM
 

Wiseman82 said:

Agreed - good article! I was just explaining something similar to one of our developers today. :-)

August 4, 2009 2:05 PM
 

Michael Swart said:

I thought I'd be clever and I wrote a query to illustrate your point.

declare @strings table (id int identity(1,1), val nvarchar(max));

insert @strings(val) values

('When using a left outer join,'),

('you'),

('put the condition'),

('in the outer join clause and not'),

('in the where clause. In effect'),

('all rows are returned and no'),

('filtering out '),

('is done.'),

('You can see that all rows are '),

('returned, both primes and'),

('non-primes')

declare @primes table (id int)

insert @primes values(2), (3), (5), (7), (11)

-- a query with a proper left join

select p.id, s.val

from @strings s

left outer join @primes p

on s.id = p.id

-- a query with the condition in the where clause

select p.id, s.val

from @strings s

left outer join @primes p

on 1 =1

where s.id = p.id

August 10, 2009 3:09 PM
 

matt said:

it took me a while, but i got it.  thanks for the education!  : )

filtering with the ON clause has always been a little confusing to me and now i see why.  it was the outer join that threw the monkey wrench.

August 11, 2009 4:37 PM
 

Jon Crawford said:

Nice, ran into this recently but didn't realize it in quite the way you presented it. Much clearer to me now.

Michael Swart, ok if I borrow your sample and pass it around at work as a learning tool? Credit given to this url, of course.

August 12, 2009 1:24 PM
 

Michael Swart said:

Sure thing Jon.

August 13, 2009 9:52 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement