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